What's wrong with this query?

mpandit

It gives parser error

select * from master_type(all) where i_chronicle_id in

(

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/01',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/02',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/03',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/04',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/05',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/06',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/07',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/08',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/09',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/10',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/11',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/12',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/13',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/14',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/15',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/16',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/17',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/18',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/19',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/20',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/21',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/22',DESCEND) union

select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test/23',DESCEND)

)

and any r_version_label != '10.0'

Find more posts tagged with

Comments

DCTM_Guru

Length of DQL might be too long. Instead of explicitly defining subfolders underneath /Test, why dont you reference /Temp:

select * from master_type(all) where i_chronicle_id in

(select distinct i_chronicle_id from gxp_master_type(all) where FOLDER ('/Test',DESCEND)

)

and any r_version_label != '10.0'

mpandit

Thanks Johnny for quick response. Actually, all the subfolders are different in my case and I posted dummy data here as I cannot post actual data on the forum.

mpandit

Also note that the inner query (that to gather a list of chronicle ids) worked fine and only when I add the outer predicates to select r_object_id and ignore version label that I get a parser error.

DCTM_Guru

Whats the parser error? It normally tells you approx where the error is.

mpandit

Its at the end of first union.

DCTM_Guru

Then DQL parser probably doesnt know how to include UNION in a subselect. You could always write a utility to store the result set of the UNION and then pass each i_chronicle_id as parameter to query.

lastnitescurry

Two thoughts:

Link all folders you want to query content from to 1 master folder. PROS: Query is simpler and if embedded in a report, easy to add new folders
If list of folders is static, then get the SQL for the UNIONs and create a view, and register the view so you can query from it with DQL

Wiesnoepersad

Database performancewise thisis a horrible query.

You can try:

Select * from master_type(all)

where i_chronicle_id in ( select distinct i_chronicle_id

from gxp_master_type(all)

where

FOLDER ('/Test/01',DESCEND)

OR FOLDER ('/Test/02',DESCEND)

OR FOLDER ('/Test/03',DESCEND)

FOR FOLDER ('/Test/23',DESCEND))

and any r_version_label != '10.0' ENABLE (optimize_top 100)

even this query will take some time, just compare the Database querry plan..

Why not try somethign like: