Joining Tables

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Target:

SQL

Syntax for Format 1

With DB-DECLARE

DB-DECLARE cursorname correlname1.copylibname-REC
... [DISTINCT]
... [column1 [... columnN]]|[NONE]
           .
... correlnameN.copylibname-REC
... [column1 [... columnN]]|[NONE]
           .
... [WHERE correlname.column1 op [:]value|correlname.column2
... [AND|OR correlname.column3 op [:]value|correlname.column4
           .
... AND|OR correlname.columnN operator [:]value|correlnameN]]
... [ORDER
... column1 [ASC|DESC] [...columnN [ASC]]]

Syntax for Format 2

With DB-OBTAIN

DB-OBTAIN REC correlname1.copylibname-REC
... [DISTINCT]
... [column1 [... columnN]]|[NONE]
           .
... REC correlnameN.copylibname-REC
... [column1 [... columnN]]|[NONE]
... [WHERE correlname.column1 op [:]value|correlname.column2
... [AND|OR correlname.column3 op [:]value|correlname.column4
           .
... AND|OR correlname.columnN op [:]val|correlname.columnN]]

Syntax for Format 3

With DB-PROCESS

DB-PROCESS REC correlname1.copylibname-REC
... [DB-PROCESS-ID name]
... [DISTINCT]
... [column1 [... columnN]]|[NONE]
           .
... REC correlnameN.copylibname-REC
... [column1 [... columnN]]|[NONE]
... [WHERE correlname.column1 op [:]value|correlname.column2
... [AND|OR correlname.column3 op [:]value|correlname.column4
           .
... AND|OR correlname.columnN op [:]val|correlname.columnN]]
... [DB-LOOP-MAX=number]
... [ORDER 
... column1 [ASC|DESC] [...columnN [ASC|DESC]]]
        Controlled logic block

General Rules:

Including a correlation name with each column is optional when joining tables. Correlation names help insulate your code against changes in table structures, and emphasize the exact columns you are accessing. A call requires a correlation name if the column name in a select list or WHERE clause appears in more than one of the joined tables.
Use the NONE keyword when you want to qualify on the table, that is, you reference the table with a WHERE clause, but do not want to select columns.
Join your tables together before coding WHERE. You can join any number of tables. You can also join a table to itself.
The WHERE clause refers to any column prefaced with the correlation name assigned to its table. Be sure to enter the correct correlation name–the SQL Generator cannot check it for you.

Example:

Select specific columns from tables D2MASTER and D2INVTRY; add qualifications to the WHERE clause. Note that every column in the WHERE clause is preceded by a correlation name.

DB-OBTAIN REC A.D2TAB-REC
... PM_PART_NO PM_UNITS PM_COLOR
... REC B.D2INVEN-REC
... IN_PART_NO IN_QTY_ONHAND 
... IN_DATE_LAST_ORDER
... WHERE A.PM_PART_NO = B.IN_PART_NO
... AND A.PM_COLOR = 'RED'
... AND B.IN_COLOR = 'RED'

Declare the cursor D2JOIN-CUR and include columns from tables D2MASTER and D2INVTRY. Use a WHERE clause to select rows where:

Columns PM_PART_NO and IN_PART_NO from each table match
Column IN_QTY_ONHAND from table D2INVTRY is greater than 100

Eliminate duplicate rows from the cursor set (rows with matching data in every selected column are considered duplicates). Sort the cursor set in ascending order by PM_PART_NO from table D2MASTER, then in descending order by IN_QTY_ONHAND from table D2INVTRY.

DB-DECLARE D2JOIN-CUR A.D2TAB-REC
... DISTINCT
... PM_PART_NO PM_COLOR
... B.D2INVEN-REC
... IN_PART_NO IN_QTY_ONHAND
... WHERE A.PM_PART_NO = B.IN_PART_NO
... AND B.IN_QTY_ONHAND > 100
... ORDER A.PM_PART_NO B.IN_QTY_ONHAND DESC

Process the cursor D2MAST-ID. The cursor set includes columns from tables D2MASTER and D2INVTRY. Use a WHERE clause to include rows in the cursor set where:

Columns PM_PART_NO and IN_PART_NO from each table match
Column PM_PART_SHORT_DESC from table D2MASTER equals the Working-Storage variable :WS-PART-SHORT-DESC
IN_QTY_ONHAND from table D2INVTRY is greater than 100

Eliminate duplicate rows from the cursor set (rows with matching data in columns PM_PART_NO, PM_COLOR, IN_PART_NO, and IN_COLOR are considered duplicates). Sort cursor set in ascending order by D2MASTER columns PM_PART_NO and PM_COLOR.

DB-PROCESS REC A.D2TAB-REC
... DB-PROCESS-ID D2MAST-ID
... DISTINCT
... PM_PART_NO PM_COLOR
... REC B.D2INVEN-REC 
... IN_PART_NO IN_COLOR
... WHERE A.PM_PART_NO = B.IN_PART_NO
... AND A.PM_PART_SHORT_DESC =
... :WS-PART-SHORT-DESC 
... AND B.IN_QTY_ONHAND > 100 
... DB-LOOP-MAX=999
... ORDER A.PM_PART_NO ASC
... A.PM_COLOR ASC

Join a table to itself. Retrieve records where the IN_QTY_ONHAND column is greater than 100 and retrieve records whose IN_DATA_LAST_ORDER column matches these records.

DB-PROCESS REC A.D2INVEN-REC
... DB-PROCESS-ID D2MAST-ID
... IN_PART_NO
....IN_QTY_ONHAND
... IN_DATE_LAST_ORDER
... REC B.D2INVEN-REC
... IN_PART_NO       (:WS-PART-NO)
... IN_QTY_ONHAND    (:WS-QTY-ONHAND)
... WHERE A.IN_DATA_LAST_ORDER = B.IN_DATE_LAST_ORDER
... AND A.IN_QTY_ONHAND > 100