Trying to build a case statement

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

Transact-SQL (2000)

Trying to build a case statement

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-03-17 : 13:31:38

Hi I am converting an Ms Access 2003 staement to TSQL adn am not sure how to convert this this one part.
Below is a prothion of the Access SQL, the part that I am not sure is the IIF statements I can do this if it si just one but I ma not sure how to handle the nested part
*****IIf([Category]='Medi',IIf([Grp_Prod_S_Date].[MinOfCon_Date]=[Group_Medical_Dental_Start_Date].[MinOfCon_Date],712,719),IIf([Category]='Dent',IIf([Group_Product_Start_Date].[MinOfCon_Date]=[Grp_Prod_S_Date].[MinOfCon_Date],713,720))) AS Col_prd_id,******

SELECT [SPprocess_prod_recs].group AS Expr1, [SPprocess_prod_recs].SP_Product AS Expr2, [SPprocess_prod_recs].Category AS Expr3, IIf([Category]='Medi',IIf([Grp_Prod_S_Date].[MinOfCon_Date]=[Group_Medical_Dental_Start_Date].[MinOfCon_Date],712,719),IIf([Category]='Dent',IIf([Group_Product_Start_Date].[MinOfCon_Date]=[Grp_Prod_S_Date].[MinOfCon_Date],713,720))) AS Col_prd_id,

My attempt at this is:
(case when Category ='Medi'
(case when [Grp_Prod_S_Date].[MinOfCon_Date]=[Group_Medical_Dental_Start_Date].[MinOfCon_Date] Then 715 Else 713),
(case when [Category]='Dent'
(case when [Group_Product_Start_Date].[MinOfCon_Date]=[Grp_Prod_S_Date].[MinOfCon_Date] Then 723 Else 727))) AS Col_prd_id,******

Any idease will be appreciated
Thank you

ITM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:38:11

case when Category ='Medi'
then case when [Grp_Prod_S_Date].[MinOfCon_Date]=[Group_Medical_Dental_Start_Date].[MinOfCon_Date] Then 715 Else 713 end
when [Category]='Dent'
then case when [Group_Product_Start_Date].[MinOfCon_Date]=[Grp_Prod_S_Date].[MinOfCon_Date] Then 723 Else 727 end
end AS Col_prd_id

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2009-03-17 : 14:00:03

Thanks for the reply, it now has one problem left it says tat ther is a syntext error near "AS Col_prd_id"
any idea
Thnaks

ITM

cjonline
Yak Posting Veteran

55 Posts

Posted - 2009-03-24 : 07:19:38

I might be wrong , but I think the THEN and WHEN are mixed up. ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources