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
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?
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