Cognos Analytics

Hi all,

I know I can do a filter such as 

[Order Method Code] in ( [Query2].[Order Method Code] )

within a report.

Traditionally in SQL such a filter would be written as an EXISTS statement as it usually faster than an IN statement when the query referenced within the IN is a large result set.

Is there anyway of writing a SQL EXISTS within a CA report query?

Thanks

Hi Mark,

you can link qeueries in report studio. Just create two queries and place the needed data items in them and then drag one next to the other:

You can now use the data from Querey1 in the filter area in Query2:

Another way is to use {}. Everything between them will be pushed directly to the database system without any interpretation from Cognos. Yo oyu can place any valid manual written SQL in there. Give it a try.

Hi all,

I know I can do a filter such as 

[Order Method Code] in ( [Query2].[Order Method Code] )

within a report.

Traditionally in SQL such a filter would be written as an EXISTS statement as it usually faster than an IN statement when the query referenced within the IN is a large result set.

Is there anyway of writing a SQL EXISTS within a CA report query?

Thanks

@Robert Dostal - i am intrigued by your two suggestions but do not really understand either one. if you have time, can you mock up a simple example?

------------------------------
brenda grossnickle
BI Programmer Analyst
FIS

Original Message:
Sent: Fri October 11, 2024 10:26 AM
From: Robert Dostal
Subject: SQL Exists within a Report

Hi Mark,

you can link qeueries in report studio. Just create two queries and place the needed data items in them and then drag one next to the other:

You can now use the data from Querey1 in the filter area in Query2:

Another way is to use {}. Everything between them will be pushed directly to the database system without any interpretation from Cognos. Yo oyu can place any valid manual written SQL in there. Give it a try.

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen

Original Message:
Sent: Thu October 10, 2024 11:53 AM
From: Marc Reed
Subject: SQL Exists within a Report

Hi all,

I know I can do a filter such as 

[Order Method Code] in ( [Query2].[Order Method Code] )

within a report.

Traditionally in SQL such a filter would be written as an EXISTS statement as it usually faster than an IN statement when the query referenced within the IN is a large result set.

Is there anyway of writing a SQL EXISTS within a CA report query?

Thanks

------------------------------
Marc Reed
------------------------------

Hi Mark,

you can link qeueries in report studio. Just create two queries and place the needed data items in them and then drag one next to the other:

You can now use the data from Querey1 in the filter area in Query2:

Another way is to use {}. Everything between them will be pushed directly to the database system without any interpretation from Cognos. Yo oyu can place any valid manual written SQL in there. Give it a try.

Hi all,

I know I can do a filter such as 

[Order Method Code] in ( [Query2].[Order Method Code] )

within a report.

Traditionally in SQL such a filter would be written as an EXISTS statement as it usually faster than an IN statement when the query referenced within the IN is a large result set.

Is there anyway of writing a SQL EXISTS within a CA report query?

Thanks

Hi Robert,

Yes - fully aware that you can data items from one query into another. The example in my initial post ( [Order Method Code] in ( [Query2].[Order Method Code] ) shows how to do that. You could also achieve similar with a join between two queries.

Within SQL the rule of thumb is that EXISTS is usually better than an IN when there are a large number of rows in the lower query, so was looking to see if anyone had found a method of using the SQL exists syntax within CA reports.

Hi Mark,

you can link qeueries in report studio. Just create two queries and place the needed data items in them and then drag one next to the other:

You can now use the data from Querey1 in the filter area in Query2:

Another way is to use {}. Everything between them will be pushed directly to the database system without any interpretation from Cognos. Yo oyu can place any valid manual written SQL in there. Give it a try.

Hi all,

I know I can do a filter such as 

[Order Method Code] in ( [Query2].[Order Method Code] )

within a report.

Traditionally in SQL such a filter would be written as an EXISTS statement as it usually faster than an IN statement when the query referenced within the IN is a large result set.

Is there anyway of writing a SQL EXISTS within a CA report query?

Thanks