I am pretty sure it is erroring on...
Sum(Midnight_Seconds(substring(qecmnt,10,8)) -
Midnight_Seconds(substring(qecmnt,33,8))) AS "Elapsed Time"
the substrings for qecmnt should produce a 99:99:99 format...
If so, just replace the colons through dots.
Sum(Midnight_Seconds(Translate(substring(qecmnt,10,8), '.', ':') -
Midnight_Seconds(Translate(substring(qecmnt,33,8), '.', ':')) AS
"Elapsed Time"
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Jay Vaughn
Gesendet: Tuesday, December 11, 2007 22:23
An: RPG programming on the AS400 / iSeries
Betreff: RE: sql statement structure
Thanks guys... but I am now going at this a different way... the following
actually runs but produces this error...
Selection error involving field *N.
Select or omit error on field (Cast(BANKCDTL_2.QECMNT[7], Char Fixed Len
40 Pad(x40,x4040)) AS Short Packed(1,0)) member BANKCHDR.
Select qfgrp AS "Group", qfdate AS "Date", CLSUPR AS "Team Lead",
qfuser AS "User",
qfstCd || Digits(qfArea) || Digits(qfBank) as "Acct#",
Sum(Midnight_Seconds(substring(qecmnt,10,8)) -
Midnight_Seconds(substring(qecmnt,33,8))) AS "Elapsed Time"
From BANKCHDR
Join BANKCDTL
On qFStCd=qEStCd And qFArea=qEArea And qFBank=qEBank And qFGrp=qEGrp
And qFTYPE=qETYPE And QFCRCD=QECRCD
Join CSRCLVLPF
On QFGRP=CLLGRP AND QFUSER=CLCSR
Where QFTYPE = '3' AND idate(QFDATE) >= (CURRENT DATE - 7 DAYS)
AND CLSUPR IN ('MELANIEG', 'MARYB', 'AMYR', 'MELISSAJ', 'SERGIOC')
AND QECMNT = 1
ORDER BY QFGRP, QFDATE, CLSUPR, QFUSER
I am pretty sure it is erroring on...
Sum(Midnight_Seconds(substring(qecmnt,10,8)) -
Midnight_Seconds(substring(qecmnt,33,8))) AS "Elapsed Time"
the substrings for qecmnt should produce a 99:99:99 format...
Any ideas?
Thanks,
Jay Vaughn
Senior I5 Programmer/Analyst
RateWatch
(P) 251.633.8929
(F) 251.633.8929
www.rate-watch.com
-----Original Message-----
From: "Wilt, Charles" <WiltC@xxxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Date: Tue, 11 Dec 2007 12:19:36 -0500
Subject: RE: sql statement structure
Note that you only need the group by if you have columns in your select that
are not used by a summary
function.
In other words, this would work:
Select max(amount), avg(amount)
From myfile
As would this
Select itemnbr, max(amount), avg(amount)
From myfile
Group by itemnbr
But this doesn't
Select itemnbr, max(amount), avg(amount)
From myfile
HTH,
Charles
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Bob Elliott
Sent: Tuesday, December 11, 2007 11:38 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: sql statement structure
Jay,
It looks like you are missing the group by. If you are going to use any of
the summarization functions, you need a group by.
Bob Elliott
Sr Programmer
City of Winchester
15 North Cameron St
Winchester, VA 22601
Phone: (540)-667-1815 ext 1462
Fax (540)-722-3618
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Jay Vaughn
Sent: Tuesday, December 11, 2007 11:34 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: sql statement structure
when I run the following, there are no problems...
SELECT qfGrp, qfDate, clSupr, qfUser, qfStCd, qfArea, qfBank
FROM BANKCHDR
Join BANKCDTL On qFStCd=qEStCd And qFArea=qEArea And qFBank=qEBank
And qFGrp=qEGrp And qFTYPE=qETYPE And QFCRCD=QECRCD Join CSRCLVLPF
On QFGRP=CLLGRP AND QFUSER=CLCSR WHERE QFTYPE = '3' AND
idate(QFDATE) >= (CURRENT DATE - 7 DAYS) AND CLSUPR IN ('MELANIEG',
'MARYB', 'AMYR', 'MELISSAJ', 'SERGIOC') AND QECSEQ = 1 ORDER BY
QfGRP, QFDATE, CLSUPR, QFUSER
But when I try to add... Max(Case When qeCSeq=1 Then substring(qecmnt,10,8)
Else '' End) As
TimeIn
as in the following, I get... "Column QFGRP or expression in SELECT list not
valid. "...
SELECT qfGrp, qfDate, clSupr, qfUser, qfStCd, qfArea, qfBank,
Max(Case When qeCSeq=1 Then substring(qecmnt,10,8) Else '' End) As
TimeIn
FROM BANKCHDR
Join BANKCDTL On qFStCd=qEStCd And qFArea=qEArea And qFBank=qEBank
And qFGrp=qEGrp And qFTYPE=qETYPE And QFCRCD=QECRCD Join CSRCLVLPF
On QFGRP=CLLGRP AND QFUSER=CLCSR WHERE QFTYPE = '3' AND
idate(QFDATE) >= (CURRENT DATE - 7 DAYS) AND CLSUPR IN ('MELANIEG',
'MARYB', 'AMYR', 'MELISSAJ', 'SERGIOC') AND QECSEQ = 1 ORDER BY
QfGRP, QFDATE, CLSUPR, QFUSER
anyone know what I am missing here???
Thanks,
Jay Vaughn
Senior I5 Programmer/Analyst
RateWatch
(P) 251.633.8929
(F) 251.633.8929
www.rate-watch.com
midrange.com
