November 12, 2009 at 11:57 am
Hi,
I'm afraid it is another of my newbie questions, thank you for your patience.
I've seen lots of queries asking about replacing the NULL result of SUM with zero but I want to do the opposite.
My situation is I'm writing some code to summarise positions, part of this retrieves a valuation rate from other tables. Now it is possible this valuation rate is not yet in the database. In this situation I want to notify the user that there is a problem with the valuation.
The script below creates a temp table that contains the fields I need to summarise.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#myValns','U') IS NOT NULL
DROP TABLE #myValns
--===== Create the test table with
CREATE TABLE #myValns
(
IDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
ValueDateDATE,
MarketCHAR(3),
AmountDECIMAL,
RateDECIMAL
)
--===== Setup any special required conditions especially where dates are concerned
SET DATEFORMAT DMY
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #myValns ON
--===== Insert the test data into the test tables
INSERT INTO #myValns
(ID, ValueDate, Market, Amount, Rate)
SELECT 1,'Jun 6 2010','ABC',100,10 UNION ALL
SELECT 2,'Jul 6 2010','ABC',200,15 UNION ALL
SELECT 3,'Jun 7 2010','XYZ',10,10 UNION ALL
SELECT 4,'Jul 7 2010','XYZ',20,NULL
SET IDENTITY_INSERT #myValns OFF
--SETUP SCRIPT ENDS
If I use the following SQL:
--Summarise our valuations Note this ignores our XYZ Amounts to Jul 7 2010
SELECT Market, SUM(Rate*Amount) AS "Value" FROM #myValns
GROUP BY Market
SUM hides the fact that I have no valuation rate for Market XYZ to 'Jul 7 2010' (table row 4)
What Ideally want to do is something like:
SELECT Market, SUM(CASE WHEN Rate IS NULL THEN RAISERROR(N'Missing Rate',10,1) ELSE Rate*Amount END) AS "Value" FROM #myValns
GROUP BY Market
But this is rejected by the parser with a message "Incorrect syntax near the keyword 'RAISERROR'."
How can I get SUM to either throw an error or return NULL if a single NULL exists in the results set it is evaluating?
Thanks in advance,
Jeremy
November 12, 2009 at 12:26 pm
Do the checking to see if you have NULL values first. Assuming there are no nulls, THEN run the sum.
in other words:
IF EXISTS (select * from #myValns where rate is null)
Raiserrror blah
ELSE
select sum(Rate*Amount ) AS "Value" FROM #myValns
GROUP BY Market
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply