That's actually the first one I want to test, since I came across that function in my research before posting but could not get the syntax right.
Sean Porterfield
-----Original Message-----
From: Michael Schutte
Use the ROW_NUMBER suggestion I gave you to do that.
--
Michael Schutte
Admin Professional
"Porterfield,
Sean"
<SPorterfield@bea To
conroofingsupply. Midrange Systems Technical
com> Discussion
Sent by: <midrange-l@xxxxxxxxxxxx>
midrange-l-bounce cc
s@xxxxxxxxxxxx
Subject
RE: SQL question on join and limit
05/14/2009 04:48 rows retrieved
PM
It will take me a bit of time to parse these responses and test them out.
One thing I'm reading based on the below suggestion is MIN(LLINE) as if there is a line number. That would be too easy! The detail file has no unique key to identify a single record. Maybe that should be my first fix... If I do that, I could select LINE=1 and make this easy.
Sean Porterfield
-----Original Message-----
Something like this?
WITH ECLONE AS (
SELECT LORD, MIN(LLINE) AS LLINE
FROM GDIDIVF/ECL
GROUP BY LORD
ORDER BY LORD)
SELECT HORD, HCUST, LLINE
FROM GDIDIVF/ECH
LEFT OUTER JOIN ECLONE ON HORD=LORD
WHERE HORD<650
ORDER BY HORD, LLINE
....+....1....+....2.
Ord No Cust LLINE
No
585 11366 1
621 1115 1
623 1115 1
641 1115 1
648 11366 1
649 11366 1
Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From:
"Porterfield, Sean" <SPorterfield@xxxxxxxxxxxxxxxxxxxxxxx>
To:
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date:
05/14/2009 04:13 PM
Subject:
SQL question on join and limit rows retrieved Sent by:
midrange-l-bounces@xxxxxxxxxxxx
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.
midrange.com
