November 21, 2011 at 3:29 pm
I have a fairly long select statment that returns a SUM of values.
I want to check if the value returned is null, if it is then use 0, else use the value from the SUM expression.
Here is a simple example:
DECLARE @TestTable TABLE(
[ID] [int] IDENTITY(1,1) NOT NULL,
[SomeKey] [int] NOT NULL,
[SomeValue] [int] NOT NULL);
INSERT INTO @TestTable (SomeKey, SomeValue) VALUES(1,2);
INSERT INTO @TestTable (SomeKey, SomeValue) VALUES(1,3);
SELECT
CASE
WHEN SUM(SomeValue) IS NOT NULL
THEN SUM(SomeValue)
ELSE 0
END
FROM
@TestTable
WHERE SomeKey = 2
The entire query is very long and in the interest of keeping it more readable and managable I was wodering if there was a kind of shortcut using the case statement where I wouldn't have to enter the really long SUM expression twice.
SELECT
CASE
WHEN SUM(Really long sql query) IS NOT NULL
THEN SUM(Really long sql query)
ELSE 0
END
FROM
@TestTable
WHERE SomeKey = 2
Thanks!
November 21, 2011 at 3:48 pm
isNull(sum(Really Long Select), 0)
or put the really long select result into a variable and do the same with the variable
November 22, 2011 at 8:49 am
Or COALESCE
SELECT COALESCE(SUM(SomeValue),0) FROM
http://databases.aspfaq.com/database/coalesce-vs-isnull-sql.html
November 22, 2011 at 9:14 am
Thanks Burninator and Chrissy321, that did the trick.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply