SQL Where Clause Help (SQL Server)

I am having a problem with a SQL Where Clause. There are 3 tables involved (Inventory, Invbalance, and invreserve). The inventory table contains a numeric value in one of its columns (curbal). I want the where clause to return records from the inventory table if the value in the curbal column is greater than or equal to the sum of a column in the invbalance table minus the sum of a column in the invreserve table. Following is the SQL that I've used:

Select itemnum
    from inventory
where minlevel >=
   (select sum(curbal)
         from invbalances
         where siteid=inventory.siteid
         and location=inventory.location
         and itemsetid=inventory.itemsetid
         and itemnum=inventory.itemnum)
   -
   (select sum(reservedqty)
           from invreserve
           where storelocsiteid=inventory.siteid
           and location=inventory.location
           and itemsetid=inventory.itemsetid
           and itemnum=inventory.itemnum)

A problem occurs if the invreserve table doesn't have any row in it. If that's the case, I'd like the sum for that table to be 0 and the comparison would just be to the sum in the invbalance table. It appears, instead, that the arithmetic just doesn't work. If there are no rows in the invreserve table, the where clause returns nothing.

I guess the question is, how can I set the sum for the invreserve table to 0 if there are no rows?
cbridgmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Matthew KellyCommented:
Use an SQL CASE statement: http://technet.microsoft.com/en-us/library/ms181765.aspx

Select itemnum 
    from inventory
    where minlevel >=
     (CASE WHEN 
         (SELECT COUNT(reservedqty) from invreserve
           where storelocsiteid=inventory.siteid 
           and location=inventory.location 
           and itemsetid=inventory.itemsetid 
           and itemnum=inventory.itemnum) 
     THEN 
        (select sum(curbal) 
         from invbalances 
         where siteid=inventory.siteid 
         and location=inventory.location 
         and itemsetid=inventory.itemsetid 
         and itemnum=inventory.itemnum) 
   ELSE
        ((select sum(curbal) 
         from invbalances 
         where siteid=inventory.siteid 
         and location=inventory.location 
         and itemsetid=inventory.itemsetid 
         and itemnum=inventory.itemnum)
        - 
        (select sum(reservedqty)
           from invreserve
           where storelocsiteid=inventory.siteid 
           and location=inventory.location 
           and itemsetid=inventory.itemsetid 
           and itemnum=inventory.itemnum))
END)

Open in new window

0
PortletPaulfreelancerCommented:
I would suggest you don't use correlated subqueries, instead use outer joined subqueries. Then apply the ISNULL() function to the wanted values for those summed values in the where clause. Like this:
SELECT
        inventory.itemnum
FROM inventory
LEFT JOIN (
                SELECT
                         siteid
                       , location
                       , itemsetid
                       , itemnum
                       , SUM(curbal) AS sum_curbal
               FROM invbalances
               GROUP BY
                         siteid
                       , location
                       , itemsetid
                       , itemnum
            ) AS IB ON inventory.siteid = IB.siteid
                        AND inventory.location = IB.location
                        AND inventory.itemsetid = IB.itemsetid
                        AND inventory.itemnum = IB.itemnum
LEFT JOIN (
                SELECT
                  storelocsiteid
                , location
                , itemsetid
                , itemnum
                , SUM(reservedqty) AS sum_reservedqty
                FROM invreserve
               GROUP BY
                  storelocsiteid
                , location
                , itemsetid
                , itemnum
            ) AS IR ON inventory.siteid = IR.storelocsiteid
                        AND inventory.location = IR.location
                        AND inventory.itemsetid = IR.itemsetid
                        AND inventory.itemnum = IR.itemnum
WHERE inventory.minlevel >= ( ISNULL(IB.sum_curbal,0) - ISNULL(IR.sum_reservedqty,0) )

Open in new window

{+edit} By the way, using this approach you ahve the potential to include those summed values in the output, e.g.:
SELECT
        inventory.itemnum
      , ISNULL(IB.sum_curbal,0) AS sum_curbal
      , ISNULL(IR.sum_reservedqty,0) AS sum_reservedqty
FROM inventory
...

Open in new window

0
hnasrCommented:
Try: Modified last expression in your code: IsNull(...,0)

Select itemnum
    from inventory
where minlevel >=
   (select sum(curbal)
         from invbalances
         where siteid=inventory.siteid
         and location=inventory.location
         and itemsetid=inventory.itemsetid
         and itemnum=inventory.itemnum)
   -
   IsNull((select sum(reservedqty)
           from invreserve
           where storelocsiteid=inventory.siteid
           and location=inventory.location
           and itemsetid=inventory.itemsetid
           and itemnum=inventory.itemnum),0)

Open in new window

0
PortletPaulfreelancerCommented:
There are other potential benefits of using joins, and these results I hope demonstrate this:
|        NOTE | ITEMNUM | SUM_CURBAL | SUM_RESERVEDQTY |
|-------------|---------|------------|-----------------|
| join method |       1 |          0 |               0 |
| join method |       2 |          0 |               0 |
| join method |       3 |          0 |               0 |

