How to query same table twice in Progess OpenEdge Procedure?

Question

In this example, I have two tables; Order Header (oe-hdr) and Location (location). The order header table contains two fields (sale-location-key and ship-location-key) which have an associated location name in the location table. If I were to use SQL to get my results, I would do something like this..

SELECT oe-hdr.order-num, oe-hdr.order-date, saleloc.location-name, shiploc.location-name
FROM oe-hdr,
    (SELECT location.location-name
     FROM oe-hdr, location
     WHERE oe-hdr.sale-location-key = location-key) as saleloc,
    (SELECT location.location-name
     FROM oe-hdr, location
     WHERE oe-hdr.ship-location-key = location-key) as shiploc
WHERE oe-hdr.order-num = saleloc.order-num 
AND oe-hdr.order-num = shiploc.order-num

Does anyone know how to replicate this in a Progress procedure?

score 5 · Accepted Answer · answered Jul 21 '14 at 17:27

5

Define two buffers for "location" and then do a for-each with a link to the buffers:

DEFINE BUFFER saleloc FOR location.
DEFINE BUFFER shiploc FOR location.

FOR EACH oe-hdr
   NO-LOCK,

   EACH saleloc
      WHERE saleloc.location-key = oe-hdr.sale-location-key
      NO-LOCK,

   EACH shiploc
      WHERE shiploc.location-key = oe-hdr.ship-location-key
      NO-LOCK
     :

   DISPLAY
       oe-hdr.order-num
       oe-hdr.order-date
       saleloc.location-name
       shiploc.location-name
     DOWN
     .

END.

one note - if the sale or ship-to doesn't exist in the location table, then the entire record will not be displayed. You'll need a different approach if you need that functionality - it'll involve moving the "linking" to a pair of "FIND" statements in the FOR EACH block.

answered Jul 21 '14 at 17:27

Tim Kuehn

3,201
1
17
23

This worked perfectly! Fortunately, I am 99.9% positive that I shouldn't encounter the above situation. – FancyPanda Jul 21 '14 at 18:44
1

and nice thing is you can define a buffer name with same name as table. DEFINE BUFFER oe-hdr FOR oe-hdr. – AquaAlex Jul 22 '14 at 10:58
1

I use DEFINE BUFFER oe-hdr FOR oe-hdr to strong scope the buffer to the current procedure, which helps avoid things like transaction scope creep and inadvertent side-effects of a change to a buffer in one routine affecting a buffer with the same name in another procedure / function. – Tim Kuehn Jul 22 '14 at 12:10

score 2 · Answer 2 · answered Jul 21 '14 at 21:22

2

To overcome Tims point about the missing addresses you could have a function or method (if using OO code) that returns the location-name and use that in the display. It would allow for better error handling on that front. Not sure the performance impact though. Just a thought.

answered Jul 21 '14 at 21:22

mollyfud

61
2

This is also a valid and performant approach I've used in many places. It's a bit more programming work to accomplish though. – Tim Kuehn Jul 21 '14 at 23:57

How to query same table twice in Progess OpenEdge Procedure?

2 Answers2