iif to case for sql server 2000

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.COMMENTS2,
Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_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_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,
Jeopardy.DATE_UPDATED, 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_Name, tblIRIP_QA_NAMES.ADMIN_NAME
AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
AccountOwnership.SIZE, AccountOwnership.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, AccountOwnership.Canc_Date,
AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
AccountOwnership.CLASSIC, AccountOwnership.BROKER,
AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
Null,[DISPOSITION_month],Null) AS OC_MONTH

FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ON
AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
Jeopardy.DATE_UPDATED DESC; " );

--
Posted via http://dbforums.comBelow 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...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"SPAFA" <member44362@.dbforums.com> wrote in message
news:3487057.1066269043@.dbforums.com...
> 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.COMMENTS2,
> Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,
> Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,
> Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL,
Jeopardy.COMP_DNTL_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_RANKING, Jeopardy.LG_SCORE,
Jeopardy.DATE_NOTIFIED,
> Jeopardy.DATE_UPDATED, 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_Name, tblIRIP_QA_NAMES.ADMIN_NAME
> AS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,
> AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,
> AccountOwnership.SIZE, AccountOwnership.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, AccountOwnership.Canc_Date,
> AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,
> AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,
> AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,
> AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,
> AccountOwnership.CLASSIC, AccountOwnership.BROKER,
> AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,
> AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is Not
> Null,\"YES\",\"NO\") AS OC, IIf([DISPOSITION_month] Is Not
> Null,[DISPOSITION_month],Null) AS OC_MONTH
> FROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ON
> AccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN Jeopardy
> ON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES
ON
> AccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BY
> Jeopardy.DATE_UPDATED DESC; " );
>
> --
> Posted via http://dbforums.com|||In addition to Dan's post, note that there may sometimes be neater
alternatives to CASE:

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

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