String Function in Case Statement

[msawyer]

Hello, I have the following case statement that is throwing an error.  I cannot figure out why it isn't working.  Help please!

CASE (cast(extract(month , [Presentation Layer].[Sales Order].[CRD Dt]),varchar(2)))
WHEN 1 THEN 'SP'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 2 THEN 'SP'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 3 THEN 'SP'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 4 THEN 'SU'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 5 THEN 'SU'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 6 THEN 'SU'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 7 THEN 'FA'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 8 THEN 'FA'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 9 THEN 'FA'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 10 THEN 'HO'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 11 THEN 'HO'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
WHEN 12 THEN 'HO'+ right(cast(extract( year, [Presentation Layer].[Sales Order].[CRD Dt]), varchar(4)),2)
else year([Presentation Layer].[Sales Order].[CRD Dt])
END

[CognosPaul]

First problem that I see is you're mixing data types, and it looks like you're also mixing local and database functions.

First, get rid of the cast for the month.

case extract(month,date)
when 1
when 2
etc

You're using numbers for the comparison against a string. So '01' will not equal 1. If that doens't work, there are some more things to fix. What db are you using?

[Rahul Ganguli]

Replace
CASE (cast(extract(month , [Presentation Layer].[Sales Order].[CRD Dt]),varchar(2)))
with
CASE (extract(month , [Presentation Layer].[Sales Order].[CRD Dt]))

Try this, it should work.

Regards,
Rahul