Hi all, I need to perform the following query on Cognos Analytics;
I need to know how to force the subquery to join the main query. :(
Select A.*
from Table_A A
where A.field_b = ?Date?
And A.field_c = (
select min(B.field_a)
from Table_B B
Where B.field_b = A.field_d
and B.field_c = A.field_e
and B.field_d = A.field_f
and B.field_e > ?Date?
and B.field_f = 'Lorem Ipsum'
and B.field_g in ('Lorem', 'Ipsum')
)
I'm new to Cognos and spent a day searching for this answer but with no luck.
I appreciate the help.
Thanks. :)
What have you done?
What error message are you getting?
So far, the closest I can get is this:
Query A Main Query - Filter ( QueryA.field_c = QueryB.field_a )
Query B Sub Query - static filters
Cognos is generating the query like this:
WITH TAB_B as (
select min(B.field_a) field_a
from Table_B B
Where B.field_e > ?Date?
and B.field_f = 'Lorem Ipsum'
and B.field_g in ('Lorem', 'Ipsum')
)
Select A.*
from Table_A A
where A.field_b = ?Date?
And A.field_c = (Select field_a from TAB_B)
This has two main problems:
1 - the query is wrong since it doesn't have the join between the main and subqueries
2 - Even with the joins, the performance would not be good since it's reading the whole tableB first
The idea is to force the subquery with the joins like the SQL from the first post.
I tried puting filters inside the subquery pointing to the main one to kind of force the join but it returns an error:
XQE-PLN-0132 There is a circular reference involving the following queries
That still doesn't make sense. Reproduce the problem using the samples, post the report spec to pastebin, and paste the link here.
Can't reply if I type the SQL here but can write one line !