SQL Case question

[Alan Shore <AlanShore@xxxxxxxx>]

Hi everyone
I have an SQL question
I have a file with a date held in a 6 digit/numeric field (NOT my design)
in the format MMDDYY
here is my SQL statement
SELECT case substr(digits(DATEFIELD), 5, 2) || substr(digits(DATEFIELD), 1,
2)
when '7901' then '01/07 - Jan 07'
when '7902' then '02/07 - Feb 07'
when '7903' then '03/07 - Mar 07'
when '7904' then '04/07 - Apr 07'
when '7905' then '05/07 - May 07'
when '7906' then '06/07 - Jun 07' else 'BYPASS' end as ForMonthYear,
count(*), sum(AMT1) FROM FILE1 WHERE
AMT1 > 49.99 and
 and 1 <> 'BYPASS' GROUP BY 1

when I run this I get the error
Column DATEFIELD or expression in SELECT list not valid.
DATEFIELD definitely is in FILE1
Is the problem with my CASE select?
If anyone can point me in the right direction, it would be MUCH appreciated

Thanks in advance



Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill