June 7, 2013 at 11:56 am
I have a CASE statement with a subquery that works most of the time but if the subquery comes back with zero rows, I get a null and I want that null to be a zero instead.
How would I change it to handle that.
CASE WHEN TypeId = 8
THEN ( SELECT Balance
FROM InvoicePayments
WHERE InvoicePaymentIdId = dd.InvoicePmtId
)
ELSE CAST(0.00 AS money)
END AS BalanceDue ,
I tried putting an ISNULL around the subquery and it just returned nothing in that case.
Thanks,
Tom
June 7, 2013 at 12:06 pm
The case statement is part of the result set from the SELECT.
Not sure why, this doesn't work:
SELECT CASE WHEN TypeId = 8
THEN ISNULL(( SELECT Balance
FROM InvoicePayments
WHERE InvoicePaymentIdId = dd.InvoicePmtId
),0.00)
ELSE CAST(0.00 AS money)
END AS BalanceDue
FROM table
It runs but instead of returning the 3 rows it did before the ISNULL, which returned BalanceDue = NULL, it doesn't return the rows at all.
I could understant that if it were part of a JOIN or Where clause, but not in the result set.
Thanks,
Tom
June 7, 2013 at 12:11 pm
It apparently was working.
It was part of another query that removed rows where BalanceDue not null.
Thanks,
Tom
June 10, 2013 at 2:48 pm
tshad (6/7/2013)
The case statement is part of the result set from the SELECT.Not sure why, this doesn't work:
SELECT CASE WHEN TypeId = 8
THEN ISNULL(( SELECT Balance
FROM InvoicePayments
WHERE InvoicePaymentIdId = dd.InvoicePmtId
),0.00)
ELSE CAST(0.00 AS money)
END AS BalanceDue
FROM table
It runs but instead of returning the 3 rows it did before the ISNULL, which returned BalanceDue = NULL, it doesn't return the rows at all.
I could understant that if it were part of a JOIN or Where clause, but not in the result set.
Thanks,
Tom
Is the main table you are querying the InvoicePayments table or a different table? Could you provide some sample data?
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
June 11, 2013 at 2:30 am
Please provide some sample data with table structure
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 11, 2013 at 3:12 am
tshad (6/7/2013)
I have a CASE statement with a subquery that works most of the time but if the subquery comes back with zero rows, I get a null and I want that null to be a zero instead.How would I change it to handle that.
CASE WHEN TypeId = 8
THEN ( SELECT Balance
FROM InvoicePayments
WHERE InvoicePaymentIdId = dd.InvoicePmtId
)
ELSE CAST(0.00 AS money)
END AS BalanceDue ,
I tried putting an ISNULL around the subquery and it just returned nothing in that case.
Thanks,
Tom
Your code is better to be rewirttten to replace subquery with a outer join:
...
CASE WHEN TypeId = 8
THEN ISNULL( ip.Balance, 0)
ELSE CAST(0.00 AS money)
END AS BalanceDue ,
...
FROM [yourtable] AS dd
LEFT JOIN InvoicePayments AS ip ON ip.InvoicePaymentIdId = dd.InvoicePmtId
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply