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.
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,...
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.
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:
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 1
Example: We read all customers
with city "HEIDELBERG":
GuiXT
// 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
GuiXT
// 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
// 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
GuiXT
// 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.
GuiXT
// 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).
GuiXT
// 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]"