IS NULL Syntax

[tfhwargt3]

I am transferring queries from MS Access to my new SQL server and I am getting this error on what seems to be one of the more simple comparisons within my query.

"...

 [COLOR=red]IIf((Addresses.Company IS NULL OR Addresses.Company="")[/color red], Addresses.FirstName & Trim(IIf(Addresses.Initial IS NULL, "", " " & Addresses.Initial)) & " " & Addresses.LastName, Addresses.Company) AS CompanyOrName,

..."

The error is:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'IS'.

The error refers the the red portion above.

This query ran without errors in access, but it seems the IS NULL comparison is causing problems in SQL Server. Thanks for any help.

 

[gmmastros]

Your problem is not the IS NULL comparison.

SQL Server does not have IIF. You need to use Case/When instead.

Ex:

Case When Addresses.Company Is NULL OR Addresses.Company = ''
     Then Addresses.FirstName
     Else Addresses.Company
     End As CompanyOrName

You can also embed a case/when inside another case/when.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

you can also do

COALESCE(NULLIF(Addresses.Company,''),Addresses.FirstName) As CompanyOrName
from Addresses

example

create table Addresses(Company varchar(66),FirstName varchar(55))
insert Addresses values(null,'aaaaaaa')
insert Addresses values('22222',null)
insert Addresses values('33333','ccccccc')
insert Addresses values('44444','ddddd')

select  COALESCE(NULLIF(Addresses.Company,''),Addresses.FirstName) As CompanyOrName
from Addresses

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

didn't add the blank rows to the table run this

insert Addresses values('','eeeeee')
insert Addresses values('66666','')

then run query again

select  COALESCE(NULLIF(Addresses.Company,''),Addresses.FirstName) As CompanyOrName
from Addresses

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions