How to get the (+) as a filter option? (for outer joins)

[sharkatek]

Hello all,

I have joined a table and a hot file and the join works ok sometimes, but not always. I want to force it to work by using the (+) for a left outer join, but the (+) is not an available option I in the filter tab.

How can I get he (+) option added to the filter tab?

Thank-you in advance!
Sharkatek

 

[sharkatek]

more information: I am using Cognos Impromptu v. 7.1.339.0

 

[Draoued]

define the outer join in your catalog , open the catalog , edit the Joins from catalog menu , and click the Outer-Join option , in the relationship between your table and the hotfile

 

[sharkatek]

Thank-you, but I had already done all of this. The outer join is not working AND I still do not see the option in the filter tab to use the "(+)"

When I go to the catalog/joins and select my "left" table and click "show joins", I see the table I need the join with. I click on it and in the diagram part of the screen, I see the two tables - the field is linked AND the outer join box ON THE LEFT is checked.

What am I missing? Is there ANY way to force the outer join to work? Is it impossible to actually see the (+) option in the filter tab for Impromptu v. 7.1.339.0?

 

[Draoued]

I tried it.

In Impromptu , it s not showing the join , but I saved the report as IQD , and edit the IQD in notepad, then I can see the Outer join.

 

[GJParker]

what makes you think the outer join is not working ?

what do you mean by (+) option in the filter tab ? joins are not controlled by individual reports so there is no option to change join type anywhere in the query dialog.



Gary Parker
MIS Data Analyst
Manchester, England

 

[sharkatek]

I tried this just now -- here is my SQL:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ENRICH
DATASOURCENAME,\\Cognos\Reports\Development\Available Equipment New.imr
TITLE,Available Equipment New
BEGIN SQL
select distinct T1."AAACNB" as c1,
T1."AABJST" as c2,
T1."AAADNB" as c3,
T1."AAUUTX" as c4,
T1."AAWCCD" as c5,
T2."MGPBTX" as c6,
T3."MHACNB" as c7,
T3."MHNOCD" as c8,
T3."G1BUNA" as c9,
T4."D4ACNB" as c10,
T4."D4E6CE" as c11,
T5."C7ACNB" as c12,
T5."Maximum Unit Termination Date" as c13,
T5."C7GZCD" as c14
from "S1043BFC"."CAT1DATA"."FDMGREL1" T2,
"S1043BFC"."CAT1DATA"."FDAAREL1" T1,
QSS."\\Cognos\Reports\Field\HF CAT1 Field Unit Facility.ims" T3,
QSS."\\Cognos\Reports\Field\HF CAT1 Unit Pool.ims" T4,
QSS."\\Cognos\Reports\Field\Hot_File_Data_Available.ims" T5
where (T2."MGRZCD" = T1."AAWCCD")
and ((((((T1."AABJST" = 'A') and (T1."AAACNB" = T3."MHACNB")) and (T1."AAACNB" = T4."D4ACNB")) and (T1."AAACNB" = T5."C7ACNB")) and (T3."MHNOCD" = '004 ')))
order by c1 asc,c7 asc,c10 asc,c12 asc

END SQL
COLUMN,0,Unit Number
COLUMN,1,Equipment Status
COLUMN,2,Equipment Year
COLUMN,3,Equipment Description
COLUMN,4,Equipment Type
COLUMN,5,Equipment Type Description
COLUMN,6,HFUnitFacility Unit Number
COLUMN,7,HFUnitFacility Facility Code
COLUMN,8,HFUnitFacility Facility Desc
COLUMN,9,HFUnitPool Unit Number
COLUMN,10,HFUnitPool Pool
COLUMN,11,HFAvailable Unit Number
COLUMN,12,HFAvailable Available Date
COLUMN,13,HFAvailable Last Pool
--- end of sql ------------

The part where the outer join should be applied is
(T1."AAACNB" = T4."D4ACNB")

I HAVE to have the two fields matched up in my filter because if I don't, it produces a million rows. It just seems to ignore the outer join.

 

[sharkatek]

