Sub query join

[bruno_gomes]

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. :)

[dougp]

What have you done?
What error message are you getting?

[bruno_gomes]

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

[dougp]

That still doesn't make sense.  Reproduce the problem using the samples, post the report spec to pastebin, and paste the link here.

[cognostechie]

Can't reply if I type the SQL here  but can write one line !