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.
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]