SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Iif syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sportsguy
Starting Member

USA
38 Posts

Posted - 01/23/2013 :  20:44:14  Show Profile  Reply with Quote
Query is giving an error at IIF
I 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,

sportsguy

MS Access 20 years, SQL hack

sportsguy
Starting Member

USA
38 Posts

Posted - 01/23/2013 :  21:06:09  Show Profile  Reply with Quote
I know this works, so as to not think i am a total SQL idiot


CASE WHEN hdr.CONTRACT_NUMBER Is Null THEN Null ELSE IsNull(hdr.CONTRACT_NUMBER_MODIFIER,'Null') END AS "MODIFIER"


sportsguy

MS Access 20 years, SQL hack
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17587 Posts

Posted - 01/23/2013 :  21:20:34  Show Profile  Reply with Quote
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.aspx

quote:

Syntax


IIF ( boolean_expression, true_value, false_value )

Arguments

boolean_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
Time is always against us

Go to Top of Page

sportsguy
Starting Member

USA
38 Posts

Posted - 01/24/2013 :  12:06:52  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 01/24/2013 :  12:10:33  Show Profile  Reply with Quote
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..

Edited by - Lamprey on 01/24/2013 12:11:29
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/25/2013 :  01:24:29  Show Profile  Reply with Quote
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 one

IIf(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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000