RE: SQL Case question

Perhaps you should move the case into a CTE, so that the column references can work....

with CT (ForMonthYear, Amt) as
(
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,
AMT1
FROM FILE1
WHERE AMT1 > 49.99
)

SELECT ForMontYear, count(*), sum(Amt)
FROM CT
GROUP BY ForMonthYear


Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Alan Shore
Sent: Tuesday, December 18, 2007 9:34 AM
To: Midrange Systems Technical Discussion
Subject: SQL Case question


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