Rob - thanks for your reply - and - yes, that was a cut and paste mistake.
I'm trying your suggestion right now and its definitely going further than
mine did, so thanks for your help
Alan Shore
NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
rob@xxxxxxxxx
Sent by:
midrange-l-bounce To
s@xxxxxxxxxxxx Midrange Systems Technical
Discussion
<midrange-l@xxxxxxxxxxxx>
12/18/2007 10:58 cc
AM
Subject
Re: SQL Case question
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
It's probably the group by 1 that's confusing it.
And, hopefully this is a pasting error (because I don't understand 'and
and')
WHERE
AMT1 > 49.99 and
and 1 <> 'BYPASS'
Does this work?
With T1 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 as ForMonthYear,
AMT1
FROM FILE1
WHERE AMT1 > 49.99)
Select ForMonthYear, count(*), sum(amt1)
from t1
where ForMonthYear<>'BYPASS'
Group by ForMonthYear
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
Alan Shore <AlanShore@xxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
12/18/2007 10:34 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc
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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com
