Re: SQL question on join and limit rows retrieved

[rob@xxxxxxxxx]

Expanding that out...

WITH ECLONE AS ( 
 SELECT LORD as ONELORD, MIN(LLINE) AS ONELLINE 
 FROM GDIDIVF/ECL 
 GROUP BY LORD 
 ORDER BY LORD) 
SELECT HORD, HCUST, ONELLINE, L.* 
FROM GDIDIVF/ECH 
 LEFT OUTER JOIN ECLONE ON HORD=ONELORD 
 LEFT OUTER JOIN GDIDIVF/ECL L ON HORD=LORD AND ONELLINE=LLINE
WHERE HORD<650 
ORDER BY HORD 

....+....1....+....2....+....3....+....4....+....5....+..
Ord No    Cust  ONELLINE  ID  Ord No  Ord Ln  Item Number
          No                          Num 
 
   585   11366       1    ZL     585      1   MSHPSILCVR5
   621    1115       1    ZL     621      1   1701270 
   623    1115       1    ZL     623      1   1701271 
   641    1115       1    ZL     641      1   1701271-A 
   648   11366       1    ZL     648      1   MSHPSILCVR5
   649   11366       1    ZL     649      1   MSHPSILCVR5


Rob Berendt