Left outer join issue 2

[Harki06]

O am using Crystal Reports 10.

I have 2 tables suplr and prod_trans. For the selected suppliers, I want to print data from prod_trans table. Even if there is no data in the prod_trans table, I want to print zeros. I have grouped by suplr.

For suppliers who have no data, I do not see them on the report as zeros inspite of having a left outer join and
enforced from.

Here is the Database SQL displayed
SELECT "Prod_Trans"."Prod_Trans_Dte", "Suplr"."Suplr_Id", "Suplr"."Suplr_Nm", "Prod_Trans"."Clb_Id", "Prod_Trans"."Div_Amt"
FROM "test"."dbo"."Suplr" "Suplr" LEFT OUTER JOIN "test"."dbo"."Prod_Trans" "Prod_Trans" ON "Suplr"."Suplr_Id"="Prod_Trans"."Suplr_Id"
WHERE ("Suplr"."Suplr_Nm"='Busch' OR "Suplr"."Suplr_Nm"='Gray Line' OR "Suplr"."Suplr_Nm"='Universal')
ORDER BY "Suplr"."Suplr_Nm

 

[Turkbear]

Hi,
If I recall correctly, once you apply any selection
criteria to the Joined table, the Left Outer becomes an Equi join..

Try a sub-report or use a Stored Procedure or View to create the join if the database supports it ( you did not specify which database is being used)

..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Harki06]

Thanks! I am using SQL Server as the database.

 

[synapsevampire]

Remove the where clause, do you see them now?

I thought that the suplr table was the parent in viewing this, but at any rate if you qualify the child table with any conditions you'll overide the left outer.

-k

 

[Turkbear]

Hi,
Good point Synapse..I am still getting used to reading the Left Outer Join standard syntax ( Oracle, until recently, used a non-standard form)..so I missed that the Join seems to be From suplr TO prod_trans..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Harki06]

Suplr is the parent table. If I understood you right, I took out the suplr selection in the parameter list. It still did not show one particular supplier. I know for sure there is nodata for this supplier for current year.

 

[Harki06]

What is interesting I noted is a change in the record selection. I know I am onto something. I have the following as my record selection -
{(if {?Quarter} = 1 then
month({Prod_Trans.Prod_Trans_Dte})) <=3 and
(if {?Quarter} = 2 then
month({Prod_Trans.Prod_Trans_Dte})) <=6 and
(if {?Quarter} = 3 then
month({Prod_Trans.Prod_Trans_Dte})) <=9 and
(if {?Quarter} = 4 then
month({Prod_Trans.Prod_Trans_Dte})) <=12 and
(year({Prod_Trans.Prod_Trans_Dte}) = {?Year} or
year({Prod_Trans.Prod_Trans_Dte}) = {?Previous Year}) and ({Suplr.Suplr_Nm} = {?Partners} or {?Partners} = 'ALL')

If I take out all the code EXCEPT the last statement which is the suplr nm comparison, the suplr shows up. Is the 'or' causing a problem??

 

[Turkbear]

Hi, Nope..
All the rest may be causing the problem.Those criteria are being applied to the CHILD table so the join gets converted to Equi ...Altho', since the criteria are not sent to the database ( they do not appear in your 'Show Sql' ) I am suprised that it still affects the join

This part:
({Suplr.Suplr_Nm} = {?Partners} or {?Partners} = 'ALL')
used the PARENT table so it works as expected.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Harki06]

As soon as I took off the 'or' condition on the year, it is working perfectly. So what does that mean??

 

[synapsevampire]

Note that NONE of your record selection ios being passed to the database, and as Turk says, you're overiding the left outer by putting criteria on the child.

This isn't a Crystal concern, this is how SQL works.

As for the OR being removed, it looks like the cuplrit as it will filter rows on the database, whereas the others would not (you allow for all months).

Try this:
or
(
isnull({Prod_Trans.Prod_Trans_Dte})
or
year({Prod_Trans.Prod_Trans_Dte}) = {?Previous Year})
)

At any rate, you'd probably be better served to create an SP for this.

-k

 

[Harki06]

Thank you very much! I was just trying to limit unecessary data being retrieved...

 

[ianoctdec]

HI,
Would the selection formula written as bellow solve the left outer join issue?

if not isnull("Prod_Trans"."Prod_Trans_Dte") then
(if {?Quarter} = 1 then
month({Prod_Trans.Prod_Trans_Dte})) <=3 and
(if {?Quarter} = 2 then
month({Prod_Trans.Prod_Trans_Dte})) <=6 and
(if {?Quarter} = 3 then
month({Prod_Trans.Prod_Trans_Dte})) <=9 and
(if {?Quarter} = 4 then
month({Prod_Trans.Prod_Trans_Dte})) <=12 and
(year({Prod_Trans.Prod_Trans_Dte}) = {?Year} or
year({Prod_Trans.Prod_Trans_Dte}) = {?PreviousYear}) and ({Suplr.Suplr_Nm}={?Partners} or {?Partners} = 'ALL')
else
true

Daniela

 

[Harki06]

It did not help. Thanks a lot for looking into it!

What I ended up doing is I took off all the conditions on the child table in the record selection above. I added a <= month condition in the report formulas since I have a YTD sum and it would affect that.
This way I am getting my calculations and the zeros for the missing ones. The only thing is the report does have hidden the data for all years. I was trying to limit that.