Adding an integer and a NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding an integer and a NULL

Author

Topic

DrEvilBeer
Starting Member

4 Posts

Posted - 2002-10-04 : 15:29:39

I have a query (below) that I run from within a VB program. My problem is with the portion of code highlighted below in red. Sometimes the first or second part of the statement will produce a NULL value and when added together with a valid integer it produces a NULL value and the record doesn't come up under the condition of "<>" my other quantity because it's a NULL. What I want is for all NULL values to be replaced with "0" so that I get an integer as a result no matter what.

SELECT IV00102.LOCNCODE, IV00102.ITEMNMBR, 
    IV00102.ATYALLOC,
        (SELECT SUM(IV10001.TRXQTY)
      FROM IV10001
      WHERE IV10001.ITEMNMBR = IV00102.ITEMNMBR AND 
           IV10001.TRXLOCTN = IV00102.LOCNCODE
      GROUP BY IV10001.ITEMNMBR, IV10001.TRXLOCTN) 
    AS CalcInvAlloc,
        (SELECT SUM(SOP10200.ATYALLOC)
      FROM SOP10200
      WHERE SOP10200.ITEMNMBR = IV00102.ITEMNMBR AND 
           SOP10200.LOCNCODE = IV00102.LOCNCODE
      GROUP BY SOP10200.ITEMNMBR, SOP10200.LOCNCODE) 
    AS CalcSOPAlloc
FROM IV00102 LEFT JOIN
    IV10001 ON IV00102.LOCNCODE = IV10001.TRXLOCTN AND 
    IV00102.ITEMNMBR = IV10001.ITEMNMBR AND 
    ((IVDOCTYP = 3 AND TRFQTYTY = 1) OR
    (IVDOCTYP = 1 AND TRXQTY < 0)) LEFT JOIN
    SOP10200 ON 
    IV00102.LOCNCODE = SOP10200.LOCNCODE AND 
    IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND 
    (SOPTYPE = 2 OR
    SOPTYPE = 3)
WHERE RCRDTYPE = 2 AND IV00102.ATYALLOC <> 0 AND 
    IV00102.ITEMNMBR >= '0' AND 
    IV00102.ITEMNMBR <= '9999999'
GROUP BY IV00102.LOCNCODE, IV00102.ITEMNMBR, 
    IV00102.ATYALLOC
HAVING IV00102.ATYALLOC <>
        ((SELECT SUM(IV10001.TRXQTY)
       FROM IV10001
       WHERE IV10001.ITEMNMBR = IV00102.ITEMNMBR AND 
           IV10001.TRXLOCTN = IV00102.LOCNCODE
       GROUP BY IV10001.ITEMNMBR, IV10001.TRXLOCTN) 
    +
        (SELECT SUM(SOP10200.ATYALLOC)
      FROM SOP10200
      WHERE SOP10200.ITEMNMBR = IV00102.ITEMNMBR AND 
           SOP10200.LOCNCODE = IV00102.LOCNCODE
      GROUP BY SOP10200.ITEMNMBR, SOP10200.LOCNCODE))
ORDER BY IV00102.ITEMNMBR ASC, IV00102.LOCNCODE ASC

Please let me know if this is unclear or if I need to provide additional information. Thanks!

Edited by - drevilbeer on 10/04/2002 15:31:17

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-10-04 : 15:40:12

Try this:

((SELECT SUM(IsNull(IV10001.TRXQTY,0))
FROM IV10001
WHERE IV10001.ITEMNMBR = IV00102.ITEMNMBR AND
IV10001.TRXLOCTN = IV00102.LOCNCODE
GROUP BY IV10001.ITEMNMBR, IV10001.TRXLOCTN)
+
(SELECT SUM(IsNull(SOP10200.ATYALLOC,0))
FROM SOP10200
WHERE SOP10200.ITEMNMBR = IV00102.ITEMNMBR AND
SOP10200.LOCNCODE = IV00102.LOCNCODE
GROUP BY SOP10200.ITEMNMBR, SOP10200.LOCNCODE))

Jeremy

Edited by - joldham on 10/04/2002 15:41:01

DrEvilBeer
Starting Member

4 Posts

Posted - 2002-10-04 : 17:07:24

I appreciate the help, Jeremy. That function was exactly what I was looking for. Sorry I didn't look though books online more thoroughly!

Brandon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources