Converting Access Queries to SQL Views (IIf(x,y,z))

[kwvh]

I have been tasked with moving several queries from Access to SQL Views, which will ultimately become reports in SSRS. There is this convenient Access query that allows using "IIF()" as a field/column in a query (e.g. IIf([Description]="Poison",True,False) AS Poison).

Is there an equivalent in SQL Server or a workaround?

Thanks in advance.

Ken

[HansV]

In this particular example, you could use

Poison = ([Description]="Poison")

or

([Description]="Poison") AS Poison

since the expression already evaluates to True or False. In general, you can use CASE WHEN:

SELECT ..., Poison = CASE WHEN [Description]="Poison" THEN True ELSE False END, ...

[kwvh]

Hans,

Thanks. I posted the simplest of the group thinking I could apply that to the more complex ones. However, I don't see an obvious way of doing it. So below is another column from the query that is a bit more complex:

Code: Select all

IIf(IIf([CDL]>180,180,[CDL])+[RDCD]>180,180-IIf([CDL]>180,180,[CDL]),[RDCD]) AS RCapped

Again I don't yet grasp how to convert the IIF above into some SQL equivalent without creating some interim table and updating individual fields then using the temp table as the source for the report. It is probably due to my ignorance of SQL Server/TSQL.

Thank you for your consideration.

Ken

[HansV]

I'm not an expert in TSQL either but I think it'd be

RCapped = CASE WHEN CASE WHEN [CDL]>180 THEN 180 ELSE [CDL] END + [RDCD] >180 THEN 180 - CASE WHEN [CDL]>180 THEN 180 ELSE [CDL] END ELSE [RDCD] END

[kwvh]

Thanks, Hans. I'll post here the ultimate dispostion/solution.