Purpose Read the SAP database using the Open Call interface
We can call up the function module /guixt/dbselect using the
Open Call interface. In addition to the advantages of the Open
all interface such as the bundling and parallel processing
capabilities, this simplifies the interpretation of the returned
values, since the interface automatically fills in the columns of a
given result table.
How to proceeed
Fill separate GuiXT variables to specify the database
table name, the "where" condition and the fields to return
Create a table variable for the result values It is not
necessary but can be helpful to use the database field names. The
mapping of the returned database values (result set) to the table
variable is by position, not by name.
Call up /guixt/dbselect Please note: the "exporting.xxx"
parameters are the "in.xxx" parameters of the former notation and
"import.xxx" are the former "out.xxx", i.e. we now use the standard
ABAP notation for the function call parameters. The former
"table.xxx" is now either "exporting.xxx" or "importing.xxx"
or both, depending on whether you want to export the table values,
import them or need both directions.
InputField (1,1) "Country" (1,20) size=2 _
techName="KNA1-LAND1" name="country" -upperCase
// Create table to determine the country name
CreateTable V[t005t] country
// country code specified?
if V[country]
Set V[table] "T005T"
Set V[condition] "LAND1 = '&V[country]' and SPRAS = '&V[_language]' "
Set V[fields] "LANDX"
Call /guixt/dbselect _
cache="session" _ // use cache="session" to save RFC calls
export.table="table" _
export.condition="condition" _
export.fields="fields" _
import.values="t005t"
endif
Text (1,26) "&V[t005t.country.1]"
Remarks
Use cache="..." if appropriate. For many text tables
cache="session" makes sense
See the article Read
an SAP table for how to deal with SQL syntax errors
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
GuiXT
InputField (1,1) "Customer name contains" (1,24) size=30 name="cname" -upperCase
Pushbutton (1,59) "Search" process="read_customers.txt" size=(1,17)
// create table if not yet done
if not V[customers.rowcount]
CreateTable V[customers] kunnr name1 ort01 country region
endif
Table (3,1) (19,87) name="customers" _
title="&V[customers.rowcount] 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"
GuiXT
// reset table display status
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]'"
// fields and condition for database call
Set V[fields] "KNA1~KUNNR,KNA1~NAME1,KNA1~ORT01,T005T~LANDX,T005U~BEZEI"
Set V[condition] "KNA1~MCOD1 LIKE '%&V[cname]%' "
// select customers
Call /guixt/dbselect cache="transaction" _
export.table="join" _
export.fields="fields" _
export.condition="condition" _
import.values="customers"
Return
We let the user enter a date interval and display a table of
all invoices created during this time.
In this example the table displayed contains an additional column,
the row number, which is not contained in the CDS view. We need to use
two different table variables, one with the row number to display the
table and one without to read the CDS view. After reading the CDS
view we copy the CDS table variable into the display table variable and
also set the row number column.
The InputScript "read_invoices.txt" calls up the CDS view and then
copies the CDS table variable into the display table
variable.
As you see, the CDS parameter P_LANGUAGE is passed in parentheses just
behind the CDS view name (no space character between is allowed). If
there are two or more parameters, separate them by a comma.
The two dates in the where-condition need to be passed in the format
YYYYMMDD which is different from the user date format, e.g. MM-DD-YYYY
in our example. We use the domname=, domvalue= feature of /guixt/dbselect
in order to convert the dates into this internal format.
InputScript "read_invoices.txt"
GuiXT
// clear display status
Clear V[invoices.stats.*]
// build up parameters for database call
Set V[cdsview] "V_SD_CI( P_LANGUAGE = 'E' )"
Set V[fields] "Invoice_Date,Invoice_Type,Document_ID,Customer_Id,
Customer_Name,Payment_Terms,Payment_Terms_Text,
Lifecycle_Status_Text,Currency,Net_Value"
Set V[condition] "Invoice_Date between @DATUM.1 and @DATUM.2 "
Set V[orderby] "Invoice_Date,Document_ID"
Set V[domname1] "DATUM.1"
Set V[domname2] "DATUM.2"
CreateTable V[cdsdata] include=V[invoice]
// call CDS view
Call /guixt/dbselect _
export.table="cdsview" _
export.fields="fields" _
export.condition="condition" _
export.orderBy="orderby" _
export.domname1="domname1" _
export.domvalue1="date1" _
export.domname2="domname2" _
export.domvalue2="date2" _
import.values="cdsdata"
// copy CDS table variable into display table variable
Clear V[invoices]
Set V[k] 1
label next_row
ReadRow V[invoice] table=V[cdsdata] index=&V[k]
if Q[ok]
AppendRow V[invoice] table=V[invoices]
Set V[invoices.No.&V[k]] "&V[k]"
Set V[k] &V[k] + 1
goto next_row
endif
Return