Hi Guys,
I would like to know if it's possible to filter few rows in cognos in similar to the below query:
select
col1,
col2
from table
where (col1,col2) <> ('abc','yz')
I am basically removing only few combinations of the columns. At present I have concatenated the 2 columns in cognos and then I have applied the filter but I would like to know if there any other options available.
P.S I achieved the same behaviour with or condition too.
Thanks,
mc
use the sql as data source
Quote from: mrcool on 23 Jan 2017 09:49:55 PM
where (col1,col2) <> ('abc','yz')
To be honest, I have not seen that kind of column/value list in a SQL WHERE clause before and I have been doing this for decades. I find it very ambiguous and would use different syntax, but maybe that is a lack of familiarity. Not even sure exactly what it is saying.
At any rate, I definitely do not recommend using SQL as a report data source. Cognos allows it but using it is sub-optimal, is subject to problems when the underlying data semantics change, and limits some of the out-of-the-box product functionality. Use SQL only if you absolutely must.
Are you essentially looking for those records where
Col1 <> 'abc' and Col2 <> 'xy'Your original statement isn't very clear (and like bdbits, I've never come across that syntax before)
Col1 | Col2 | Included? |
abc | xy | No |
abc | mn | ?? |
def | xy | ?? |
def | mn | ?? |
If you can fill in the blanks we might be able to help you get to the results you're after.
Hi Guys,
Thanks for your response.
I am using postgresql and it does support this syntax.
Quote from: BigChris on 24 Jan 2017 09:55:07 AM
Are you essentially looking for those records where Col1 <> 'abc' and Col2 <> 'xy'
Your original statement isn't very clear (and like bdbits, I've never come across that syntax before)
Col1 | Col2 | Included? |
abc | xy | No |
abc | mn | ?? |
def | xy | ?? |
def | mn | ?? |
If you can fill in the blanks we might be able to help you get to the results you're after.
Chris - Col1 <> 'abc' and Col2 <> 'xy' is not same as (col1,col2)<>('abc','xy')
Your filter condition will drop 2 rows where as my condition will drop only 1 row which is the combination of 'abc' in col1 and 'xy' in column2. From your examples only 1st row will be dropped.
You can achieve the same result using the or condition Col1 <> 'abc' or Col2 <> 'xy' .
Please let me know if I am not clear enough.
This is covered in the Report Studio user guide (aka online help).
http://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/t_cr_rptstd_wrkdat_filter_data_rel.html#cr_rptstd_wrkdat_filter_data_rel