sqlsql-serversubqueryisnull

How to use ISNULL result in a calculation involving a subquery


I have a SQL script that uses a subquery, the result of the subquery can sometimes be NULL so I've used ISNULL to set the result to zero (0) in those cases.

The problem I have to solve is that I need to use that zero in a calculation. The calculation is to see if the system has enough inventory of a specific item, so its basically: Quantity In Stock - Quantity Needed = Balance

If the subquery returns 0 and qty. 8 is needed then the result should be -8 but currently the result will just show as 0 because of the ISNULL function.

ISNULL((
    SELECT
        SUM(ILE.[Remaining Quantity]) - CTE.[ExtendedQuantity] 
    FROM [Item Ledger Entry] ILE
    GROUP BY ILE.[Item No_]
    HAVING (CTE.[ItemNo] = ILE.[Item No_])
),0) AS [Balance]

I tried to use a case expression but wasn't exactly sure how to structure that with the subquery to make it work, it still may be possible.

Current Result:

Item No. Qty. In Stock Qty. Needed Balance
017030 0 8 0

Desired Result:

Item No. Qty. In Stock Qty. Needed Balance
017030 0 8 -8

I think I need to modify how I'm using the ISNULL in some way just not sure what approach to try, please let me know if anything more is needed.


Solution

  • isnull() should only applied to your subquery

    ISNULL((
        SELECT 
            SUM(ILE.[Remaining Quantity]) 
        FROM  
            [Item Ledger Entry] ILE
        WHERE 
            CTE.[ItemNo] = ILE.[Item No_]
    ),0) - CTE.[ExtendedQuantity]  AS [Balance]