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.
  

Example 1 (analogous to Read an SAP table)

In an input field the user can enter a country code directly or select it from a list via F4. In each case we display the country name:

GuiXTCopy

InputField (1,1) "Country" (1,20) size=2 _
    techName="KNA1-LAND1" name="country" -upperCase

GuiXT Script

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

Example 2 (analogous to Using an outer join)

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)

// 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"

// 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

Example 3 (analogous to Using a CDS view)

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.

GuiXT Script

InputField (1,1) "Invoice date between" (1,24) name="date1" -date
InputField (1,36) "and" (1,42) name="date2" -date

Pushbutton (1,59) "Display invoices" process="read_invoices.txt"

if not V[invoices.rowcount]
    CreateStructure V[invoice] Invoice_Date Invoice_Type Document_ID _
                   Customer_ID Customer_Name Payment_Terms _
                   Payment_Terms_Text Lifecycle_Status_Text Currency Net_Value
   CreateTable V[invoices] No include=V[invoice]
 endif

Table (3,1) (32,151) name="invoices" _
  title="&V[invoices.rowcount invoices" fixedColumns=11

Column "No" size=5 name="No" -readOnly
Column "Date" size=12 name="Invoice_Date" -readOnly
Column "Type" size=4 name="Invoice_Type" -readOnly
Column "DocNumber" size=12 name="Document_ID" -readOnly
Column "Customer" size=10 name="Customer_ID" -readOnly
Column "Name" size=30 name="Customer_Name" -readOnly
Column "Terms" size=6 name="Payment_Terms" -readOnly
Column "Payment Terms" size=20 name="Payment_Terms_Text" -readOnly
Column "Status" size=20 name="Lifecycle_Status_Text" -readOnly
Column "Currency" size=8 name="Currency" -readOnly
Column "Net value" size=14 name="Net_Value" -alignRight -readOnly

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"

// 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

Components InputAssistant