RE: SQL question on join and limit rows retrieved

d.X, d.Y, d.Z contain different values in each detail record, so GROUP BY there will return multiple records from the detail.

Sean Porterfield

-----Original Message-----
From: Elvis Budimlic

Couple of ideas off the top of my head:

SELECT h.A, (SELECT MAX(x),MIN(y),AVG(z) FROM detail d WHERE d.A = h.A) FROM header h

SELECT h.A,d.X,d.Y,d.Z
FROM header h INNER JOIN detail d USING(A) GROUP BY h.A,d.X,d.Y,d.Z

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and OS/400 www.centerfieldtechnology.com


-----Original Message-----
Subject: SQL question on join and limit rows retrieved

I have an RPGLE program that works and lists header/detail (one to many) for selection. This means a standard SETLL/READ or READE on the header and CHAIN to detail once to get the first record. The remaining detail records are only retrieved when one entry is selected. (The first detail record is intended as an overview / brief glimpse.)

I want to duplicate this with SQL to allow for easy enhancements and web enablement.

I already have my SELECT A,B,C,D FROM HEADER WHERE xxxxxx working fine. I'm trying to figure out how to JOIN to detail and only get the first row.
DISTINCT didn't seem to help me at all. FETCH FIRST 1 ROW ONLY limits the entire query to 1 row rather than 1 row from detail.

Is this possible or do I have to essentially duplicate the CHAIN meaning a SELECT A,X,Y,Z FROM DETAIL WHERE A='value from header' for each row retrieved on the HEADER select?

My expectation is if I can get the correct query in one, it will perform better than multiple selects over the detail.

I want to keep the number of records retrieved low by limiting the detail to
1 row at this point. (Although I don't even know for sure if I'll get the same row on my detail select as I get from the chain...)

Sean Porterfield

This email is confidential, intended only for the named recipient(s) above and may contain information that is privileged. If you have received this message in error or are not the named recipient(s), please notify the sender immediately and delete this email message from your computer as any and all unauthorized distribution or use of this message is strictly prohibited. Thank you.