Purpose Using an outer join Reading data from the database often consists of reading a main table and several dependent tables, i.e. tables where the key fields are contained in columns of the main table. For performance reasons it is desirable to read multiple tables with a single RFC call, joining several tables. The "join" approach is faster than reading the dependent tables with single reads for each row of the main table, but you have to keep in mind that entries in the dependent tables might be missing and a normal "inner" join does not return a row of the main table if no matching entry is found in one of the dependent tables. In such situations you need an "outer" table join. |
Solution
Example The main table is KNA1 (customer master data) and the dependent tables are T005T (country names) and T005U (region names). The country code is always filled in KNA1. If you are sure that T005T contains a country name for all countries and all user languages, you can use an inner join to link KNA1 and T005T. If your are not sure, an outer join is a better alternative. The region code is not necessarily filled for all customers, which leaves the outer join as the only possibility, since customers without a region code would otherwise be missing in the result table:
GuiXT Script InputField
(1,1)
"Customer name contains"
(1,24)
size=30
name="cname"
-upperCase if
not
V[customercount] Table (3,1) (19,87) name="customers" rows="&V[customercount]" _title="&V[customercount] customers found" fixedColumns=4 Column
"Account"
size=12
name="kunnr"
-readOnly
InputScript "read_customers.txt" // reset table Clear V[customers.cell.*] Clear V[customers.stat.*] // Build join Set V[join] "" Set V[join] "&V[join] KNA1 " Set V[join] "&V[join] left outer join T005T " Set V[join] "&V[join] on T005T~LAND1 = KNA1~LAND1 " Set V[join] "&V[join] and T005T~SPRAS = '&V[_language]' " Set V[join] "&V[join] left outer join T005U " Set V[join] "&V[join] on T005U~LAND1 = KNA1~LAND1 " Set V[join] "&V[join] and T005U~BLAND = KNA1~REGIO " Set V[join] "&V[join] and T005U~SPRAS = '&V[_language]'" Clear text[r]// Select from
database in.fields="KNA1~KUNNR,KNA1~NAME1,KNA1~ORT01,T005T~LANDX,T005U~BEZEI" _ in.condition="KNA1~MCOD1 LIKE '%&V[cname]%' " _ table.values="r"
// result into table cell variables Set V[n] 1 label nextrowCopyText fromText="r" toString="customers.cell.kunnr.&V[n]" line="&V[k]" if Q[ok]Set V[k] &V[k] + 1 CopyText fromText="r" toString="customers.cell.name1.&V[n]" line="&V[k]" Set V[k] &V[k] + 1 CopyText fromText="r" toString="customers.cell.ort01.&V[n]" line="&V[k]" Set V[k] &V[k] + 1 CopyText fromText="r" toString="customers.cell.country.&V[n]" line="&V[k]" Set V[k] &V[k] + 1 CopyText fromText="r" toString="customers.cell.region.&V[n]" line="&V[k]" Set V[k] &V[k] + 1 Set V[n] &V[n] + 1 goto nextrow endif
// matching customer
count Return |
Remarks
Components |