Purpose The function module  /guixt/dbselect offers you a comfortable way to read the SAP database in a GuiXT script.

You execute the function with the Call statement. Both single and multiple records can be returned by the function.

Prerequisites:

  • The function module has been imported into your SAP system (see "Import of the function module" below)
     
  • The RFC user has the necessary user authorizations (see "User authorizations" below)

 
Import of the
 function module
Authorizations You can either execute the Call with the RFC user specified in the GuiXT profile or with the user that is currently logged on  (option -currentUser in Call command).

Following authorizations are needed:

Function group authorization for /GUIXT/DB01:
S_RFC: RFC_TYPE=FUGR, RFC_NAME=/GUIXT/DB01, ACTVT=16

Table group display authorization:
S_TABU_DIS: DICBERCLS=XXXXX, ACTVT=03
 

Here XXXX is the SAP authorization group of the table that you want to read (internally stored in table TDDAT).

Note: The table display autorization S_TABU_DIS is checked in the same way in the SAP transactions SE16 and SM30 and in the standard function module RFC_READ_TABLE.
 
Parameters Required parameters:
  • in.table=  Table name 
     
  • in.Condition=  Search condition  (or table.Conditiontable=)
      
  • in.Fields=  Field list

Optional parameters:

  • in.orderBy=  Ordering of the result set
  • in.Distinct=  "X" = Select Distinct
     
  • in.Maxcount=  Maximum number of returned table rows
     
  • in.Username=  User name, whose settings will be used, e.g. date format and decimal values
     
  • in.Domnane1
    in.Domvalue1      SAP DDIC domain e.g. KUNNR and a domain value in external format, e.g. 1032. In the search condition the string @KUNNR will then be replaced by a literal with the internal value format, e.g. '0000001032'.
     
  • in.Domnane2 to in.Domnane8
    in.Domvalue2 to in.Domvalue8 further domains and values
    If a domain e.g. KUNNR is needed with several different values in the search condition you may add a suffix to the domain name, e.g. KUNNR.X1 and KUNNR.X2.
     
  • out.Found=  "X" = At least one row selected
     
  • out.Reccount=  Number of selected rows 0,1,2,...
     
  • table.Values= All values read, formatted as longtext, one value per text line
     
  • table.Conditiontable= Serach condition as longtext
 in.table= Table or table view to be read. Example: 

... in.table="T001"

In addition to normal tables you may use the search help views M_..., e.g. M_MAT11 and views that join several tables. For example, the view "VBAKUK" for a customer order returns both general information from table VBAK and status information from table VBUK.

Joining tables
You may combine several tables in a "join". This is an "inner join",  i.e. you obtain only those values for which matching values according to the "on"-condition exist in all joined tables. For example,  in "Example 4" (below) you would not obtain customers with a country code that does not exist in table T005T for the given language.

The table authority is checked for all joined tables.
in.Condition= Search condition in the format of the ABAP "Select" command. Example:

... in.Condition="BUKRS = '1000' ".

Note that ABAP strings are enclosed with inverted commas.

As usual the content of a script variable is addressed by &V[varname]:

... in.Condition="BUKRS = '&V[mybukrs]' ".

If you want to compare one column with the value in another column, use the notation  table~column, e.g.:

... in.Condition="PARVW = 'RE' AND KUNNR <> KNVP~KUNN2"

For further details see the ABAP "Select" documentation.

A search condition is always required, specified either with in.Condition=  or with table.Conditiontable=.

The  in.Condition= parameter is restricted to a total length of 4000 bytes. If you need to pass many keys, e.g. 1000 customer numbers, use the table.Conditiontable= parameter which allows any number of lines, each with a maximum length of 4000 bytes.

You need to use the internal format of each value, for example you must use customer number  '0000001032' and not '1032'.
The conversion from external to internal format is made automatically within the function /guixt/dbselect if you use the parameters in.domname1, in.domname2,... and in.domvalue1, din.omvalue2,... 

Example: in.Condition="KUNNR = @KUNNR"   in.Domnane1="KUNNR"  in.Domvalue1="1032"

See also Example 5 (below).
 
in.Fields= List of field names, separated by commas. Only the given fields are read from the database. Example:

... in.Fields="KUNNR,NAME1,NAME2,ORT01,STRAS,PSTLZ,LAND1".

The field values are returned in external format. You may put a  * before the file name in order to prevent the formatting of the value. In this case, date values are returned in format YYYYMMDD and numbers with  '.' as decimal separator. Account number, material numbers etc. are returned with leading zeros e.g.  0000002000 instead of 2000. This is useful if you want to use the value in subsequent database calls.

