Error in the 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

Error in the Case statement

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-09-10 : 09:04:30

I am trying to write a script that will display certain calculated fields. And then within the script I also need to determine the policy type and if it meets certain criteria then it need to do additional calculations..

The following is a portion of the script and when I run the script I get the following error "Incorrect syntax near the keyword 'AS'" this is referring to the AS after this script line
CASE WHEN M.POLICY_NUMBER LIKE 'MS1%' OR M.POLICY_NUMBER LIKE 'MS2%' THEN

----------------------------------------------------------
SELECT
CASE WHEN T.NOVA_DESCRIPTION IS NULL THEN ' ' ELSE T.NOVA_DESCRIPTION END AS PHYSICIAN_NOVA_DESCRIPTION,

COALESCE(BASE_PREM,0) AS BASE_PREMIUM,
COALESCE(PHYS_TMB_PREM,0) AS TMB_PREMIUM,
COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0) AS TOTAL_BASE_PREM,
COALESCE(ENT_PREM_PCT,0) AS ENTITY_PREMIUM_PERCENTAGE,

GROUP_DISC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(COALESCE(GROUP_DISC,0)/100.0),0)) AS GROUP_DISC_AMOUNT,

DISCOUNT_2 AS MARKET_DISC_PERC,
CONVERT (DECIMAL (10,0),ROUND((COALESCE(BASE_PREM,0)+COALESCE(PHYS_TMB_PREM,0))
*(1.0+COALESCE(GROUP_DISC,0)/100.0)
*(COALESCE(DISCOUNT_2,0)/100.0),0)) AS MARKET_DISCOUNT_AMOUNT,

EXPER_DISC AS EXPER_DISC_PERC,
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)
*(COALESCE(EXPER_DISC,0)/100.0),0)) AS EXPER_DISC_AMOUNT,

CASE WHEN M.POLICY_NUMBER LIKE 'MS1%' OR M.POLICY_NUMBER LIKE 'MS2%' 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,

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-10 : 09:28:09

END is missing there.

btw. in that case expression you have no else? this means the result for this column is NULL in some cases...

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-10 : 09:28:32

It looks like you're just mising an END

CASE WHEN M.POLICY_NUMBER LIKE 'MS1%' OR M.POLICY_NUMBER LIKE 'MS2%' THEN

CLAIMS_MADE_DEBIT END AS CLAIMS_MADE_DEBIT_PERCENT

Jim

Everyday I learn something that somebody else already knew

divan
Posting Yak Master

153 Posts

Posted - 2012-09-10 : 09:42:49

so does this mean I have to have a CASE and and END for each of the calculated fields after CASE WHEN M.POLICY_NUMBER LIKE 'MS1%' OR M.POLICY_NUMBER LIKE 'MS2%' THEN ..

Also jimf I have tried to use some of the options like hightlighting or making certain workds in RED and have not been able to do so. Is there a trick to it or is there something wrong with my desktop??

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-09-10 : 09:51:22

END is the sign that its end of CASE statement. i.e. (CASE...END) CASE condition THEN value ELSE value END
A case can be nested i.e. CASE with in a CASE, but rule will remain same, every CASE must end with END word.
CASE condition THEN (CASE condition THEN value ELSE value END) ELSE value END

--------------------------
http://connectsql.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-10 : 11:54:10

[code]
so does this mean I have to have a CASE and and END for each of the calculated fields after CASE WHEN M.POLICY_NUMBER LIKE 'MS1%' OR M.POLICY_NUMBER LIKE 'MS2%' THEN ..

Also jimf I have tried to use some of the options like hightlighting or making certain workds in RED and have not been able to do so. Is there a trick to it or is there something wrong with my desktop??
[/code]

Each CASE statement must have an END
<CASE WHEN <something> THEN <something> ELSE <somethingElse> END

If you "Reply To Topic", instead of just typing in the window, a new window opens and there is where you can change colors or ~~othr~~ other things.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources