Welcome to the new CogKnowHow.com!


If you have trouble logging in, reset your password via the login window
by clicking the 'Forgot Password' link.

Forcing subquery in main query filter to be optional


#1

requirement :where ( PROJ = (SELECT NAME FROM SOURCE.MGMT WHERE NUM = ‘’ and BUS_PARTNER = ‘’) )

To do this in cognos:
I have taken Query 1:SELECT NAME FROM SOURCE.MGMT WHERE NUM = ‘’ and PARTNER = ‘‘
Filter usage :option for NUM=’’ and Required for PARTNER=’'
Now in Query 2 which is main query for List report , I have taken filter usage as optional again and PROJ=

[Query1].[Name].

SQL generated expected as below when NUM parameter is not entered :
select * from table where date=‘2016-01-01’ and week=‘2016-07-01’ and what ever users enter in prompt page

which are all optional.

SQL generated is:
select * from table where date=‘2016-01-01’ and week=‘2016-07-01’ and PROJ=(select NAME from SOURCE.MGMT

where PARTNER=’’.

which is not correct.
Here what should I do to skip the PROJ=…filter in where clause all together when no NUM is entered by

users.

Any suggestions please