Outer join doesn't work for transaction table

[JanTeb]

I am fairly new to crystal 9.0 and I am trying to create a report that has 2 tables (SQL 7.0). A master table of vehicles and a transaction table of costs that are connected by an outer join. As soon as I make a date restriction on the transaction table the outer join doesn’t work any more.
For example:
Running report for Feb 2004
Veh 1 has transaction in Feb 2004 fuel cost and maintenance cost– works fine
Veh 2 has no transactions in Feb but does have for other months – doesn’t appear in the report
Veh 3 has no transactions at all – shows up because of the additional where statement of checking for null dates
Veh 4 has transactions in Feb but has been sold March 3 – want it to show up since it has costs (status = Sold, StatusDate = 2004/03/03)
Veh 5 has transactions but not in Feb. since it was sold Jan 10 – do not want it to show up (status = Sold, StatusDate = 2004/01/10)


Here is the SQL that I had to try to get the first 3 vehicles working. I hadn’t even tried to get the last 2 yet, but I will need to.

SELECT "DATA_ENTITIES_COH"."ENT_Key", "DATA_ENTITIES_COH"."ENT_Make", "DATA_ENTITIES_COH"."ENT_Model",
"DATA_MONTHLY_COSTS_COH"."MthCost_Facility_Cost",
"DATA_MONTHLY_COSTS_COH"."MthCost_Admin_Cost",
"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate", "DATA_ENTITIES_COH"."Status",
"DATA_ENTITIES_COH"."StatusDate"
FROM {oj "PWDW_DATAWAREHOUSE"."PWDW"."DATA_ENTITIES_COH" "DATA_ENTITIES_COH"
LEFT OUTER JOIN "PWDW_DATAWAREHOUSE"."PWDW"."DATA_MONTHLY_COSTS_COH" "DATA_MONTHLY_COSTS_COH"
ON "DATA_ENTITIES_COH"."ENT_Key"="DATA_MONTHLY_COSTS_COH"."ENT_Key"}
WHERE ("DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate">={?StartDate}
AND "DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"<{?EndDate})
ORDER BY "DATA_ENTITIES_COH"."ENT_Key"

If anyone could help me figure out how to get all master records to show up even if it does not have transactions, I would be very grateful because I run into this a lot lately.

 

[GJParker]

Veh 3 has no transactions at all – shows up because of the additional where statement of checking for null dates

In the SQL you have published you are not checking for Nulls.

Is this SQL generated by crystal or are you using a sql command to run your report ?

you would be better to let crystal generate the SQl and enter the filter criteria in as the select formulu i.e.

isnull({("DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate}) or
({"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"} >={?StartDate}
AND {"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"}<{?EndDate})

HTH


Gary Parker
Systems Support Analyst
Manchester, England

 

[JanTeb]

Sorry I was trying different things. I did have the null check in there but it didn't always work. Worked for veh 3 scenario but not veh 2.

 

[GJParker]

Ok I see a little bit clearer now.

in your report you want to show all vehicles form the master table but only transactions during the prompted period ?

You need to remove the date filter and return all the records to your report.

Then create formulas for your costs.

//@FacilityCosts
If ({"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"} >={?StartDate}
AND {"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"}<{?EndDate}) Then
{"DATA_MONTHLY_COSTS_COH"."MthCost_Facility_Cost"}
Else
0

//@AdminCosts
if ({"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"} >={?StartDate}
AND {"DATA_MONTHLY_COSTS_COH"."MthCost_ReportDate"}<{?EndDate}) Then
{"DATA_MONTHLY_COSTS_COH"."MthCost_Admin_Cost"}
Else
0

You shgould then be able to use these formulas in your report Group by Vuhicle and summarise.

HTH


Gary Parker
Systems Support Analyst
Manchester, England

 

[tektipdjango]

try this

SELECT "DATA_ENTITIES_COH"."ENT_Key", "DATA_ENTITIES_COH"."ENT_Make", "DATA_ENTITIES_COH"."ENT_Model", 
"MthCost_Facility_Cost", 
"MthCost_Admin_Cost", 
"MthCost_ReportDate", 
"DATA_ENTITIES_COH"."Status",
"DATA_ENTITIES_COH"."StatusDate"
 FROM   
"PWDW_DATAWAREHOUSE"."PWDW"."DATA_ENTITIES_COH" "DATA_ENTITIES_COH" 
LEFT OUTER JOIN 
(select
"MthCost_Facility_Cost", 
"MthCost_Admin_Cost", 
"MthCost_ReportDate",
"ENT_Key"
from
"PWDW_DATAWAREHOUSE"."PWDW"."DATA_MONTHLY_COSTS_COH"
 WHERE  ("MthCost_ReportDate">={?StartDate} 
AND "MthCost_ReportDate"<{?EndDate}) as costsBetweenStartEndDate
ON "DATA_ENTITIES_COH"."ENT_Key"=costsBetweenStartEndDate."ENT_Key"
ORDER BY "DATA_ENTITIES_COH"."ENT_Key"

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​