Problem totaling in SQL Query

[erp84]

Can anyone identify any problems with this query? The last little piece of my select you can see I'm adding T1.LABOR, T2.PURCHASESTOT, and T3.MOCOST. Everything seems to work great, except for when one of these fields are empty/zero, my total will always be zero. If all fields have a value other than zero, it works just fine. Ideas?

Spoiler

SELECT T1.PROJECT, T1.labhr as LaborHrs, T1.LABOR as LaborCost, T2.PURCHASESTOT as BUYSTOT, T2.PURCHASES as BUYS, T3.MOCOST as MOTOT, T1.LABOR+T2.PURCHASESTOT+T3.MOCOST AS TOT ;
FROM (SELECT project.project, sum(ltxnhist.actlab) AS Labhr, SUM(ltxnhist.actlab*ltxnhist.rate) AS Labor FROM project LEFT OUTER JOIN ltxnhist ON project.project=ltxnhist.project WHERE PROJECT.PROJECT = TAB1.PROJECT GROUP BY project.project) T1;
LEFT JOIN (SELECT project.project, SUM(podet.uprice*podet.qtyord) AS PURCHASESTOT, SUM(podet.uprice*podet.qtyinv) AS Purchases FROM project LEFT OUTER JOIN podet on project.project=podet.project WHERE PROJECT.PROJECT = TAB1.PROJECT GROUP BY project.project) T2;
ON T1.PROJECT=T2.PROJECT;
LEFT JOIN (SELECT project.project, SUM(wpm.fstot*wpm.qtydel) as MOCOST FROM ((project INNER JOIN momast ON project.project=momast.project) INNER JOIN wpm ON momast.mono=wpm.mono) WHERE PROJECT.PROJECT = TAB1.PROJECT GROUP BY project.project) T3;
ON T1.PROJECT=T3.PROJECT;
INTO CURSOR TAB2

 

[philhege]

(I wish you didn't use the "Spoiler" wrapper for your code.)

In the DB world, NULL (any operator) (any other value) = NULL. So you need to handle blank/null values in the fields that need to be operated on.

-----------
With business clients like mine, you'd be better off herding cats.

 

[Dan Freeman]

Yeah, it's a null issue most likely.

You refer to fields as "zero/empty" as if it's the same condition but they're really two ENTIRELY different values. Zero is an actual value. Null (empty) is best thought of as "I don't know".

When you sum any number and zero, it's a knowable answer.

When you sum any number and "I don't know", the only answer is "I don't know". Take a look at Nvl() in the help file to get you out of this.

 

[Olaf Doschke]

I agree about the suspicion of NULLs. But you say you get 0. Maybe you just see 0, because SET("NULLDISPLAY") is 0 and you want non joined records to be added as 0 amount, then NVL(T1.LABOR,0)+NVL(T2.PURCHASESTOT)+NVL(T3.MOCOST,0) is your solution.

You see, even if those fields are defined to not allow NULL values, you do two LEFT Joins, and these always take the data of the LEFT side table and eventually join data of the right side table, if it's found by the join condition, else all NULLs are joined to the data of the left side table.

It's no solution to change to INNER joins in this case, as you then also won't get the data of the top and/or middle level in this hierarchy of tables.

JOINS are a reason you have to know how to handle NULLs even in case your database table normalisation does not allow NULL values in any single table field.

Bye, Olaf.

 

[erp84]

Thanks guys, have it working perfectly now!

I did have NULLDISPLAY set to 0, and Olaf's suggestion did work with the added NVL function.

 

[Olaf Doschke]

I hope you catched my error. In NVL(T2.PURCHASESTOT) a ",0" slipped me.

Bye, Olaf.