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
Converting Access Queries to SQL Views (IIf(x,y,z))
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting Access Queries to SQL Views (IIf(x,y,z))
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, ...
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, ...
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
Re: Converting Access Queries to SQL Views (IIf(x,y,z))
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:
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
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
Thank you for your consideration.
Ken
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Converting Access Queries to SQL Views (IIf(x,y,z))
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
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
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 308
- Joined: 24 Feb 2010, 13:41
Re: Converting Access Queries to SQL Views (IIf(x,y,z))
Thanks, Hans. I'll post here the ultimate dispostion/solution.