CASE statement - 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

General SQL Server Forums

New to SQL Server Programming

CASE statement

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-24 : 07:38:04

I am trying to write a script where certain calculation is done for one kind of policy (let us say policy A)and a different calculation is done for another type (let us say policy B). The thing is that some of the calculation for Policy A are also done for Policy B. here is a sample of the script;

The first portion is for both Policy A and Policy B

PART_TIME_DISC AS PART_TIME_DISC_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(COALESCE(PART_TIME_DISC,0)/100.0),0)) AS PART_TIME_DISC_AMOUNT,

NEW_PRAC_DISC AS NEW_PRAC_DISC_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(COALESCE(NEW_PRAC_DISC,0)/100.0),0)) AS NEW_PRAC_DISC_AMOUNT,

here is the part that is done only for Policy B

----POLICY B CALCULATION

CASE WHEN M.POLICY_NUMBER LIKE 'B1%' OR M.POLICY_NUMBER LIKE 'B2%' THEN

CLAIMS_MADE_DEBIT AS CLAIMS_MADE_DEBIT_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(COALSCE CLAIMS_MADE_DEBIT,0)/100.0),0)) AS CLAIMS_MADE_DEBIT_AMOUNT,

TAIL_NOT_BOUGHT AS TAIL_NOT_BOUGHT_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(1.0+COALESCE(CLAIMS_MADE_DEBIT,0)/100.0)
*(COALESCE TAIL_NOT_BOUGHT,0)/100.0),0)) AS TAIL_NOT_BOUGHT_AMOUNT,

When I try to compile the script I get the following error message..

"Incorrect syntax near the keyword 'AS'" and it show that it is pointing to the AS in the Cases statement..

What am I doing wrong???

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 07:44:54

Missing brackets, incorrectly spelled COALESCE, missing END key word etc. in the policy B calculation. See in red - I was only trying to correct the syntax errors; verify whether the logic is correct based on your business requirements:

CASE WHEN M.POLICY_NUMBER LIKE 'B1%' OR M.POLICY_NUMBER LIKE 'B2%' THEN 

CLAIMS_MADE_DEBIT END AS CLAIMS_MADE_DEBIT_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0) 
*(1.0+COALESCE(DISCOUNT_1,0)/100.0) 
*(1.0+COALESCE(DISCOUNT_3,0)/100.0) 
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0) 
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0) 
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(COALESCE (CLAIMS_MADE_DEBIT,0)/100.0),0)) AS CLAIMS_MADE_DEBIT_AMOUNT,

TAIL_NOT_BOUGHT AS TAIL_NOT_BOUGHT_PERCENT,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0) 
*(1.0+COALESCE(DISCOUNT_1,0)/100.0) 
*(1.0+COALESCE(DISCOUNT_3,0)/100.0) 
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0) 
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0) 
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(1.0+COALESCE(CLAIMS_MADE_DEBIT,0)/100.0)
*(COALESCE(TAIL_NOT_BOUGHT,0)/100.0),0)) AS TAIL_NOT_BOUGHT_AMOUNT

divan
Posting Yak Master

153 Posts

Posted - 2012-09-24 : 08:04:33

Suniabeck thank you for correcting my spelling error and the bracket issue.. I have corrected them as mentioned above now I do not get that error any more but then I get

Incorrect syntax near the keyword 'ELSE'.

and it is pointing to
REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
*(1.0+COALESCE(CLAIMS_MADE_DEBIT,0)/100.0)
*(1.0+COALESCE(TAIL_NOT_BOUGHT,0)/100.0)
*(1.0+COALESCE(GRIEVANCE,0)/100.0)
*(1.0+COALESCE(FELONY_MISDEMEANOR,0)/100.0)
*(1.0+COALESCE(SICK,0)/100.0)
*(1.0+COALESCE(REVIEW,0)/100.0)
*(1.0+COALESCE(NEW_PRACTICE,0)/100.0)
*(1.0+COALESCE(PART_TIME,0)/100.0)
*(1.0+COALESCE(ABUSE,0)/100.0)
*(1.0+COALESCE(LICENSE,0)/100.0)
*(1.0+COALESCE(MISCONDUCT,0)/100.0)
*(1.0+COALESCE(RELAPSE,0)/100.0)
*(1.0+COALESCE(NO_COVERAGE,0)/100.0)
*(1.0+COALESCE(FDA_APPROVED,0)/100.0)
*(1.0+COALESCE(EXPIRING_COVERAGE,0)/100.0)
*(1.0+COALESCE(MP_PHYS_PROG,0)/100.0)
*(1.0+COALESCE(NOT_RENEWED,0)/100.0)
*(1.0+COALESCE(OTHER_DEBIT,0)/100.0)
*(1.0+COALESCE(OTHER_CREDIT,0)/100.0)
*(1.0+COALESCE(LOSS_FREQUENCY,0)/100.0)
*(1.0+COALESCE(LONGEVITY_CREDIT,0)/100.0)
*(1.0+COALESCE(VIC_LIAB_DEBIT,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_1,0)/100.0)
*(1.0+COALESCE(MISC_DEBIT_2,0)/100.0)
*(COALESCE (REG_PROC_PREM,0)/100.0),0)) AS EXPR_PREM,

ELSE
REG_PROC_PREM AS REG_PROC_PREM,
CONVERT (DECIMAL (10,0),(COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(1.0+COALESCE(DISCOUNT_2,0)/100.0)
*(1.0+COALESCE(EXPER_DISC,0)/100.0)
*(1.0+COALESCE(RISK_MGMT,0)/100.0)
*(1.0+COALESCE(DISCOUNT_1,0)/100.0)
*(1.0+COALESCE(DISCOUNT_3,0)/100.0)
*(1.0+COALESCE(MANUAL_BLENDING,0)/100.0)
*(1.0+COALESCE(PART_TIME_DISC,0)/100.0)
*(1.0+COALESCE(NEW_PRAC_DISC,0)/100.0)
+ COALESCE(REG_PROC_PREM,0)) AS EXPR_PREM
END
which I thought that we need to always have a ELSE and an END in a CASE statement.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-24 : 08:09:39

You have an ELSE clause without a beginning CASE and WHEN parts. The CASE expression has only two acceptable formats - see here: http://msdn.microsoft.com/en-us/library/ms181765.aspx

Your query needs to conform to one of those two formats.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-24 : 08:10:38

Please read this:

http://msdn.microsoft.com/de-de/library/ms181765.aspx

Evaluates a list of conditions and returns one of multiple possible result expressions

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources