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
The  function module /guixt/dbselect supports both inner joins and outer joins.

Example
We let the user enter a search term and display a table of all customers whose name contains the search term:

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:


Outer join

 


Inner join

 

GuiXT Script

InputField (1,1) "Customer name contains" (1,24) size=30 name="cname" -upperCase
Pushbutton (1,59) "Search" process="read_customers.txt" size=(1,17)

if not V[customercount]
  Set V[customercount]
0
endif

Table (3,1) (19,87) name="customers" rows="&V[customercount]" _
  
title="&V[customercount] customers found" fixedColumns=
4

Column "Account" size=12 name="kunnr" -readOnly
Column "Name" size=20 name="name1" -readOnly
Column "City" size=20 name="ort01" -readOnly

Column "Country" size=10 name="country"
-readOnly
Column "Region" size=20 name="region"
-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
Call
/guixt/dbselect cache="transaction" in.table="&V[join]" _
 
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[k] 1
Set V[n] 1

label nextrow
CopyText
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
Set
V[customercount] &V[n] - 1

Return

Remarks

  • If the SQL expression for the join becomes rather complex, it can save you development  time when you first test the Select statement in a small ABAP program and then copy the Select statement into your GuiXT script. The ABAP syntax check shows errors that would lead to a runtime error and an ST22 dump when executed via /guixt/dbselect.
  • Be careful to distinguish conditions in the on-condition of the table join and in the where-condition. For example, when you put the term

      and T005U~SPRAS = '&V[_language]'

    into the where-condition instead of the on-condition, customers without a region code will not be returned.

 

Components
InputAssistant