It is also possible to specify a field name twice, with and without *, in order to obtain both value formats. Example:

... in.Fields="KUNNR,*KUNNR,NAME1,NAME2,ORT01,STRAS,PSTLZ,LAND1".

If no field list is specified, the number of records that fulfill the specified condition is returned in the statement parameter Reccount.

Field names and Join

In case of a "join" operation for several tables, use the notation TABLENAME~FIELDNAME, e.g.

... in.Fields="KNA1~KUNNR,KNA1~NAME1,T005T~LANDX".

For database columns in the format  P (decimal packed), I (integer),  D (date) or T (time) the type needs to be specified explicitely. You may add the option "type P", "type I", "type D", "type T", e.g.

...  in.fields="KNA1~KUNNR,S001~UMNETWR type P,S001~STWAE"

or you use the option "as x", where x is P1,...P30 for packed decimal, I1,...I30 for integer, D1,...D30 for date, T1,...T30 for time.
The  "as ..." option is particurlarly useful when you want to name an for aggregated value such as SUM( ... ) in the orderby-list; sie example 6 (below).
For  character type fields the notation  "as F1", "as F2",... can be used.
in.Orderby= Optionally a list of field names, separated by comma. The result table is ordered ascending by the given fields.  Example:

... in.Orderby="ORT01,NAME1".

After each field name you may specifiy "DESCENDING" for descending ordering. Example:

... in.Orderby="NAME1 DESCENDING, ORT01 DESCENDING".

in.Groupby= Optionally a list of field names, separated by comma. The result table is grouped according to the given fields; this is needed if you use aggregation functions such as SUM(  ). See also Example 6 (below).
in.Distinct=  With "X" the DISTINCT clause of the Select command is used
out.Found= Returns "X" if at least one record returned, "" otherwise.
out.Reccount= Returned record count
table.Values= Name of a longtext variable which will contain all values returned. Each text line contains one value for the fields specified with in.Fields. If more than one table row is returned, the text lines are repeated for each row.
table.
Conditiontable=
 

Search condition in ABAP "Select" format. In contrast to in.Condition= you pass a long text variable that can contain any number of lines.

Please note:

  • Maximum 8000 characters per line
  • There is a maximum number of conditions per select statement per database system, in HANA e.g.16 000 or 32 000 conditions depending on the version.

Example:

We assume that a long text variable "kns" contains a list of account numbers. We want to use the account numbers in order to build up the search condition, using an "OR" clause for each account number:

Set text[ctab] ""
 Set V[i] 1

label next
 CopyText fromText="kns" toString="kn" line="&V[i]"
 if Q[ok]
   if V[i=1]
   Set V[condline] "KUNNR = '&V[kn]'"
   else
      Set V[condline] "OR KUNNR = '&V[kn]'"
   endif

 CopyText fromString="condline" toText="ctab" -appendLine
  Set V[i] &V[i] + 1
  goto next

endif

Call /guixt/dbselect in.table="KNA1" in.fields="KUNNR,NAME1,ORT01"   table.conditiontable="ctab" ...
 
Example 1  Example: We read all customers with city  "HEIDELBERG":

// Clear result table (important for performance reasons)
Clear text[r]

// Read data
Call /guixt/dbselect in.table="KNA1" in.fields="KUNNR,NAME1,NAME2,PSTLZ,ORT01,LAND1" in.condition="ORT01 = 'Heidelberg'" table.values="r"

// Test output
Message "&text[r]"

 
Example 2  Read text and city of a plant

// Set test data
Set V[plant] "1000"

// Clear result table (important for performance reasons)
Clear text[r]

Call /guixt/dbselect cache="session" in.table="T001W" in.Condition="WERKS = '&V[plant]'" in.Fields="NAME1,ORT01" table.Values="r"

CopyText fromText="r" toString="pname" line=1
CopyText fromText="r" toString="pcity" line=2

Message "Plant &V[plant]: &V[pname], &V[pcity]"

 
Example 3  Search customers by name or by city and display them in your own table control:

// GuiXT Script
InputField (1,10) "Name" (1,24) size=30 name="mcod1" -upperCase
InputField (2,10) "City" (2,24) size=30 name="mcod3" -upperCase

Pushbutton (1,56) "Search" process="search_customers.txt" size=(2,20)

