Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
sportsguy
Starting Member
39 Posts |
Posted - 2013-01-23 : 20:44:14
|
Query is giving an error at IIFI am not sure of the syntax for IIF evaluating a Null field.What is the correct context for the evaluation part??Neither of these work. . .IIf(hdr.CONTRACT_NUMBER = Null, Null,IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null')) AS "MODIFIER"IIf(hdr.CONTRACT_NUMBER Is Null, Null,IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null')) AS "MODIFIER" thanks, sportsguyMS Access 20 years, SQL hack |
|
sportsguy
Starting Member
39 Posts |
Posted - 2013-01-23 : 21:06:09
|
I know this works, so as to not think i am a total SQL idiotCASE WHEN hdr.CONTRACT_NUMBER Is Null THEN Null ELSE IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null') END AS "MODIFIER" sportsguyMS Access 20 years, SQL hack |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-01-23 : 21:20:34
|
What is the error message ?i don't have SQL 2012 to confirm but according to the BOL, the true or false part should be a VALUE, meaning you can' have an expression like IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null')http://msdn.microsoft.com/en-us/library/hh213574.aspxquote:
SyntaxIIF ( boolean_expression, true_value, false_value )Argumentsboolean_expression A valid Boolean expression. If this argument is not a Boolean expression, then a syntax error is raised.true_value Value to return if boolean_expression evaluates to true.false_value Value to return if boolean_expression evaluates to false.
KH[spoiler]Time is always against us[/spoiler] |
|
|
sportsguy
Starting Member
39 Posts |
Posted - 2013-01-24 : 12:06:52
|
Doesn't IS Null return a boolean expression, True or False?In the Case Statement, IS Null returns a Boolean answer, NO??I am OK with the CASE, was just trying to use this as a quicker shortcut. . .MS Access 20 years, SQL hack |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-24 : 12:10:33
|
quote: Originally posted by sportsguy Doesn't IS Null return a boolean expression, True or False?In the Case Statement, IS Null returns a Boolean answer, NO??I am OK with the CASE, was just trying to use this as a quicker shortcut. . .MS Access 20 years, SQL hack
No, ISNULL is a very poorly named fucntion. It takes two parameters and returns teh first non-null value (or null if they are both null). There are debates as to which funciton is better to use, but I'd suggest using COALESCE instead of ISNULL. They do, basically, the same thing. However, COALESCE is ANSI compliant and it accepts N-number of paramters (instead of jsut two).EDIT: Nenver mind. I read your questuion as about ISNULL not about the IS NULL operator.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-25 : 01:24:29
|
quote: Originally posted by sportsguy Doesn't IS Null return a boolean expression, True or False?In the Case Statement, IS Null returns a Boolean answer, NO??I am OK with the CASE, was just trying to use this as a quicker shortcut. . .MS Access 20 years, SQL hack
the IIF for your requirement is second oneIIf(hdr.CONTRACT_NUMBER Is Null, Null,IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null')) AS "MODIFIER"=NULL wont work under default conditions as NULL is not stored as a value and cant be used with operators like =,>,< etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|