case - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

case

Author

Topic

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-26 : 09:57:31

Hi,

The query below is in Access and now I need to do it in sql. I know IIf function does not support in SQL, there for I use Case in SQL but I am not sure that I have the right syntax or not. Please look at my case statement and tell me what did I have wrong.

Access:
IIf([OfficeType]="50",[OHA].[OHARO], IIf([OfficeType]="11",[AREA],[ROCD],
IIf(NULL([COLOCOFC].[OWNR_OCD]="",[OTHER].[DCORO],[COLOCOFC].[OWNR_OCD])))
as ReportsToOfficeCode

SQL:
SELECT
Case OfficeType
WHEN 50 THEN OHA.OHARO
WHEN 11 THEN AREA.ROCD
ELSE(ISNULL(COLOCOFC.OWNR_OCD = '',[OTHER].[DCORO],[COLOCOFC].[OWNR_OCD])))
End as ReportsToOfficeCode

Thanks alot

Jay99

468 Posts

Posted - 2002-03-26 : 10:10:40

You need to take a look at ISNULL in BOL . . . a comparison will return {TRUE,FALSE} but not NULL . . .

Jay
<O>

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-26 : 10:36:14

Thanks for your respond, however I don't understand what you mean?

Thanks

vicki
Posting Yak Master

117 Posts

Posted - 2002-03-26 : 10:36:15

Thanks for your respond, however I don't understand what you mean?

Thanks

Nazim
A custom title

1408 Posts

Posted - 2002-03-26 : 10:46:09

Try somethign on these linds.

SELECT
Case OfficeType
WHEN 50 THEN OHA.OHARO
WHEN 11 THEN AREA.ROCD
ELSE ISNULL(COLOCOFC.OWNR_OCD,[OTHER].[DCORO])

the way you are using isnull isnt right. Check in BOL for Isnull.

--------------------------------------------------------------

Jay99

468 Posts

Posted - 2002-03-26 : 11:15:41

Yeah, what Nazim said . . .

you see, this...

COLOCOFC.OWNR_OCD = ''

...will always evaluate to either being TRUE or FALSE. So your ISNULL System Function, not only has too many arguments, but the check_expression argument is not an expression, it is a comparison. Additonally, the check_expression and the replacement_value must be of the same type, and a comparison has no type.

Jay
<O>

robvolk - Sorry Jay, had to edit this, it wasn't displaying properly.

Edited by - robvolk on 03/26/2002 11:19:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources