Converting Iif Statement From Access To Case When

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Iif Statement From Access To Case When

Author

Topic

cwildeman
Starting Member

40 Posts

Posted - 2010-12-30 : 10:20:00

Hi,
I receive a report from a proprietary system that has a field called [Wound #1] that has text values of Yes or No. I would then import this to an Access table and create a query with a field called Wound and assigned a value of 1 if [Wound #1] is Yes and 0 if no. I want to move this to SQL Server and am having trouble converting the IIF statement to Case When Else. Here is my access query. Can someone help? Thanks, Chuck

SELECT HAPU.[Patient Name], HAPU.[Patient MRN], HAPU.[Account Number], HAPU.[Act Discharge Date], HAPU.[Admit Date],
HAPU.[Attend Physician], HAPU.[Encounter Facility], IIf([Wound #1]="Yes",1,0) AS Wound, 1 AS PTCount,
HAPU.[Unit HAPU Occured] AS Unit, HAPU.[Wound Stage] AS Stage, HAPU.Consult, HAPU.CreatedInError, tblDays.TrxMonth AS [Month]
FROM HAPU INNER JOIN tblDays ON HAPU.Consult = tblDays.TrxDay
WHERE (((IIf([Wound #1]="Yes",1,0))>0) AND ((HAPU.CreatedInError) Is Null))

Chuck W

Kristen
Test

22859 Posts

Posted - 2010-12-30 : 10:39:50

Change


IIf([Wound #1]="Yes",1,0) AS Wound,


CASE [Wound #1] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) AS Wound,

(you can use a different value for the ELSE if you like - e.g. NULL)
and


WHERE (((IIf([Wound #1]="Yes",1,0))>0)



WHERE [Wound #1] = 'Yes'

cwildeman
Starting Member

40 Posts

Posted - 2010-12-30 : 11:24:47

Thanks. That worked. Chuck

Chuck W

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-12-31 : 04:46:49

CASE [Wound #1] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) AS Wound,

is equal to

CASE [Wound #1] WHEN 'Yes' THEN 1 ELSE 0 END) AS Wound,

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-01 : 19:57:10

"CASE [Wound #1] WHEN 'Yes' THEN 1 WHEN 'No' THEN 0 ELSE 0 END) AS Wound,

is equal to

CASE [Wound #1] WHEN 'Yes' THEN 1 ELSE 0 END) AS Wound,"

Indeed, I only offered it on the basis that the O/P might want to handle the case where [Wound #1] was NOT either "Yes" or "No" and return an explicit/different value to indicate the error

(The close parenthesis should not have been present in my original ... )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:18:34

Ok. I was thinking that IIf([Wound #1]="Yes",1,0) means what I specified. It wont handle more than two conditions

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 04:35:15

Yes, you are quite right - its just my coding style Madhi

We don't code

IF @XXX = 'Yes' THEN 1 ELSE 0 END

ever!!! because we want the application to "break" at the earliest opportunity when the value in @XXX is neither "Yes" nor "No" so we can fix the underlying problem ... and not have it running wrongly for months and then have to fix the mess than has ensued through the whole database!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-03 : 04:47:01

quote:
Originally posted by Kristen

Yes, you are quite right - its just my coding style Madhi

We don't code

IF @XXX = 'Yes' THEN 1 ELSE 0 END

ever!!! because we want the application to "break" at the earliest opportunity when the value in @XXX is neither "Yes" nor "No" so we can fix the underlying problem ... and not have it running wrongly for months and then have to fix the mess than has ensued through the whole database!!

You are correct

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources