CODEWITHSUNDEEP
progress-4glopenedge
FancyPanda
FancyPanda

Reputation: 85

How to query same table twice in Progess OpenEdge Procedure?

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?

Upvotes: 3

Views: 2242

Answers (2)

mollyfud
mollyfud

Reputation: 61

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.

Upvotes: 2

Tim Kuehn
Tim Kuehn

Reputation: 3251

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.

Upvotes: 5

Related Questions