Hi everyone, I have a SQL Server query where I'm generating some counts of records. In that query, I'm using "not exists" in the where clause to exclude records that match specific criteria. In Cognos Analytics, I tried using the "not exists" statement but got an error when running the report. I then tried using "not (criteria1 = x and criteria2 = y)" but Cognos doesn't seem to pay attention to that (either set to before auto aggregation or after). Does anyone have any suggestions on what I can do in the Cognos report to mimic the "not exists" SQL?

Thanks and regards, Melody

------------------------------
MELODY ALBERTI
------------------------------

#CognosAnalyticswithWatson

Never mind everyone, I figured it out:

not (criteria1 like '%xyz%') or criteria1 is null

------------------------------
MELODY ALBERTI
------------------------------

Original Message:
Sent: Wed July 22, 2020 02:11 PM
From: MELODY ALBERTI
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

Hi everyone, I have a SQL Server query where I'm generating some counts of records. In that query, I'm using "not exists" in the where clause to exclude records that match specific criteria. In Cognos Analytics, I tried using the "not exists" statement but got an error when running the report. I then tried using "not (criteria1 = x and criteria2 = y)" but Cognos doesn't seem to pay attention to that (either set to before auto aggregation or after). Does anyone have any suggestions on what I can do in the Cognos report to mimic the "not exists" SQL?

Thanks and regards, Melody

------------------------------
MELODY ALBERTI
------------------------------

#CognosAnalyticswithWatson

In the event that you do need to to use a quantified predicate  you can reference a query subject as shown.

[XXYYZZ].[T1].[C1]  NOT IN  ( [XXYYZZ].[T2].[C2] )

....

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Wed July 22, 2020 04:50 PM
From: MELODY ALBERTI
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

Never mind everyone, I figured it out:

not (criteria1 like '%xyz%') or criteria1 is null

------------------------------
MELODY ALBERTI

Original Message:
Sent: Wed July 22, 2020 02:11 PM
From: MELODY ALBERTI
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

Hi everyone, I have a SQL Server query where I'm generating some counts of records. In that query, I'm using "not exists" in the where clause to exclude records that match specific criteria. In Cognos Analytics, I tried using the "not exists" statement but got an error when running the report. I then tried using "not (criteria1 = x and criteria2 = y)" but Cognos doesn't seem to pay attention to that (either set to before auto aggregation or after). Does anyone have any suggestions on what I can do in the Cognos report to mimic the "not exists" SQL?

Thanks and regards, Melody

------------------------------
MELODY ALBERTI
------------------------------

#CognosAnalyticswithWatson

Thanks Nigel!

------------------------------
MELODY ALBERTI
------------------------------

Original Message:
Sent: Tue July 28, 2020 08:53 AM
From: NIGEL CAMPBELL
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

In the event that you do need to to use a quantified predicate  you can reference a query subject as shown.

[XXYYZZ].[T1].[C1]  NOT IN  ( [XXYYZZ].[T2].[C2] )

....

------------------------------
NIGEL CAMPBELL

Original Message:
Sent: Wed July 22, 2020 04:50 PM
From: MELODY ALBERTI
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

Never mind everyone, I figured it out:

not (criteria1 like '%xyz%') or criteria1 is null

------------------------------
MELODY ALBERTI

Original Message:
Sent: Wed July 22, 2020 02:11 PM
From: MELODY ALBERTI
Subject: how to incorporate "not exists" into query/filter in Cognos Analytics 11.1.6

Hi everyone, I have a SQL Server query where I'm generating some counts of records. In that query, I'm using "not exists" in the where clause to exclude records that match specific criteria. In Cognos Analytics, I tried using the "not exists" statement but got an error when running the report. I then tried using "not (criteria1 = x and criteria2 = y)" but Cognos doesn't seem to pay attention to that (either set to before auto aggregation or after). Does anyone have any suggestions on what I can do in the Cognos report to mimic the "not exists" SQL?

Thanks and regards, Melody

------------------------------
MELODY ALBERTI
------------------------------

#CognosAnalyticswithWatson