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

Answer 1

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;