Iff condition Access to SQL syntax

Question

I have an access query that I have recreate in SQL.

Access:

   SELECT Columns , 

IIf([Col1]="X",IIf([COL2]<>"XXXX",1,0)) AS NEWCOL1, 

IIf([COL2] Not In ("HHH","GGG"),1,0) AS [NEWCOL2], 

IIf(([NEWCOL1]=1) Or ([NEWCOL2]=1),1,0) AS NEWCOL3 

FROM [TABLE]

WHERE ((([TABLE].COL2)<>"XXXX")) OR ((([TABLE].COL2)<>"HHH" And ([TABLE].COL2)<>"GGG"));

In SQL :

SELECT Columns , 

"NEWCOL1" =
CASE WHEN ([COL1]='X' AND COL2<> 'XXXX') THEN 1 
ELSE 0
END,

"NEWCOL2" =

CASE WHEN COL2 NOT IN ('HHH','GGG') THEN 1
ELSE 0
END ,

IIf(([NEWCOL1]=1) Or ([NEWCOL2]=1),1,0) AS NEWCOL3 


FROM [TABLE]

WHERE ((([TABLE].COL2)<>'XXXX')) OR ((([TABLE].COL2)<>'HHH' And ([TABLE].COL2)<>'GGG'));

IIf(([NEWCOL1]=1) Or ([NEWCOL2]=1),1,0) AS NEWCOL3

When I use the Newcol1 and newcol2 it throws an error invalid column how could use them maybe in a nested case or iif statement

Which version of SQL Server are you using? `iif`is supported in 2012 and later. — jpw, Nov 12 '14 at 13:45
Not exactly a duplicate, but your answer is here: http://stackoverflow.com/questions/22839254/sql-server-iif-vs-case — Tab Alleman, Nov 12 '14 at 13:48
IIf(([NEWCOL1]=1) Or ([NEWCOL2]=1),1,0) AS NEWCOL3 WHERE NEWCOL1 AND NEWCOL2 , it throws an error as invalid column. How could I use it in the IIf condition — WiredTheories, Nov 12 '14 at 14:35
The invalid column is the one which is formed from the first case statement and is being reused again in the last case statement. I think it should be clear now what I'm trying to achieve — WiredTheories, Nov 12 '14 at 14:46
Two options: 1, if you don't need NewCol1 or NewCol2 in your final output, you can nest the two CASE statements and only get NewCol3. Or if you need all three columns, you can generate NewCol1&2 in a subselect or cte, and get NewCol3 in the outer query. — Tab Alleman, Nov 12 '14 at 14:47
I need the NewCOl1 , NewCol2 in my final o/p. I have very little knowledge regarding CTE. Can you let me know what would be the other option I can approach — WiredTheories, Nov 12 '14 at 14:50
@TabAlleman Thanks for pointing me in the direction of subselect. I have accomplished my goal with it :). In the below code line. IIf([Col1]="X",IIf([COL2]<>"XXXX",1,0)) AS NEWCOL1 Should it be "NEWCOL1" = CASE WHEN ([COL1]='X' AND COL2<> 'XXXX') THEN 1 ELSE 0 END, "NEWCOL1" = CASE WHEN ([COL1]='X' OR COL2<> 'XXXX') THEN 1 ELSE 0 END, — WiredTheories, Nov 12 '14 at 14:58

Tab Alleman · Accepted Answer · 2014-11-12T15:34:28.710

2

If you're in SQL 2012, where IIF() is valid, then it looks like the problem is that you don't have an ELSE value for the first outer case:

IIf([Col1]='X',IIf([COL2]<>'XXXX',1,0){,NEED SOME ELSE VALUE HERE}) AS NEWCOL1,

I don't know why this would work in Access. I guess Access must be more "dummy proof" than SQL Server.

To replace your original first IIF with a CASE, you would do this:

CASE WHEN [Col1]='X' THEN 
  CASE WHEN [COL2]<>'XXXX' THEN 1 ELSE 0 END
END

By not supplying an ELSE for the first condition, if [Col1] does not equal 'X', the statement will return NULL without raising an error.

To handle your most recent request with a CTE, you could do as below:

WITH cte AS (
  SELECT 
    Columns,
    CASE WHEN ([COL1]='X' AND COL2<> 'XXXX') THEN 1 ELSE 0 END AS NEWCOL1,
    CASE WHEN COL2 NOT IN ('HHH','GGG') THEN 1 ELSE 0 END AS NEWCOL2
  FROM [TABLE]
  WHERE ((([TABLE].COL2)<>'XXXX')) OR ((([TABLE].COL2)<>'HHH' And ([TABLE].COL2)<>'GGG'))
)
SELECT *, IIf(([NEWCOL1]=1) Or ([NEWCOL2]=1),1,0) AS NEWCOL3 
FROM cte;

edited Nov 12 '14 at 15:34

answered Nov 12 '14 at 13:50

Tab Alleman

31,483
7
36
52

I created the iif as Case , the case statement creates 2 columns whose columns needs to be checked again. Newcol1 and Newcol2. So how do use the columns in next case since these are columns created in the code. – WiredTheories Nov 12 '14 at 14:26
CASE WHEN [Col1]='X' THEN CASE WHEN [COL2]<>'XXXX' THEN 1 ELSE 0 END END , This statement releases in null values for that particular column – WiredTheories Nov 12 '14 at 15:11
Yes, that's what I said in my last paragraph. To avoid NULLs, supply an "ELSE" for the outer CASE statement – Tab Alleman Nov 12 '14 at 15:27
Yes I put the else in the outer case statement. But Can't I AND the condition or OR it to get the same result – WiredTheories Nov 12 '14 at 15:30
Possibly, depending on what the value of the ELSE should be. – Tab Alleman Nov 12 '14 at 15:31
Thanks for enlightening me on CTE. I think i will be using more of it. It is wonderful approach and alternative to subselect. I have a lot of check queries to be recreated in SQL – WiredTheories Nov 12 '14 at 15:43

Iff condition Access to SQL syntax

1 Answers1