where ( PROJ = (SELECT NAME FROM SOURCE.MGMT WHERE NUM = '<P31>' and BUS_PARTNER = '<BP>') )
To do this in cognos:
I have taken Query 1:SELECT NAME FROM SOURCE.MGMT WHERE NUM = '<P31>' and PARTNER = '<BP>'
Filter usage
ption for NUM='<P31>' and Required for PARTNER='<BP>'
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='<BP>'.
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
To do this in cognos:
I have taken Query 1:SELECT NAME FROM SOURCE.MGMT WHERE NUM = '<P31>' and PARTNER = '<BP>'
Filter usage
ption for NUM='<P31>' and Required for PARTNER='<BP>'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='<BP>'.
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