October 27, 2010 at 1:04 pm
i have a case statment in the stored procedure
when the field is null i would like it to show 0
this is may case statment i keep getting an error,
Msg 174, Level 15, State 1, Line 6
The isnull function requires 2 argument(s).
not sure what im doing incorrectly
SELECT
IB.DIVISION,
IB.SEASON,
IB.STYLE,
IB.BEGINNING_BALANCE,
case when ISNULL(sum(dbo.INV_REC_JAN_JUNE_2010.RCP_QTY)) then 0
else SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) end as rcpt_qty,
case when isnull(SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) then 0
else SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) end as ship_qty,
(IB.BEGINNING_BALANCE+ SUM(dbo.INV_REC_JAN_JUNE_2010.RCP_QTY)-SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY)) AS NEW_BAL
FROM
IB LEFT OUTER JOIN
dbo.INV_SHP_JAN_JUNE_2010 ON IB.STYLE = dbo.INV_SHP_JAN_JUNE_2010.STYLE AND
IB.SEASON = dbo.INV_SHP_JAN_JUNE_2010.SEASON AND
IB.DIVISION = dbo.INV_SHP_JAN_JUNE_2010.COMPANY LEFT OUTER JOIN
dbo.INV_REC_JAN_JUNE_2010 ON IB.STYLE = dbo.INV_REC_JAN_JUNE_2010.style AND
IB.SEASON = dbo.INV_REC_JAN_JUNE_2010.SEASON AND
IB.DIVISION = dbo.INV_REC_JAN_JUNE_2010.COMPANY
October 27, 2010 at 1:15 pm
try this
case when sum(dbo.INV_REC_JAN_JUNE_2010.RCP_QTY) is null then 0
else SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) end as rcpt_qty,
case when SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) is null then 0
else SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY) end as ship_qty,
October 27, 2010 at 2:49 pm
or this:
ISNULL(sum(dbo.INV_REC_JAN_JUNE_2010.RCP_QTY),0) as rcpt_qty,
isnull(SUM(dbo.INV_SHP_JAN_JUNE_2010.SHIP_QTY),0) as ship_qty,
The probability of survival is inversely proportional to the angle of arrival.
October 28, 2010 at 6:42 am
thank you all, i appreciate it
October 28, 2010 at 4:43 pm
CELKO (10/28/2010)
Look up the COALESCE() function. Do not use the old Sybase museum ISNULL(). They are not quite the same and you can get screwed.
Care to give an example and the reason for "you can get screwed", is that your opinion and not based on actual demonstrable T-SQL. An specific example would be appreciated.
October 28, 2010 at 5:20 pm
CELKO (10/28/2010)
Look up the COALESCE() function. Do not use the old Sybase museum ISNULL(). They are not quite the same and you can get screwed.
The museum piece is faster.
http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx
Is it a serious difference? No. But it exists.
Now, COALESCE() takes more arguments and returns the datatype of the highest precedence. ISNULL returns the datatype of the first argument.
However, ISNULL can return a NULL (as an int) without having to do internal casting, whereas COALESCE requires more, if dealing with variants. Rarely done but necessary if you have the possibility.
A further, deeper discussion here, from the SQL Engine team themselves:
http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx
So, since they're not deprecating ISNULL anytime soon, how does he have a chance at being screwed?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply