Outer Join not working in Impromptu 1

[SharonT]

Hi, I have read a few other threads on this subject and still can't seem to get to the bottom of my problem.

I have three tables in my report: Product, Orders, RMA_Credit

Product to Orders should be an equal join and Orders to RMA_Credit should be the left outer join.

What I am trying to achieve is to see all order lines regardless of whether there are any RMA_Credit entries. However, if I have one order which contains three order lines in my order table, I only see one row returned - the one which does not have an entry in the RMA_Credit table. The other two rows do have an entry in the RMA_Credit table but they are not being displayed in the report.
If the order only has one row and this has an entry on the RMA_Credit table, everything displays correctly.

The SQL in my report is as follows:
select T1.`OrderId`, T1.`Website`, T1.`Name`, T1.`Address1`, T1.`Town`, T1.`Address3`, T1.`Country`, T1.`Postcode`, T1.`Order_Date`, T1.`Despatch_Date`, T1.`Status`, T1.`Product_ID`, T1.`Quantity`, T1.`ProductPrice` - (T1.`ProductPrice` / 100) * 17.5, T1.`Quantity` * (T1.`ProductPrice` - (T1.`ProductPrice` / 100) * 17.5), T3.`RMA_CREDIT_NUM`, T3.`CREDIT_DATE`, T3.`RMA_Credit_Reason`
from `orders` T1 LEFT OUTER JOIN `RMA_CREDIT` T3 on T1.`OrderId` = T3.`OrderID` and T1.`Product_ID` = T3.`Product_ID`
order by T1.`Product_ID`

select T2.`product only`, T2.`Product`, T2.`product_desc`, T2.`unit_quantity`, T2.`unit_cost` - (T2.`unit_cost` / 100) * 17.5, T2.`product_id`
from `product` T2
order by T2.`product_id`

Any ideas greatfully received.

 

[drlex]

Sharon,
I'd have expected to see a more direct link in the SQL between the three tables, like

select T1.`OrderId`, T1.`Website`, T1.`Name`, T1.`Address1`, T1.`Town`, T1.`Address3`, T1.`Country`, T1.`Postcode`, T1.`Order_Date`, T1.`Despatch_Date`, T1.`Status`, T1.`Product_ID`, T1.`Quantity`, T1.`ProductPrice` - (T1.`ProductPrice` / 100) * 17.5, T1.`Quantity` * (T1.`ProductPrice` - (T1.`ProductPrice` / 100) * 17.5), T3.`RMA_CREDIT_NUM`, T3.`CREDIT_DATE`, T3.`RMA_Credit_Reason`, T2.`product only`, T2.`Product`, T2.`product_desc`, T2.`unit_quantity`, T2.`unit_cost` - (T2.`unit_cost` / 100) * 17.5, T2.`product_id`
from "product" T2,
	("Orders" T1 left outer join "rma_credit" T3 on (T1."OrderId" = T3."OrderID") and (T1."Product_ID" = T3."Product_ID"))
where (T1."product_id" = T2."Product_id")

Can you post the join structures between the tables Product, Orders and RMA_credit? It looks like there's no specific join between the first two tables being shown in your SQL.

soi la, soi carré

 

[SharonT]

Not sure how you want me to show this but if I go into "Joins" then "Definitions" in impromptu, it shows:

product.product_id = orders.product_id

orders.orderid = rma_credit.orderid and
orders.product_id = rma_credit.product_id

outer join for table "orders" is ticked.

Thanks for the quick response

 

[drlex]

Hmmm - sounds right. The only other point I can make is that you've got some 'order by's in the SQL.
Can you try and recreate the report without putting any sorting/grouping in it? Just a few fields to check the SQL and output, not all the calculations.

soi la, soi carré

 

[SharonT]

I have tried to do a few simplified versions of the report and found that if I linked "product" to "orders" (and nothing else) I lost a couple of records. A couple of old products had been removed. I changed this link to be an outer join and then got all records from the orders table. Then I did a new report and linked the orders table to the RMA_Credit table. This returned all rows correctly. It was only when I added one column from the "product" table that my records disappeared again. This time (for my three line order) I get one row that does have an entry in the RMA_credit table and one row that doesn't. The other row that does have an entry in the RMA_credit table doesn't show!

