RE: Possible in SQL?

["Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

Sorry, got "Column YEAR not in specified tables." I would also get the
message "Column DTECHK or expression specified in SELECT list not valid."
since I selected the dtechk field and didn't use an aggregate function on
it. But it sparked an idea, and this DOES work:

select a.emply,
year(date(substr(digits(a.dtechk),1,4)||'-'||
          substr(digits(a.dtechk),5,2)||'-'||
          substr(digits(a.dtechk),7,2))),
month(date(substr(digits(a.dtechk),1,4)||'-'||
           substr(digits(a.dtechk),5,2)||'-'||
           substr(digits(a.dtechk),7,2))),
sum( b.amtdol )
from pylookup/prtranh a
join pylookup/prtrand b on (a.refer=b.refer)
where a.emply='9360'
and a.dtechk between 20000101 and 20011231
and payid <= 3000
group by a.emply,
year(date(substr(digits(a.dtechk),1,4)||'-'||
          substr(digits(a.dtechk),5,2)||'-'||
          substr(digits(a.dtechk),7,2))),
month(date(substr(digits(a.dtechk),1,4)||'-'||
           substr(digits(a.dtechk),5,2)||'-'||
           substr(digits(a.dtechk),7,2)))
order by 1

Thanks for the inspiration!
Loyd


-----Original Message-----
From: Fisher, Don [mailto:Dfisher@roomstoreeast.com]
Sent: Wednesday, May 15, 2002 8:23 AM
To: 'midrange-l@midrange.com'
Subject: RE: Possible in SQL?


Change your "group by" clause to "group by a.emply,year,month" and you
should see the output you expect.

Hope that helps.

Donald R. Fisher, III
Project Manager
The Roomstore Furniture Company
(804) 784-7600 extension 2124
DFisher@roomstoreeast.com

<clip>
select a.emply,
year(date(substr(digits(a.dtechk),1,4)||'-'||
          substr(digits(a.dtechk),5,2)||'-'||
          substr(digits(a.dtechk),7,2))),
month(date(substr(digits(a.dtechk),1,4)||'-'||
           substr(digits(a.dtechk),5,2)||'-'||
           substr(digits(a.dtechk),7,2))),
sum( b.amtdol )
from pylookup/prtranh a
join pylookup/prtrand b on (a.refer=b.refer)
where a.emply='9360'
and a.dtechk between 20000101 and 20011231
and payid <= 3000
group by a.emply, a.dtechk
order by 1
<clip>