Subquery

[sony]

How to get the below query in cognos report studion irrespective of joins in FM or do i have to change the join in FM :

Select a.* from a
left join b
on a.key = b.key
Where a.key not in (Select b.key from b)

????

Thanks,
sony

[tjohnson3050]

create a separate query in Report Studio for the subquery (b.key), do not join the queries, then on both queries set the property 'allow cross joins' to yes.

After that, in the primary query, pull the data item b.key from the subquery.

[wyconian]

Hi

Looks like you want to get everything from a where it's not in b.  If that's what you want add a union query where one of the queries selects everything from a and the other selcts everything from b. Change the union icon to except.

[mrcool]

First All I don't think join is required in your example as below query will work the same

Select a.* from a

Where a.key not in (select key from b)

As tjohnson3050 suggested create one more query for getting b.key and apply detail filter in parent query
a.key <>b.key

Cheers,
MC