How does Cognos RS 8.4 build a SQL subquery and function properly?

[rhythmz]

Can someone educate me on how to utilize Report Studio to properly address the following subquery in a SQL statement?

from mid T2,
   organization T5,
   organization_alias T3,
   bl_mstr T4,
   turnin
         T1 left outer join
   manfst T6 on ((T1.store = T6.opfac_mnfst)
                                             and (T6.julian_date = T1.julian_date))
                                             and (T6.sqnc_number = T1.sqnc_number))
where (T2.niin = T1.niin_trn)
and (T5.store_code = T1.store)
and (T5.store_id = T3.store_id)
and ((T6.bl_num = T4.bl_num) and (T6.store_mnfst = T4.store))
and ((((not T1.store IN ('50100'))
            and (T1.prodid = T2.prodid))
            and (T1.location_stat = 'INTRAN'))
            and (not T2.im_code IN ((select distinct T1.value as c1
                                                      from valcode T1
                                                      where (T1.val_type = 'PBH')

I am not sure how subqueries are built in Report Studio and my class manuals do not seem to adrees this functionality as well.

[sdee_sekaran]

create a seperate query and join it with master query based on condition.

Regards, Dhana.

[rhythmz]

Thanks Dhana for replying.

I think I understand what you are saying.

I created a Query2 in Query Explorer with just two columns, 'value' and 'val_type' from the valcode table.
I put in the detail filter of Query two, valcode.val_type = 'PBH'
Then I put in the detail filter of Query1, mid.im_code <> Query2.valcode.value

However, I get a cross-join error when I do this.
Am I on the right track or misled somewhere.

Thanks again for your assistance!

[MFGF]

Did you add a join between the two queries?

[rhythmz]

Good point. I was reading about that in the Volititch book. However, I'm confused because my Query1 was built before reading this so adding the Join in QE then expects 2 queries to be built leading into Query1. The book leaves you the impression that Query1 hasn't been built yet and is created at the time you drop the Join into the window from insertable objects.

Should I rebuild this report thus creating 1 query with just my subquery statement above and the other query consisting of what is now my Query 1? This operation has me a little confused.

Thanks for working with me through this!
Dennis

[MFGF]

Hi Dennis,

Yes, the results of the join between your two queries are presented within a third query, and you can then use this third query to drive your report object (list, crosstab, chart etc).

Regards,

MF.