Use the ROW_NUMBER suggestion I gave you to do that.
--
Michael Schutte
Admin Professional
Try Bob Evans GRILLING SAUSAGE! This summerâs hottest destination is your
own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
using Bob Evans grilling sausage, visit
http://www.BobEvans.com/Recipes
"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
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com
