Outer join returns no results (Impromptu Admin)

[Capoerista]

Hi Everyone,

I am trying to the following outer join :

select T1."TERRITORY_CODE" "c1" , T1."TERRITORY_DESC" "c2" , T2."REP_MANAGER_CODE" "c3" , T2."REP_MANAGER_DESC" "c4"
from {oj "XLOOK_TERRITORIES" T1 LEFT OUTER JOIN "XLOOK_REP_MANAGER" T2 on T1."REP_MANAGER_CODE" = T2."REP_MANAGER_CODE" }
order by 1 asc

Using:

Impromptu Admin 7.1.529.0
Easysoft ODBC Bridge
Windows 2K

The Easysoft documentation states that it supports ODBC style outer join syntax and indeed runs fine if I paste it into WinSQL. However when I run with Impromptu it returns no rows.

We feel that it is an Impromptu problem but would appreciate any views or ideas on how to investigate further.

Thanks

Ade

 

[griffindm]

Ade,

Not sure what's causing the problem, but I suggest you check two things (if you already haven't).

Go to Report | Query | Access and make sure the checkbox for "Include the missing table join values (outer join)" is checked.

Examine your Impromptu filter and make sure that all elements of your filter are expressed in the SQL and that Impromptu is doing no local filtering.

Dave G.




The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Capoerista]

griffindm,

The "Include the missing table join values (outer join)" is checked . I should have said the SQL was that automatically generated by Impromptu.

This is a small query so there are no filters defined. I just tried suspending Detail and Summary filters to make sure and still get the same result. Is there any other filtering that could be happening?

Thanks

Ade

 

[griffindm]

Nothing I can think of, assuming you are connecting in Impromptu with the same ODBC driver you are using in WinSQL. It may be time for a support call. Cognos support is pretty good, so don't be afraid to use it (assuming you have on-going support).

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Capoerista]

Thanks Dave, I think we have support. It is up in the air though as we have just been sold off. So it may take a little while

Ade