if V[rcount]
  Table (4,10) (20,182) name="rtab" Title="&V[rcount] Kunden gefunden" rows="&V[rcount]"
  Column "Customer" size=12 name="kunnr" -readOnly
  Column "Name" size=32 name="name1" -readOnly
  Column "Post code size=12 name="pstlz" -readOnly
  Column "City" size=32 name="ort01" -readOnly
  Column "Street size=32 name="stras" -readOnly
  Column "Created" size=12 name="erdat" -readOnly
endif

//InputScript
if not V[mcod1] and not V[mcod3]
  Message "E: Please specify name or city" -statusline
  Return
endif

// clear result table (performance!)
Clear text[r]

// clear table control data
Clear V[rtab.*]

// read data. Set maximum record count because of table control limitation.
Call /guixt/dbselect
in.table="KNA1" in.fields="KUNNR,NAME1,PSTLZ,ORT01,STRAS,ERDAT" in.username="&V[user]" in.maxcount=100
in.condition="MCOD1 like '&V[mcod1]%' and MCOD3 like '&V[mcod3]%'"
out.reccount="rcount" table.values="r"

// fill table control
Set V[k] 0
Set V[row] 0

label nextrow
Set V[row] &V[row] + 1
Set V[k] &V[k] + 1

CopyText fromText="r" toString="rtab.cell.kunnr.&V[row]" line="&V[k]"

if Q[ok]
  Set V[k] &V[k] + 1
   CopyText fromText="r" toString="rtab.cell.name1.&V[row]" line="&V[k]"

 Set V[k] &V[k] + 1
  CopyText fromText="r" toString="rtab.cell.pstlz.&V[row]" line="&V[k]"

 Set V[k] &V[k] + 1
 CopyText fromText="r" toString="rtab.cell.ort01.&V[row]" line="&V[k]"

 Set V[k] &V[k] + 1
 CopyText fromText="r" toString="rtab.cell.stras.&V[row]" line="&V[k]"

 Set V[k] &V[k] + 1
 CopyText fromText="r" toString="rtab.cell.erdat.&V[row]" line="&V[k]"

  goto nextrow
endif

Return
Example  4  Join: Read customers with country name from KNA1 and T005T

// clear result table
Clear text[r]

// Join the tables KNA1 and T005T
Call "/guixt/dbselect" in.table="KNA1 join T005T on KNA1~LAND1 = T005T~LAND1" _
  in.fields="KNA1~KUNNR,KNA1~NAME1,KNA1~LAND1,T005T~LANDX" _
  in.condition="KNA1~LAND1 EQ 'CH' AND T005T~SPRAS = 'E'" _
  table.values="r"

// Test output
Message "&text[r]"

Example  5  Using SAP DDIC domains: Read the text of a functional location

The external key of a functional location may differ from the internal key, since SAP usees a conversion exit.

// clear result table
Clear text[r]

// Read the text from table ILOTX
Call "/guixt/dbselect" cache="session" in.TABLE="IFLOTX" _
  in.CONDITION="SPRAS = '&V[_language]' AND TPLNR = @TPLNR" _
  in.DOMNAME1="TPLNR" in.DOMvalue1="&V[iw_tplnr]" in.FIELDS="PLTXT" _
  table.VALUES="r"

// Test output
Message "&text[r]"

Example 6  Various options in one call

For a given material number the 10 customers with highest sales anin a particular year (here:2017) are determined. We join the tables S001 (sales data), MAKT (material texts ) and KNA1 (customers).

// Use a fixed material number for testing
Set V[mymaterial] "M-01"

 // clear result table
Clear text[r]

Call "/guixt/dbselect" _
  in.table="S001 join KNA1 on KNA1~KUNNR = S001~KUNNR join MAKT on MAKT~MATNR = S001~MATNR" _
  in.fields="KNA1~KUNNR,KNA1~NAME1,MAKT~MATNR,MAKT~MAKTX,S001~STWAE,sum( S001~UMNETWR ) as P1"_    
  in.groupby="KNA1~KUNNR,KNA1~NAME1,MAKT~MATNR,MAKT~MAKTX,S001~STWAE" _
  in.orderBy="P1 descending" _
  in.condition="S001~MATNR = @MATNR and S001~SPMON like '2017%' and MAKT~SPRAS = 'E'" _
  in.domname1="MATNR" in.domvalue1="&V[mymaterial]" _
  in.maxcount="10" _
  table.values="r"

// Test output
Message "&text[r]"

Components GuiXT + InputAssistant