Author |
Topic |
vmurali
Yak Posting Veteran
88 Posts |
Posted - 2007-07-13 : 06:02:09
|
Hi, Actually in my project I need to insert record into a table. I ned to check for condition if null replace the value with 'Unknown'I have written ascase custtype when null then 'Unknown' when 1 then 'AAA' else 'BB' end but its not working how do we check for nulls |
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-07-13 : 06:34:55
|
use ISNULL() or Coalesce() function:ISNULL(custtype, 'Unknown') Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-13 : 08:30:02
|
Always use CASE WHEN expression instead of CASE expression WHENcase when custtype is null then 'Unknown' when custtype = 1 then 'AAA' else 'BB' end MadhivananFailing to plan is Planning to fail |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-13 : 08:33:28
|
quote: Originally posted by madhivanan Always use CASE WHEN expression instead of CASE expression WHEN
what's the reasoning behind this? please educate me. elsasoft.org |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-13 : 08:40:51
|
quote: Originally posted by jezemine
quote: Originally posted by madhivanan Always use CASE WHEN expression instead of CASE expression WHEN
what's the reasoning behind this? please educate me. elsasoft.org
Why not? Referhttp://sqlteam.com/forums/topic.asp?TOPIC_ID=86290MadhivananFailing to plan is Planning to fail |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-13 : 09:04:01
|
i agree with our prematurer on this one.i have to say that using case when is more readable. at least for me._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-13 : 09:04:18
|
it just seems like a blanket statement to me. it's like saying, "never use the switch/case statement in C++, only use if/else if". sometimes one or the other makes sense.I guess you are saying one is more error-prone than the other? elsasoft.org |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-13 : 09:25:28
|
acctully case @var when ... did behave strangly on occasion but that was my error and i rewrote it using case when @var ....that's why i don't like to use the first one anymore because i like the other version better.simple as that._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-13 : 23:48:03
|
quote: Originally posted by jezemine it just seems like a blanket statement to me. it's like saying, "never use the switch/case statement in C++, only use if/else if". sometimes one or the other makes sense.I guess you are saying one is more error-prone than the other? elsasoft.org
Did I suggest to use IF..ELSE.. instead of CASE WHEN...? Self-explained example1Select case when value>=1000 then 'something1' when value>=2000 then 'something2'.. end 2Select case value when>=1000 then 'something1' when>=2000 then 'something2'.. end Will 2 work correctly?MadhivananFailing to plan is Planning to fail |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-14 : 14:17:41
|
no it won't, because you aren't using "case" right in that "case". I wasn't looking for education on the syntax - I know the syntax. I guess you mean not to use it simply because the other form can be used in all cases, whereas the case expression when syntax can only be used when testing for equality of a single expression among different values, so it's more restricted. In that sense, it's like switch/case in C++ (hence my comment above). elsasoft.org |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-07-16 : 02:26:27
|
quote: Originally posted by jezemine no it won't, because you aren't using "case" right in that "case". I wasn't looking for education on the syntax - I know the syntax. I guess you mean not to use it simply because the other form can be used in all cases, whereas the case expression when syntax can only be used when testing for equality of a single expression among different values, so it's more restricted. In that sense, it's like switch/case in C++ (hence my comment above). elsasoft.org
Exactly The newbies who start with Simple CASE function: as explained in BOL, try to similate the same for non-equality as well. Thats why I suggest CASE WHEN for checking for everything MadhivananFailing to plan is Planning to fail |
|
|
saqi
Starting Member
1 Post |
Posted - 2007-08-04 : 12:46:39
|
set ansi_nulls off --ondbcc useroptionsdeclare @num char(10)declare @value varchar(10)select @numset @value = case @num when null then 'null' else 'not null' end select @valueThe above code will just work fine. The code wont work when you set the ansi_nulls setting to ON. And thats wht the reason your case statement is failing. FYI, SQL 2K has this setting set to ON.saq |
|
|
|