September 5, 2013 at 8:56 am
This should be very easy and I am predicting a big forehead slap in just a few minutes after I post this.
I am simply trying to test a field for being null in my SELECT and display a true or false condition. Neither example below works and I've crossed referenced a couple books and looked online as well. No luck.
Thanks for looking!
(MSSL 2008)
SELECT IIF(Field1 IS NULL, 'NULL', 'NOT NULL') *** XX
FROM MyTable
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'IS'.
SELECT IIF(IsNothing(Field1), 'NULL', 'NOT NULL') AS XX
FROM MyTable
Msg 195, Level 15, State 10, Line 5
'IsNothing' is not a recognized built-in function name.
Slap in 5...4....3...
September 5, 2013 at 9:00 am
IIF is not valid syntax for SQL.
--begin edit--
IIF was added to SQL 2012 :angry:
--end edit--
The first statement would be something like this:
SELECT case Field1 when NULL then 'NULL' else 'NOT NULL' end as XX
FROM MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 5, 2013 at 9:00 am
IIF isn't a valid function in SQL 2008 (it is available in 2012 though!). A case statement should accomplish what you need.
SELECT CASE WHEN Field1 IS NULL THEN 'NULL' ELSE 'NOT NULL' END
FROM MyTable
September 5, 2013 at 9:04 am
Guess I'm showing my age. Didn't it used to be supported? back in the SQL 2000 days?
September 5, 2013 at 9:16 am
RedBirdOBX (9/5/2013)
Guess I'm showing my age. Didn't it used to be supported? back in the SQL 2000 days?
Nope. It was in Access, FoxPro, VB but not in sql server until 2012.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 6, 2013 at 1:07 pm
Sean Lange (9/5/2013)
IIF is not valid syntax for SQL.--begin edit--
IIF was added to SQL 2012 :angry:
--end edit--
The first statement would be something like this:
SELECT case Field1 when NULL then 'NULL' else 'NOT NULL' end as XX
FROM MyTable
Sean, that form of the CASE statement tests for equality, which won't work when a NULL value is involved.
SELECT CASE WHEN Field1 IS NULL THEN 'NULL' ELSE 'NOT NULL' END AS XX
FROM MyTable
September 6, 2013 at 1:30 pm
PhilPacha (9/6/2013)
Sean Lange (9/5/2013)
IIF is not valid syntax for SQL.--begin edit--
IIF was added to SQL 2012 :angry:
--end edit--
The first statement would be something like this:
SELECT case Field1 when NULL then 'NULL' else 'NOT NULL' end as XX
FROM MyTable
Sean, that form of the CASE statement tests for equality, which won't work when a NULL value is involved.
SELECT CASE WHEN Field1 IS NULL THEN 'NULL' ELSE 'NOT NULL' END AS XX
FROM MyTable
:blush: Gosh Phil thanks. I certainly know that but in my haste I posted it completely wrong. Thanks for correcting me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply