Whats wrong with this query?

[Waynest]

Can someone tell me whats wrong with this?
It works when the total returned by each SELECT is > 0, but if either of the 2 SELECTs returns nothing then the GrandTot is NULL (they differ by the OpEmp & OpDate numbers). Obviously I just want an overall total regardless...

SELECT GrandTot =
(SELECT SUM(T.Qty / 12) * (R.Rate * P.StdMinVal) AS TotEarned FROM Ticket T
INNER JOIN Product P ON (T.StyleCode = P.Style AND T.Size = P.Size)
INNER JOIN YieldRate R ON R.Code = P.YieldCode
WHERE (T.OpEmp1 = 'A1898' AND T.OpDate1 = '20010201') AND T.StyleCode = 'AY4' AND T.Size = 3
GROUP BY R.Rate, P.StdMinVal)
+
(SELECT SUM(T.Qty / 12) * (R.Rate * P.StdMinVal) AS TotEarned FROM Ticket T
INNER JOIN Product P ON (T.StyleCode = P.Style AND T.Size = P.Size)
INNER JOIN YieldRate R ON R.Code = P.YieldCode
WHERE (T.OpEmp2 = 'A1898' AND T.OpDate2 = '20010201') AND T.StyleCode = 'AY4' AND T.Size = 3
GROUP BY R.Rate, P.StdMinVal)

 

[foxdev]

If you are doing this in Query Analyzer, try
SET ANSI_NULLS OFF
and see if it makes a difference.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[TomSark]

You need to use UNION or UNION ALL instead of +.
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[Waynest]

Cheers guys, the UNION did the trick.