CODEWITHSUNDEEP
progress-4glopenedgeprogress-db
Riyaz
Riyaz

Reputation: 9

How to link two tables using temp table in progress

I am new in Progress 4gl i want to get data of two tables in a single table using temp criteria. I have two tables ih_hist and sod1_det, I want to achieve 2,2 columns from each tables. From ih_hist columns that i need, are ih_nbr and ih_inv_nbr and from sod1_det columns are sod1_nbr and sod1_shipfrom_ctry. please help me out. thanks in advance

Upvotes: 1

Views: 1765

Answers (1)

Jensd
Jensd

Reputation: 8011

Since your're not posting any information about your temp-tables I'm guessing that they are linked together by a field named "id". I'm also assuming that ih_hist is a "master" table and sod1_det contains the details. This could very well be wrong - you need to inform us more about your data!

There's a couple of parts of this you need to get right: first of all the joining of two temp-tables. This depends highly on your temp-table definitions, temp-table contains and what you really want. Do you have a one-to-many relation, a one-to-one or perhaps even many-to-many? Is the relation based on a single field (a foreign key) or is it based on many different fields? The temp-tables (like the actual fields in a Progress database) don't contain the relation - the relation is handled by code!

This is a basic example that might help you get started!

DEFINE TEMP-TABLE ih_hist NO-UNDO
    FIELD id         AS INTEGER
    FIELD ih_nbr     AS INTEGER
    FIELD ih_inv_nbr AS INTEGER.

DEFINE TEMP-TABLE sod1_det NO-UNDO
    FIELD id                 AS INTEGER
    FIELD sod1_nbr           AS INTEGER
    FIELD sod1_shipfrom_ctry AS CHARACTER.

DEFINE TEMP-TABLE ttNewTempTable NO-UNDO
    FIELD ih_nbr     AS INTEGER
    FIELD ih_inv_nbr AS INTEGER
    FIELD sod1_nbr           AS INTEGER
    FIELD sod1_shipfrom_ctry AS CHARACTER.

FOR EACH ih_hist, EACH sod1_det WHERE sod1_det.id = ih_hist.id:
    CREATE ttNewTempTable.
    BUFFER-COPY ih_hist TO ttNewTempTable.
    BUFFER-COPY sod1_det TO ttNewTempTable.
END.

I can use BUFFER-COPY because the field names match in the source and target temp-tables and don't overlap (ie no name in id_hist match a field in sod1_det but contains different data - like a name field in both). Otherwise you could also do an ASSIGN or a combination of BUFFER-COPY and ASSIGN.

Example with only ASSIGN:

FOR EACH ih_hist, EACH sod1_det WHERE sod1_det.id = ih_hist.id:
    CREATE ttNewTempTable.
    ASSIGN 
      ttNewTempTable.ih_nbr             = ih_hist.ih_nbr
      ttNewTempTable.ih_inv_nbr         = ih_hist.ih_inv_nbr
      ttNewTempTable.sod1_nbr           = sod1_det.sod1_nbr
      ttNewTempTable.sod1_shipfrom_ctry = sod1_det.sod1_shipfrom_ctry.
END.

Example with ASSIGN and BUFFER-COPY:

FOR EACH ih_hist, EACH sod1_det WHERE sod1_det.id = ih_hist.id:
    CREATE ttNewTempTable.
    BUFFER-COPY ih_hist TO ttNewTempTable
        ASSIGN 
           ttNewTempTable.sod1_nbr           = sod1_det.sod1_nbr
           ttNewTempTable.sod1_shipfrom_ctry = sod1_det.sod1_shipfrom_ctry.
END.

Based on your comment the WHERE-clause should likely look something like this:

FOR EACH ih_hist, EACH sod1_det WHERE sod1_det.sod1_nbr = ih_hist.ih_nbr:

Upvotes: 4

Related Questions