|                       NOTE | ITEMNUM | SUM_CURBAL | SUM_RESERVEDQTY |
|----------------------------|---------|------------|-----------------|
| correlated subquery method |       1 |          0 |               0 |
| correlated subquery method |       2 |          0 |               0 |

Open in new window

If an item in inventory is not present in the other 2 tables, it is still picked up by the join approach, but not by the correlated subquery approach. Full comparison here:
    CREATE TABLE inventory
    	([siteid] int, [location] int, [itemsetid] int, [itemnum] int, [minlevel] int)
    ;
    	
    INSERT INTO inventory
    	([siteid], [location], [itemsetid], [itemnum], [minlevel])
    VALUES
    	(1, 1, 1, 1, 5),
    	(2, 2, 2, 2, 5),
    	(3, 3, 3, 3, 5)
    ;
    
    CREATE TABLE invbalances
    	([siteid] int, [location] int, [itemsetid] int, [itemnum] int, [curbal] int)
    ;
    	
    INSERT INTO invbalances
    	([siteid], [location], [itemsetid], [itemnum], [curbal])
    VALUES
    	(1, 1, 1, 1, 0),
    	(2, 2, 2, 2, 0)
    ;
    
    CREATE TABLE invreserve
    	([storelocsiteid] int, [location] int, [itemsetid] int, [itemnum] int, [reservedqty] int)
    ;
    	
    INSERT INTO invreserve
    	([storelocsiteid], [location], [itemsetid], [itemnum], [reservedqty])
    VALUES
    	(1, 1, 1, 1, 0)
    ;

**Query 1**:

    SELECT
             'join method' as note
           , inventory.itemnum
           , ISNULL(sum_curbal,0)      AS sum_curbal
           , ISNULL(sum_reservedqty,0) AS sum_reservedqty
    FROM inventory
    LEFT JOIN (
                    SELECT
                             siteid
                           , LOCATION
                           , itemsetid
                           , itemnum
                           , SUM(curbal) AS sum_curbal
                   FROM invbalances
                   GROUP BY
                             siteid
                           , LOCATION
                           , itemsetid
                           , itemnum
                ) AS IB ON inventory.siteid = IB.siteid
                            AND inventory.LOCATION = IB.LOCATION
                            AND inventory.itemsetid = IB.itemsetid
                            AND inventory.itemnum = IB.itemnum
    LEFT JOIN (
                    SELECT
                      storelocsiteid
                    , LOCATION
                    , itemsetid
                    , itemnum
                    , SUM(reservedqty) AS sum_reservedqty
                    FROM invreserve
                   GROUP BY
                      storelocsiteid
                    , LOCATION
                    , itemsetid
                    , itemnum
                ) AS IR ON inventory.siteid = IR.storelocsiteid
                            AND inventory.LOCATION = IR.LOCATION
                            AND inventory.itemsetid = IR.itemsetid
                            AND inventory.itemnum = IR.itemnum
    WHERE inventory.minlevel >= ( ISNULL(IB.sum_curbal,0) - ISNULL(IR.sum_reservedqty,0) )
    

**[Results][2]**:
    
    |        NOTE | ITEMNUM | SUM_CURBAL | SUM_RESERVEDQTY |
    |-------------|---------|------------|-----------------|
    | join method |       1 |          0 |               0 |
    | join method |       2 |          0 |               0 |
    | join method |       3 |          0 |               0 |


**Query 2**:

    SELECT
               'correlated subquery method' as note
              , itemnum
              ,    (SELECT sum(curbal)
                       FROM invbalances
                       WHERE siteid=inventory.siteid
                       AND LOCATION=inventory.LOCATION
                       AND itemsetid=inventory.itemsetid
                       AND itemnum=inventory.itemnum) AS sum_curbal
              , IsNull((SELECT sum(reservedqty)
                         FROM invreserve
                         WHERE storelocsiteid=inventory.siteid
                         AND LOCATION=inventory.LOCATION
                         AND itemsetid=inventory.itemsetid
                         AND itemnum=inventory.itemnum),0) AS sum_reservedqty
        FROM inventory
    WHERE minlevel >=
       (SELECT sum(curbal)
             FROM invbalances
             WHERE siteid=inventory.siteid
             AND LOCATION=inventory.LOCATION
             AND itemsetid=inventory.itemsetid
             AND itemnum=inventory.itemnum)
       -
       IsNull((SELECT sum(reservedqty)
               FROM invreserve
               WHERE storelocsiteid=inventory.siteid
               AND LOCATION=inventory.LOCATION
               AND itemsetid=inventory.itemsetid
               AND itemnum=inventory.itemnum),0)
    

**[Results][3]**:
    
    |                       NOTE | ITEMNUM | SUM_CURBAL | SUM_RESERVEDQTY |
    |----------------------------|---------|------------|-----------------|
    | correlated subquery method |       1 |          0 |               0 |
    | correlated subquery method |       2 |          0 |               0 |



  [1]: http://sqlfiddle.com/#!3/2b6ea/3

  [2]: http://sqlfiddle.com/#!3/2b6ea/3/0

  [3]: http://sqlfiddle.com/#!3/2b6ea/3/1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.