I tried this just now -- here is my SQL:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,ENRICH
DATASOURCENAME,\\Cognos\Reports\Development\Available Equipment New.imr
TITLE,Available Equipment New
BEGIN SQL
select distinct T1."AAACNB" as c1,
T1."AABJST" as c2,
T1."AAADNB" as c3,
T1."AAUUTX" as c4,
T1."AAWCCD" as c5,
T2."MGPBTX" as c6,
T3."MHACNB" as c7,
T3."MHNOCD" as c8,
T3."G1BUNA" as c9,
T4."D4ACNB" as c10,
T4."D4E6CE" as c11,
T5."C7ACNB" as c12,
T5."Maximum Unit Termination Date" as c13,
T5."C7GZCD" as c14
from "S1043BFC"."CAT1DATA"."FDMGREL1" T2,
"S1043BFC"."CAT1DATA"."FDAAREL1" T1,
QSS."\\Cognos\Reports\Field\HF CAT1 Field Unit Facility.ims" T3,
QSS."\\Cognos\Reports\Field\HF CAT1 Unit Pool.ims" T4,
QSS."\\Cognos\Reports\Field\Hot_File_Data_Available.ims" T5
where (T2."MGRZCD" = T1."AAWCCD")
and ((((((T1."AABJST" = 'A') and (T1."AAACNB" = T3."MHACNB")) and (T1."AAACNB" = T4."D4ACNB")) and (T1."AAACNB" = T5."C7ACNB")) and (T3."MHNOCD" = '004 ')))
order by c1 asc,c7 asc,c10 asc,c12 asc

END SQL
COLUMN,0,Unit Number
COLUMN,1,Equipment Status
COLUMN,2,Equipment Year
COLUMN,3,Equipment Description
COLUMN,4,Equipment Type
COLUMN,5,Equipment Type Description
COLUMN,6,HFUnitFacility Unit Number
COLUMN,7,HFUnitFacility Facility Code
COLUMN,8,HFUnitFacility Facility Desc
COLUMN,9,HFUnitPool Unit Number
COLUMN,10,HFUnitPool Pool
COLUMN,11,HFAvailable Unit Number
COLUMN,12,HFAvailable Available Date
COLUMN,13,HFAvailable Last Pool
--- end of sql ------------

The part where the outer join should be applied is
(T1."AAACNB" = T4."D4ACNB")

I HAVE to have the two fields matched up in my filter because if I don't, it produces a million rows. It just seems to ignore the outer join.

 

[drlex]

Sounds like you may have another join structure that Impromptu is using in preference to the outer join. Have you tried testing the catalogue joins (Catalogue>Joins>test tab)?

soi la, soi carré

 

[Draoued]

you wrote :

I HAVE to have the two fields matched up in my filter because if I don't ....

do you mean that you are manually adding a filter in the filter box ? Then it might be that it s overwritting the joins.

Try to remove it , if it s a "manual" filter.

Do avoid the million rows , press ESC , save as IQD or SQL and see if you have the outer-join mentioned in the sql.

 

[sharkatek]

I tried to go into the TEST folder and Impromptu locked up on me so I ctrl-alt-delete to end it... went in again, looked at the join - still there left outer checked (only).. tried to click Test again - locked up again, so testing isn't an option.

 

[sharkatek]

Draued:

I tried this and NO, I do not see an outer join.

 

[GJParker]

try creatign a sample report just using the table and the hotfile and check the SQL created to see if the outer join is used.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[sharkatek]

Gary, I did a test and without linking the two fields in the filter, I clicked OK, then hit esc and saved the report as an .iqd file - here is the result SQL - no join that I can see. AAACNB and C7ACNB were joined manually by me as a left outer join.

COGNOS QUERY
STRUCTURE,1,1
DATABASE,APP1
TITLE,Report1
BEGIN SQL
select distinct T1."AAACNB" as c1,
T2."C7ACNB" as c2,
T2."C7GZCD" as c3
from QSS."\\Cognos\Reports\Field\Hot_File_Data_Available.ims" T2,
"S1043BFC"."EMMSDATA"."FDAAREL1" T1
order by c1 asc,c2 asc

END SQL
COLUMN,0,Unit Number
COLUMN,1,C7ACNB
COLUMN,2,C7GZCD

 

[GJParker]

Sharkatek

Looking at the derived SQL there is no join at all between these 2 tables, can you recheck and verify the joins.

Catalog menu -> Joins -> anvigate to the hotfile Hot_File_Data_Available.ims -> double click on the hotfile, this should expand and display all of the joined tables.

Is FDAAREL1 listed and a join created here ?

here is a sample I tried and the SQL created and you can see the join.

select T1."TM_ID" as c1,
T1."TM_NAME" as c2,
T1."Start Date" as c3,
T2."STAF_CODE" as c4,
T2."STAF_NAME" as c5
from (QSS."\\K2-sowebiis\edrive$\Cognos\Cognos Work\Live\Optimus\Hotfiles\H_Create Timesheet Workfile.ims" T1 left outer join "root"."STAF" T2 on T1."TM_NAME" = T2."STAF_CODE")

HTH


Gary Parker
MIS Data Analyst
Manchester, England