Select Case Statement in a crystal command

[MB57630]

I am trying to create a command in crystal with the following and I keep getting errors...somethin about an interval qualifier at the end of the statement. Any ideas??

SELECT *
FROM KCMS.FCT_WEK_LVL_COU
WHERE
CASE
WHEN rpt_cd in ('58','59','60','62','63') Then 'LCM Themed'
WHEN rpt_cd in ('21','22','23','25','26','27') Then 'Kroger Personal Finance'
WHEN rpt_cd in ('44','45','46','48','51','53') Then 'Corporate Programs'
ELSE 'Other'
END;

Thanks!

 

[Turkbear]

Hi,
Your CASE clause does not create a criteria for
the WHERE to use - What you want is something like this:

SELECT K.*,(
CASE
   WHEN rpt_cd in ('58','59','60','62','63') Then 'LCM Themed'
   WHEN rpt_cd in ('21','22','23','25','26','27') Then 'Kroger Personal Finance'
   WHEN rpt_cd in ('44','45','46','48','51','53') Then 'Corporate Programs'
ELSE 'Other'
END
) ReportCode
FROM  KCMS.FCT_WEK_LVL_COU K

You probably should use an explicit list of fields instead of * in this case so the rpt_cd is not repeated.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

You would need the CASE statement to provide a condition. Putting the statement the way you have is akin to:

select * from table
where column

instead of

select * from table
where column = 'X'

So you would need to compare the output of your CASE statement to something e.g. a column or input variable.

Retired (not by choice) Oracle contractor.