SQL statement using RRN(file) on join pulling only Max(date) records from second file in iSeries

35 pts.
Tags:
IBM iSeries
JOIN statement
RRN
SQL
I am using WebSmart with the I-series data base files. I am joining two data base files receiving the RRN so that I can use the fetch rrn and the fextnext to display one screen at a time in my WebSmart program. I have used this method many times except this time I have a one to many relationship and I only want the Max(date) for the second file. I am getting an SQLCOD of -501. Can someone help? The SQL statement is: 
SELECT RRN(XTF419), XTF419.EICNTR, XTF419.EIEMP, XTF419.EILNAM, XTF419.EIFNAM,
XTF419.EISTA, XTF419.EIDEPT, XTL420L1.ECCNTR, XTL420L1.ECEMP,
XTL420L1.ECCNBR, MAX(XTL420L1.ECCDTE) FROM XTF419, XTL420L1 WHERE
XTF419.EICNTR = XTL420L1.ECCNTR AND XTF419.EIEMP = XTL420L1.ECEMP
AND XTL420L1.ECCNBR = '145MGMT' AND XTL420L1.ECRSTS <> 01 AND
XTL420L1.ECRSTS <> 20 GROUP BY XTF419.EICNTR, XTF419.EIEMP,
XTF419.EILNAM, XTF419.EIFNAM,XTF419.EISTA,
XTF419.EIDEPT,XTL420L1.ECCNTR, XTL420L1.ECEMP,XTL420L1.ECCNBR ORDER
BY XTF419.EICNTR, XTF419.EIEMP FOR FETCH ONLY
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to include the RRN(XTF419) in the GROUP BY clause

Kevin C. Ketzler

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • PlexPro
    >> You need to include the RRN(XTF419) in the GROUP BY clause It seems to me that adding the RRN to the group by clause would return all groups of one record, and defeat the purpose. Am I misunderstanding something about RRN? René Valencourt
    40 pointsBadges:
    report
  • BigKat
    Well, I assumed this was a one-to-many join, as the XTF419 fields were all already being grouped, and the XTL420L1 file was the one with a field that had a max() on it. Regardless, all fields not in a function like max() need to be in the GROUP BY clause. Kevin C. Ketzler
    9,460 pointsBadges:
    report
  • Isjld
    I tried the group by with the RRN before I submitted my question. As a matter of fact I tried multiple ways to get my data. As long as I didn't use the RRN it would work. In the SQL manual, in the section on RRN it states: The argument must not identify a view, common table expression, or derived table whose outer subselect includes a column function, a GROUP BY clause, a HAVING clause, a UNION clause, an INTERSECT clause, or DISTINCT clause. The RRN function cannot be specified in a SELECT clause if the subselect contains a column function, a GROUP BY clause, or a HAVING clause. If the argument is a correlation name, the correlation name must not identify a correlated reference. Anyway the way I finally worked it out was I joined my two files and then added within my where statement another select statement getting my max(eccdte): where ECCDTE = (SELECT MAX(ECCDTE)FROM XTF420 where XTF420.eccntr = XTF419.eicntr and XTF420.ecemp = XTF419.eiemp and XTF420.eccnbr = XTL420L1.eccnbr group by XTF420.eccntr, XTF420.ecemp, XTF420.eccnbr ). Thanks for your responses...
    35 pointsBadges:
    report
  • BigKat
    That is strange, because I couldn't get it to run anything similar without including the RRN in the GROUP BY??? Anyway, glad you got it working, but the subselect had to cause a performance hit. I hope there aren't too many records in the file.
    9,460 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: