Case statment failure

[Tupacmoche]

Hi All,

I have the weirdest error on a CASE statement but not really the CASE statement. I have added an additional condition to a CASE statement and then aliased column don't work. I could use a second pair of eyes The lines number where the issue is starts at 72 and ends at 80. The CASE statement on line 80 works fine but the lines starting at line 72 fail. Here is the code:

Works:

casewhenCAST(l.PREMIUM ASDECIMAL(12,2))<1 then' 'else'N'end PREMIUM_DECLINED_IND,

Does not work:
CASE
WHEN (AP.APPEAL_CODE ='HCX17F1W01XXXX')THENCASE

WHENCAST(l.PREMIUM ASDECIMAL(12,2))BETWEEN 35.00 AND 52.99 THEN' '
WHENCAST(l.PREMIUM ASDECIMAL(12,2))> 52.99 THEN' '
ELSE' 'ENDAS PREMIUM_DECLINED_IND,

Here are the errors:
Msg 156, Level 15, State 1, Line 77
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near 'PREMIUM_DECLINED_IND'.
Msg 102, Level 15, State 1, Line 83
Incorrect syntax near 'PREMIUM_CODE'.
Msg 102, Level 15, State 1, Line 99
Incorrect syntax near 'a'.
Msg 102, Level 15, State 1, Line 100
Incorrect syntax near 'ap'.

Any help would be appreciated!:banghead:

 

[Frothingslosh]

You're missing the second (external) ELSE...END clause. That's why it's choking on the AS and everything after it.

 

[Tupacmoche]

There is an ELSE END AT THE END:

CASE
WHEN (AP.APPEAL_CODE ='HCX17F1W01XXXX')THENCASE

WHENCAST(l.PREMIUM ASDECIMAL(12,2))BETWEEN 35.00 AND 52.99 THEN' '
WHENCAST(l.PREMIUM ASDECIMAL(12,2))> 52.99 THEN' '
ELSE' 'END AS PREMIUM_DECLINED_IND

Do, I need another? Please show me.

 

[Frothingslosh]

You have two case statements, so you need two end statements.

Try indenting it a little more intuitively and you will be able to see it.

[FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]
SELECT
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]  CASE
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]      WHEN [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]AP[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]APPEAL_CODE[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]'HCX17F1W01XXXX'[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]THEN
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]          CASE
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]              WHEN [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]CAST[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]l[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]PREMIUM [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]AS [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]DECIMAL[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]12[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]2[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080])) BETWEEN[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 35.00 [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]AND[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 52.99 [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]THEN [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 
[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]              WHEN [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff][FONT=Consolas][SIZE=2][COLOR=#ff00ff]CAST[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]l[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]PREMIUM [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]AS [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]DECIMAL[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]12[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2]2[/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080][FONT=Consolas][SIZE=2][COLOR=#808080]))>[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][FONT=Consolas][SIZE=2] 52.99 [/SIZE][/FONT][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]THEN [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]' '
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]              ELSE [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000][FONT=Consolas][SIZE=2][COLOR=#ff0000]' '
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff][FONT=Consolas][SIZE=2][COLOR=#0000ff]          END
AS [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080][FONT=Consolas][SIZE=2][COLOR=#008080]PREMIUM_DECLINED_IND[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

See the problem?

 

[Tupacmoche]

I used this and it worked:

CASE
WHEN (AP.APPEAL_CODE = 'HCX17F1W01XXXX') THEN
CASE

WHEN CAST(l.PREMIUM AS DECIMAL(12,2))BETWEEN 35.00 AND 52.99 THEN ' '
WHEN CAST(l.PREMIUM AS DECIMAL(12,2))> 52.99 THEN ' '
ELSE ' ' END

END AS PREMIUM_DECLINED_IND,

It needs two END. But I would like to add another ELSE and this CASE Statement:

case when CAST(l.PREMIUM AS DECIMAL(12,2))<1 then ' ' else 'N' end

Now, I can't get that to work any suggestions? This is what I have:

CASE
WHEN (AP.APPEAL_CODE = 'HCX17F1W01XXXX') THEN
CASE

WHEN CAST(l.PREMIUM AS DECIMAL(12,2))BETWEEN 35.00 AND 52.99 THEN ' '
WHEN CAST(l.PREMIUM AS DECIMAL(12,2))> 52.99 THEN ' '
ELSE ' ' END

ELSE
case when CAST(l.PREMIUM AS DECIMAL(12,2))<1 then ' ' else 'N' end

END

END AS PREMIUM_DECLINED_IND,

 

[plog]

I think you need to spend more time thinking about your logic than the SQL at this point. Logically, you've got this:

If A>=35 AND A<=52.99 THEN X
If A>52.99 THEN X

The first statement serves no real purpose. You should combine those as such:

If A>=35 THEN X

That accomplishes what those other 2 statements do. Then as I look over the rest of the logic, it doesn't make much sense. I don't think you realize it, but PREMIUM_DECLINED_IND can end in 3 results:

'N'
''
NULL

Is that something you are aware of? Do you want both NULL and empty string for the final value? I suggest you spend a few moments and write what you want to occur in English, then translate it into SQL.

 

[Frothingslosh]

Plog, I read those as him having stripped actual values out as unnecessary for the discussion.