iif to case for sql server 2000

[SPAFA]

I am trying to convert this query to slq server 2000 and I cant figure
out how to get rid of the IIF statements and make them case statements.



If anyone could help I would greatly appreciate it!



Thanks!

spafa





SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMEN TS2,
Jeopardy.STATUS , Jeopardy.DENTAL _STATUS, Jeopardy.HLTH_I NC,
Jeopardy.DNTL_I NC, Jeopardy.COMP_H LTH, Jeopardy.COMP_H LTH_DISC,
Jeopardy.COMP_P LAN_DESIGN, Jeopardy.COMP_D NTL, Jeopardy.COMP_D NTL_DISC,
Jeopardy.OUT_TO _BID, IIf([COMP_HLTH]=\"Mass Blue
Cross\",\"YES\" ,IIf([COMP_HLTH]=\"Out of State Blue
Cross\",\"YES\" ,IIf([COMP_HLTH]=\"CT Blue
Cross\",\"YES\" ,IIf([COMP_HLTH]=\"Empire Blue Cross\",\"YES\" ,\"NO\"))))
AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
BC_PLAN, Jeopardy.LG_RAN KING, Jeopardy.LG_SCO RE, Jeopardy.DATE_N OTIFIED,
Jeopardy.DATE_U PDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
IIf([DateDiff]<8,\"*\",Null ) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null ) AS
[ADD], Jeopardy.Rep_Id , Jeopardy.Rep_Na me, tblIRIP_QA_NAME S.ADMIN_NAME
AS MSS, AccountOwnershi p.ANALYST_NAME, AccountOwnershi p.UND_NAME,
AccountOwnershi p.DNTL_UND_NAME , AccountOwnershi p.SERVICE_REP,
AccountOwnershi p.SIZE, AccountOwnershi p.SIZE2, Jeopardy.CYCLE,
Jeopardy.DENTAL _CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-
ardy]![cycle])) AS CYCLE2, AccountOwnershi p.Canc_Date,
AccountOwnershi p.Dntl_Canc_Dat e, AccountOwnershi p.EFFDATE,
AccountOwnershi p.Dntl_EFFDATE, AccountOwnershi p.TOTALHLTH,
AccountOwnershi p.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
AccountOwnershi p.HEALTHMATE, AccountOwnershi p.CHIP,
AccountOwnershi p.CLASSIC, AccountOwnershi p.BROKER,
AccountOwnershi p.HLTH_BROKER_1 , AccountOwnershi p_DSC.DISPOSITI ON,
AccountOwnershi p_DSC.DISPOSITI ON_MONTH, IIf([DISPOSITION_mon th] Is Not
Null,\"YES\",\" NO\") AS OC, IIf([DISPOSITION_mon th] Is Not
Null,[DISPOSITION_mon th],Null) AS OC_MONTH

FROM ((AccountOwners hip_DSC RIGHT JOIN AccountOwnershi p ON
AccountOwnershi p_DSC.Main = AccountOwnershi p.Main) RIGHT JOIN Jeopardy
ON AccountOwnershi p.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAME S ON
AccountOwnershi p.REP_ID = tblIRIP_QA_NAME S.Rep_Id ORDER BY
Jeopardy.DATE_U PDATED DESC; " );


--
Posted via http://dbforums.com

[Dan Guzman]

Re: iif to case for sql server 2000

Below are 2 examples taken directly from the SQL 2000 Books Online
<"tsqlref.chm ::/ts_ca-co_5t9v.htm">. The first example uses the simple
CASE form where the specified expression is compared with each entry in
the list. The second example uses the searched CASE form where each
entry in the list specifies a Boolean condition.

USE pubs
GO

SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

SELECT 'Price Category' =
CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END,
CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800

http://www.sqlserverfaq.com

http://www.mssqlserver.com/faq

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

"SPAFA" <member44362@db forums.com> wrote in message
news:3487057.10 66269043@dbforu ms.com...[color=blue]
>
> I am trying to convert this query to slq server 2000 and I cant figure
> out how to get rid of the IIF statements and make them case[/color]
statements.[color=blue]
>
>
>
> If anyone could help I would greatly appreciate it!
>
>
>
> Thanks!
>
> spafa
>
>
>
>
>
> SELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMEN TS2,
> Jeopardy.STATUS , Jeopardy.DENTAL _STATUS, Jeopardy.HLTH_I NC,
> Jeopardy.DNTL_I NC, Jeopardy.COMP_H LTH, Jeopardy.COMP_H LTH_DISC,
> Jeopardy.COMP_P LAN_DESIGN, Jeopardy.COMP_D NTL,[/color]
Jeopardy.COMP_D NTL_DISC,[color=blue]
> Jeopardy.OUT_TO _BID, IIf([COMP_HLTH]=\"Mass Blue
> Cross\",\"YES\" ,IIf([COMP_HLTH]=\"Out of State Blue
> Cross\",\"YES\" ,IIf([COMP_HLTH]=\"CT Blue
> Cross\",\"YES\" ,IIf([COMP_HLTH]=\"Empire Blue[/color]
Cross\",\"YES\" ,\"NO\"))))[color=blue]
> AS OTHER_BC_PLAN, IIf([other_bc_plan]=\"yes\",[COMP_HLTH],\"\") AS
> BC_PLAN, Jeopardy.LG_RAN KING, Jeopardy.LG_SCO RE,[/color]
Jeopardy.DATE_N OTIFIED,[color=blue]
> Jeopardy.DATE_U PDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,
> Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,
> IIf([DateDiff]<8,\"*\",Null ) AS CHANGE, IIf([DateDiff2]<8,\"+\",Null )[/color]
AS[color=blue]
> [ADD], Jeopardy.Rep_Id , Jeopardy.Rep_Na me, tblIRIP_QA_NAME S.ADMIN_NAME
> AS MSS, AccountOwnershi p.ANALYST_NAME, AccountOwnershi p.UND_NAME,
> AccountOwnershi p.DNTL_UND_NAME , AccountOwnershi p.SERVICE_REP,
> AccountOwnershi p.SIZE, AccountOwnershi p.SIZE2, Jeopardy.CYCLE,
> Jeopardy.DENTAL _CYCLE, IIf([Jeopardy]![cycle] Is[/color]
Null,[Jeopardy]![DENTA-[color=blue]
>[/color]
L_CYCLE],IIf([Jeopardy]![cycle]=\"N/A\",[Jeopardy]![DENTAL_CYCLE],[Jeop-[color=blue]
> ardy]![cycle])) AS CYCLE2, AccountOwnershi p.Canc_Date,
> AccountOwnershi p.Dntl_Canc_Dat e, AccountOwnershi p.EFFDATE,
> AccountOwnershi p.Dntl_EFFDATE, AccountOwnershi p.TOTALHLTH,
> AccountOwnershi p.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
> AccountOwnershi p.HEALTHMATE, AccountOwnershi p.CHIP,
> AccountOwnershi p.CLASSIC, AccountOwnershi p.BROKER,
> AccountOwnershi p.HLTH_BROKER_1 , AccountOwnershi p_DSC.DISPOSITI ON,
> AccountOwnershi p_DSC.DISPOSITI ON_MONTH, IIf([DISPOSITION_mon th] Is Not
> Null,\"YES\",\" NO\") AS OC, IIf([DISPOSITION_mon th] Is Not
> Null,[DISPOSITION_mon th],Null) AS OC_MONTH
>
> FROM ((AccountOwners hip_DSC RIGHT JOIN AccountOwnershi p ON
> AccountOwnershi p_DSC.Main = AccountOwnershi p.Main) RIGHT JOIN Jeopardy
> ON AccountOwnershi p.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAME S[/color]
ON[color=blue]
> AccountOwnershi p.REP_ID = tblIRIP_QA_NAME S.Rep_Id ORDER BY
> Jeopardy.DATE_U PDATED DESC; " );
>
>
> --
> Posted via http://dbforums.com[/color]

[David Portas]

Re: iif to case for sql server 2000

In addition to Dan's post, note that there may sometimes be neater
alternatives to CASE:

.... COALESCE(NULLIF (Jeopardy.cycle ,'N/A'),Jeopardy.de ntal_cycle) AS cycle2,
....

--
David Portas
------------
Please reply only to the newsgroup
--