The SQL now displays as:

select T1.`OrderId`, T1.`Product_ID`, T1.`Website`, T1.`Name`, T1.`ProductPrice`, T3.`Product`, T3.`unit_quantity`, T3.`unit_cost`
from `orders` T1 LEFT OUTER JOIN `product` T3 on T1.`Product_ID` = T3.`product_id`
order by T1.`OrderId`, T1.`Product_ID`

select T2.`RMA_CREDIT_NUM`, T2.`RMA_CREDIT_ITEM_NUM`, T2.`OrderID`, T2.`CREDIT_DATE`, T2.`Product_ID`
from `RMA_CREDIT` T2
order by T2.`OrderID`, T2.`Product_ID`


Shouldn't there be a "left outer join" reference for the RMA_Credit table? I don't get it!

Sorry for the lengthy waffle and thanks for looking.

 

[drlex]

No problem; you're welcome as I enjoy the forum.
I'm still bothered as to the SQL you get; it doesn't match that which I get when I join three tables in the same fashion and create a report against them. Could you try cutting and pasting the SQL I posted and seeing the result?



soi la, soi carré

 

[GJParker]

Can you post the Magic SQL for the sample report you put together. To do this go tthe profile tab -> SQL -> hold down SHIFT+CTRL+ALT the click the Edit key.



Gary Parker
MIS Data Analyst
Manchester, England

 

[SharonT]

select T1.`OrderId`, T1.`Product_ID`, T1.`Website`, T1.`Name`, T1.`ProductPrice`, T3.`product_id`, T3.`unit_cost`, T3.`unit_quantity`
from `orders` T1 LEFT OUTER JOIN `product` T3 on T1.`Product_ID` = T3.`product_id`
order by T1.`OrderId`, T1.`Product_ID`

select T2.`RMA_CREDIT_NUM`, T2.`RMA_CREDIT_ITEM_NUM`, T2.`RMA_Credit_Reason`, T2.`OrderID`, T2.`Product_ID`
from `RMA_CREDIT` T2
order by T2.`OrderID`, T2.`Product_ID`

Thanks

 

[GJParker]

I think you need to recheck your table links, as thisSQL shows their is no link between the RMA_CREDIT Table and either of the 2 other tables.

Gary

Gary Parker
MIS Data Analyst
Manchester, England

 

[drlex]

Sharon,
Did you try swapping my SQL code into your report?

soi la, soi carré

 

[griffindm]

It may be that Impromptu thinks the underlying database is incapable of doing the outer-join (especially now that the query involves two outer-joins). When this happens, it splits the query in two and does the outer-join locally, and it does NOT show up in the SQL.

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

 

[HelenW01]

I don't know whether you have sorted this yet and this may not be the solution, but looking at the joins you seem to have one too many as you only need 2 joins between 3 tables and you could loose the join product.product_id = orders.product_id. I was told that having too many joins can cause unexpected results

 

[SharonT]

Thanks all

drlex - I did try swapping the code that you gave me and it gave me an error saying that my database driver (MS Access) does not support this type of join. Looks like I have my answer.

griffindm - I think you are right that as I do have two outer joins and it doesn't seem to display them in the SQL.

I think I might have a way around things in Impromptu though as I don't seem to be able to do things the 'easy' way. Fingers crossed.

 

[Jmayhair]

Sharon,

Please post your solution, it may benefit us all.

Thanks

 

[SharonT]

Sorry, I didn't actually have a solution as such, just a work around. My work around was to create two separate 'iqd' files rather than put everything into one file. By doing this I didn't have to make the joins in Impromptu. The impromptu file was the source for my cube so I made the 'join' in Transformer. I would still appreciate a solution to the original problem though for future reference if anyone has one.

Thanks

 

[drlex]

Having set up a similar test in Access using the ODBCJT32 driver (4.00), I get the same result as SharonT for the Profile tab SQL, but the "Magic SQL" shows both left outer joins as set, as does an .iqd file from the report.

Unfortunately I can't edit the Profile tab SQL ("This report cannot be translated into a single database-only query") to match.
I even tried editing the COGDMOD.INI folder but to no avail.

The only way I could get the outer joins to show in the Profile tab SQL was to transfer the data to a SQL server DB and repoint the catalogue.

soi la, soi carré