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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy