I need help with Impromptu query filters.

[billybeeks]

Howdy,

I'm currently experiencing difficulties Querying a dB with Cognos Impromptu.

the type of search i'm looking for in Layman's term's is
"All ID's in TABLE_A that are not in TABLE_B"

working "SQL plus" example
"select ID_A
from TABLE_A
where
ID_A not in ( select ID_B from TABLE_B)"

In Impromptu after building the catalog, i'm using various filters.
ID_A not in ( ID_B )
or
ID_A is missing

this brings back nothing, where as SQL plus, with the above SQL example brings back proper results.
Impromptu only brings back results when the filters are:
ID_A in ( ID_B )
or
ID_A is not missing

Which is not what i'm looking for, it seems to only be able to bring back ID's that Exist in TABLE_B, and not bring back ID's From TABLE_A that do not exist in TABLE_B.

Any help would be much apprieciated, thank you in advance for your help.

B.

 

[griffindm]

B.

You have two methods you can use in Impromptu to achieve this. The first is to make sure the join between the two tables is an outer-join, where the 'check-box' is checked for Table A. The write your reports to contain a single data element (call it RefB, for example here) from table B that is always present for that table, and a filter that only contains the statement of RefB is missing.

The second method is to use a DATASET. This is a report that contains a row for each of the table id's from Table B. Save the report, and then create a second report with just data from table A, and in the filter use the statement: TableA_ID not in DATASET. Point the DATASET to the first report and select the ID column. This creates the subselect statement you give in your example.

The first example may be faster if there are a lot of rows in both tables and the database supports outer joins. The second will be faster if there are only a limited number of rows in table B or the database does NOT support outer joins.

Hope this is helpful,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[rkp402]

I would just have these tables without the join in the catalog and write my own sql using the set operator MINUS.