IIF and SQL

[JefB]

Is there an SQL equivalent to the Access "IIF()" function?

I find myself having to use Access2K queries linked to my SQL7 data when I would rather do it all in one place!

JefB

 

[cmmrfrds]

You can do conditional processing with the case statement. Here is an example of a cross tab query I found awhile back.

Select Site,
sum(Case When [Month] = 1 Then AvgProd Else 0 End) As M1,
sum(Case When [Month] = 2 Then AvgProd Else 0 End) As M2,
sum(Case When [Month] = 3 Then AvgProd Else 0 End) As M3,
sum(Case When [Month] = 4 Then AvgProd Else 0 End) As M4,
sum(Case When [Month] = 5 Then AvgProd Else 0 End) As M5,
sum(Case When [Month] = 6 Then AvgProd Else 0 End) As M6,
sum(Case When [Month] = 7 Then AvgProd Else 0 End) As M7,
sum(Case When [Month] = 8 Then AvgProd Else 0 End) As M8,
sum(Case When [Month] = 9 Then AvgProd Else 0 End) As M9,
sum(Case When [Month] = 10 Then AvgProd Else 0 End) As M10,
sum(Case When [Month] = 11 Then AvgProd Else 0 End) As M11,
sum(Case When [Month] = 12 Then AvgProd Else 0 End) As M12
From (
Select site, mnth, avg(productivity) AvgProd
from TableName Where Site In ('City', 'Country')
Group By site, Mnth) As qry
Group By Site
Order By Site