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

Isjld

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: February 15, 2008 7:00 PM Last updated: November 24, 2014 6:18 PM

Answer Wiki

Last updated: November 24, 2014 6:17 PM GMT

Michael Tidmarsh62,180 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh62,180 pts.
- BigKat9,110 pts.

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

PlexPro Feb 19, 2008 4:06 PM GMT

>> 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 Feb 20, 2008 2:27 PM GMT

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,110 pointsBadges:

report
Isjld Feb 20, 2008 3:07 PM GMT

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 Feb 21, 2008 2:22 PM GMT

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,110 pointsBadges:

report