Impromptu Joins with use of a constant

[swatman]

Hi,

I define a Join with the use of a constant. This works well in SQL, ie

Select *
from TABLE_A A
left join TABLE_B B
on A.col1 = B.Col1
and B.Col2 = '55'

In Impromptu 6, it is possible to define this as a join. However when a report is created the join is not used. 2 seperate selects are generated.

Has anyone seen this?

thanks in advance

swatman

 

[griffindm]

swatman,

I wasn't aware that this was happening. A work-around is to take the constant out of the join and place it as a table filter for the Creator user-class. This should have the effect that you want.

Regards,

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

 

[swatman]

yep, I tried this.

Normally it works, ie

Select * from TABLE_A A left join TABLE_B B
on A.col1 = B.Col1
where B.Col2 = '55'

However this has the effect of an INNER join. So I modify thus:

Select * from TABLE_A A left join TABLE_B B
on A.col1 = B.Col1
where (B.Col2 = '55' or B.Col2 is NULL)

However, in this case, it doesnt work because B.Col2 is never 55 and it is never NULL (ie there are other values).

This is true in raw SQL as well as Impromptu. The only solution I can see, is to get Impromptu to accept the constant in the Join.

ps...I am using V6.0... perhaps this is a bug??

 

[griffindm]

swatman,

If you need an outer join dependent on a constant, just make a view with the data from both sources with the join defined in SQL as you've specified and then place the view into Impromptu. Only way around it I can see.

Regards,

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