Search code examples
outer-joinopenedgeprogress-4glprogress-db

Is it possible to get information from the same table twice in Progress-4GL?

I need to get data from a table in two instances. I'm converting Oracle SQL to Progress and the code is something like the following:

 select item.id,structure.group, item2.id
 from item,structure,item item2 
 where item.id = structure.id and structure.sub_id = item2.id (+)

I thought that the best idea would be to create a temp-table tt-item where I would store the values of item and then use an outer-join in a query to get the results, something like

DEFINE QUERY qItem FOR item,structure,tt-item.

Is this the best way to do it? Is there a better, more sophisticated way to do it so that I don't need to copy a the item table, as it has a lot of data and it could make loading it twice quite slow? I searched for this on the internet and couldn't find it.

Solution

  • You can also define a buffer. That's like a second record pointer.

    DEFINE BUFFER item2 FOR item. 
DEFINE QUERY qItem FOR item, structure, item2.

    A temp-table needs to be prefilled. The buffer on the database table is filled more on demand.