Purpose

Examples and explanations
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 authorization 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.
 

Notation You can call the function module using the standard Call notation:
 
Example using the standard Call notation
// plant  
Set V[plant] "1710"

// result structure
CreateStructure V[plantdata] name street city

// clear result text variable
Clear V[r]

// execute database Select
Call /guixt/dbselect  _
  in.table="T001W" _
  in.condition="WERKS = '&V[plant]'" _
  in.fields="name1,stras,ort01" _
  table.values="r"

// copy text variable into structure
CopyText fromText="r" toStructure="V[plantdata]"

Instead, we recommend using the OpenCall notation:
Example using the Open Call notation
// plant 
Set V[plant] "1710"

// result table
CreateTable V[plantdata] name street city

// create SQL parameters
Set V[table] "T001W"
Set V[fields] "name1,stras,ort01"
Set V[condition] "WERKS = '&V[plant]' " 

// execute database Select
Call "/guixt/dbselect" _
  export.table="table" _
  export.fields="fields" _
  export.condition="condition" _
  import.values="plantdata"

In the following sections, we use standard notation. You can find detailed examples in OpenCall notation in the Examples and Explanations section.
 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, domvalue2,... 

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

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.Having= Group conditions
in.Distinct=  With "X" the DISTINCT clause of the Select command is used
in.UseGroupSeparator= With value “X,” groups of thousands are separated by a separator in the output of numbers according to user settings.
in.NoDecimals= With value “X”, no decimal places are displayed in the output of numbers.
in.Username= Specifies a user whose formatting parameters (decimal separator and date format) are used for the values. If nothing is specified, the RFC user is used. In most cases, ... in.Username=“&V[user]” is useful, i.e., the currently logged-in user.
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:

GuiXT
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

Further examples and explanations  
We read all customers with city  "HEIDELBERG":

GuiXT
// Clear result table
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]"

 

Components GuiXT + InputAssistant