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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 First Query - Converting IIF(IsNull to SQL

Author  Topic 

Kittyness
Starting Member

8 Posts

Posted - 2013-07-02 : 05:55:13
Hi,

First time user of SQL thrown right in the deep end so I humbly beg your patience :)

I've been working on Access to build my queries and then reverse engineer them to see how the SQL is developed. However, I've just run up against my first incompatable statements.

I'm creating a query to return all the missing/null fields for clients in our database, and I would like a count of how many fields are missing. The below query works in Access:


SELECT
Contacts.Name,
IIf(IsNull([Contacts].[Address1]),1,0)+IIf(IsNull([Contacts].[Address2]),1,0)+IIf(IsNull([Contacts].[Town]),1,0)+IIf(IsNull([Contacts].[County]),1,0)+IIf(IsNull([Contacts].[PostCode]),1,0)+IIf(IsNull([Contacts].[Code]),1,0)+IIf(IsNull([Contacts].[Text_1]),1,0)+IIf(IsNull([Contacts].[Text_2]),1,0) AS [Missing RED Fields],
Contacts.Address1,
Contacts.Address2,
Contacts.Town,
Contacts.County,
Contacts.Country,
Contacts.PostCode,
Contacts.Code,
Contacts.Text_1 AS [Client Group],
Contacts.Text_2 AS [Web Login]
FROM Contacts
WHERE (((Contacts.Address1) Is Null)) OR (((Contacts.Address2) Is Null)) OR (((Contacts.Town) Is Null)) OR (((Contacts.County) Is Null)) OR (((Contacts.Country) Is Null)) OR (((Contacts.PostCode) Is Null)) OR (((Contacts.Code) Is Null)) OR (((Contacts.Text_1) Is Null)) OR (((Contacts.Text_2) Is Null));


I've tried various ways of converting the IIFs to Case When statements, however all the research I've done has been on far more complicated queries that has confused me silly !!!

Some help with a solution, or guidence of a good (easy) place to find he answer would be gratefully received.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 06:01:04
in T-SQL
IIF is available only from version 2012 onwards otherwise you need to use CASE WHEN as below

SELECT
Contacts.Name,
CASE WHEN [Contacts].[Address1] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Address2] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Town] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[County] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Country] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[PostCode] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Code] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Text_1] IS NULL THEN 1 ELSE 0 END +
CASE WHEN [Contacts].[Text_2] IS NULL THEN 1 ELSE 0 END AS [Missing RED Fields],
Contacts.Address1,
Contacts.Address2,
Contacts.Town,
Contacts.County,
Contacts.Country,
Contacts.PostCode,
Contacts.Code,
Contacts.Text_1 AS [Client Group],
Contacts.Text_2 AS [Web Login]
FROM Contacts
WHERE Contacts.Address1) Is Null
OR Contacts.Address2) Is Null
OR Contacts.Town) Is Null
OR Contacts.County Is Null
OR Contacts.Country Is Null
OR Contacts.PostCode Is Null
OR Contacts.Code Is Null
OR Contacts.Text_1 Is Null
OR Contacts.Text_2 Is Null





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Kittyness
Starting Member

8 Posts

Posted - 2013-07-02 : 06:26:55
Thank you so much for your fast reply. That makes a lot if sense to me, what a relief !!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 06:42:22
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

chbala85
Starting Member

49 Posts

Posted - 2013-07-02 : 07:06:37
These function will work on 2012 onwords.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 07:08:38
quote:
Originally posted by chbala85

These function will work on 2012 onwords.



Please stop spamming like this with duplicate statements

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -