Re: sql statement structure

[Michael_Schutte@xxxxxxxxxxxx]

A Group By clause...
  GROUP BY qfGrp, qfDate, clSupr, qfUser, qfStCd, qfArea, qfBank

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
3776 South High Street, Columbus, OH 43207
Phone: 614/492-7419    Fax: 614/409-2017
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"


rpg400-l-bounces@xxxxxxxxxxxx wrote on 12/11/2007 11:34:17 AM:

> 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
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.