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
>> 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
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
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...
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.
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: 4  Replies