CODEWITHSUNDEEP
sql-serverms-access
mikybrain
mikybrain

Reputation: 169

How to convert or rewrite Access IIF Statement in SQL Server

I thought the IIf statements returns one value if the condtion is true or false BUT This IIf statement in Access returns the field and it values. 

IIf([A1]![KPr],[A1]![Kat],IIf([Data2]![Kat],[Data2]![Kat],[Data1]![Kat])),

the table left join in the from clause I'm try to realize this statement in SQL Server using CASE WHEN but it also accepts a true or false condition.

How can I understand and realize this statement.

Upvotes: 0

Views: 799

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82504

IIf function in VB, VBA, and Access is the same as ps_prakash02 wrote in the comment: iif(condition, value_if_true, value_if_false). this means that if the condition evaluates to true, the value_if_true is returned, otherwise value_if_false returns.
So a translation of IIf to t-sql is simply CASE WHEN condition THEN value_if_true ELSE _value_if_false END.

I'm not so sure what [A1]![KPr] means in access, I'm guessing it's KPr column value of table A1 or something like this, so I'll leave them as they are in your question and just replace the IIF with CASE in my answer:

CASE WHEN [A1]![KPr] THEN [A1]![Kat]
     ELSE 
         CASE WHEN [Data2]![Kat] THEN [Data2]![Kat] 
              ELSE [Data1]![Kat]
         END
 END

Note: In SQL Server 2012 Microsoft included IIF in t-sql.

Upvotes: 1

Related Questions