Function With the CallSQL-statement you can use SQL functions for GuiXT table variables.
Examples

More examples and explanations
CallSQL "DELETE FROM V[items] WHERE remove = 'X' "
In the table variable V[items], all rows where column "remove" contains "X" are deleted.

CallSQL customerinfo =   _
  "SELECT c.account, c.name, c.city, co.countryname " && _ 
  "FROM V[customers] AS c LEFT OUTER JOIN V[countries] AS co " && _ 
   "ON c.country = co.country" 

The specified columns from V[customers] are copied into the table variable V[customerinfo], and the matching country name "countryname" from the table variable V[countries] is added as well.
Format CallSQL "SQL statement"
CallSQL varname = "SQL statement"

Both notations execute the SQL statement, where GuiXT table variables are referenced as table names using the notation V[varname]. If a result variable is specified, the result of the statement is assigned to the result variable, which is usually also a table variable. If a structure variable is specified as the result variable, only the first row of the result set is read and assigned to the structure variable. A simple variable (not a structure or table) can only be specified in conjunction with "SELECT count(*) FROM ...".

GuiXT uses SQLite as its SQL engine, whose syntax largely conforms to the SQL standard. Please note that for your own column names, such as "order", names reserved in SQL must be enclosed in square brackets. Example:

CallSQL selorders = "SELECT [order] FROM V[orders] WHERE selected = 'X' "

For SELECT, the result set is the new table as defined by SQL; for UPDATE and DELETE, it is the changed or deleted rows; and statements such as "CREATE INDEX" have no result set.

With Q[ok] you can check whether the SQL statement was executed. Further information can then be found in the GuiXT system fields (see below).
Options mode="createFromResult"
The result variable is automatically created with CreateTable using the column names of the SQL statement's result set.  A separate CreateTable beforehand is therefore not required.

mode=
"appendRows"
The rows of the result set are appended to the specified table variable.

mode="updateCells"
The values of the result set replace the corresponding cell contents of the table variable. This lets you, for example, fill a single column of a table variable in a targeted way.

mode=""
The default mode, if not specified or left empty, is filling the result variable as described above. Here, the column names of the SQL result table are matched to the columns of the result variable by name. All other columns remain empty.
Special SQL Functions All SQL functions available in SQLite, such as upper(), concat(), replace, ... can be used via CallSQL . A list of available functions can be found at
https://sqlite.org/lang_corefunc.html.

In addition, the following special GuiXT functions are available, which can be used in the SQL statement in the same way as the standard SQLite functions. A combination of SQLite functions with GuiXT functions is possible anywhere in the SQL statement.

guixtValue(varname, format)
The function has two parameters: the variable name and the desired format. The GuiXT variable with the specified name is evaluated and, if specified, converted into the SQL format:
  • "String" or no specification
    No conversion

  • "ISODate"
    Any returnd string in one of the date formats supported by SAP dd.MM.yyyy,  MM/dd/yyyy, MM-dd-yyyy, yyyy.MM.dd, yyyy/MM/dd is converted into the ISO format yyyy-MM-dd, which is also expected by SQLite in special date functions.
    The ISO date is sortable, so that e.g.
    CallSQL "SELECT ... ORDER BY GuiXTConvert(audat, "ISODate")"  
    produces a correct sort order, even if the column "audat" contains the date in the form "dd.MM.yyyy".
    If the date is invalid, the SQL value is set to NULL.

  • "Real"
    The returned string is converted into an SQLite "Real" number, taking into account the decimal separator (period or comma) currently set in GuiXT.
    If the specification is invalid, the SQL value is set to NULL.

  • "Integer"
    The returned string is converted into an SQLite "Integer" number, taking into account the decimal separator (period or comma) currently set in GuiXT, using commercial rounding.
    If the specification is invalid, the SQL value is set to NULL.

  • "toLower"
    The returned string is converted to lowercase.

  • "toUpper"
    The returned string is converted to uppercase.

For dynamic values in a WHERE condition, it is recommended not to use '&V[...]', but rather guixtValue(). Example:

... WHERE company = '&V[companyname]'

When directly inserting the variable value via '&V[...]', the character ' is not doubled within the string, which can result in an invalid SQL statement. Instead, use:

... WHERE company = guixtValue('companyname')

This way, the variable value is not inserted as a literal, but is only passed to SQLite when the SQL statement is interpreted.

guixtConvert(value, format)
The function has two parameters: the value to be converted and the desired format. The following formats are possible:
  • "ISODate"
    Any date in one of the formats supported by SAP dd.MM.yyyy,  MM/dd/yyyy, MM-dd-yyyy, yyyy.MM.dd, yyyy/MM/dd is converted into the ISO format yyyy-MM-dd, which is also expected by SQLite in special date functions. It is sortable, so that e.g. If the specification is invalid, the SQL value is set to NULL.
    CallSQL "SELECT ... ORDER BY GuiXTConvert(audat, "ISODate")"
    produces a correct sort order, even if the column "audat" contains the date in the form "dd.MM.yyyy".
    If the date is invalid, the SQL value is set to NULL.

  • "Real"
    The specified value is converted into an SQLite "Real" number, taking into account the decimal separator (period or comma) currently set in GuiXT. This allows you to perform numeric comparisons or use aggregate functions such as SUM(), etc.
    If the specification is invalid, the SQL value is set to NULL.

  • "Integer"
    The specified value is converted into an SQLite "Integer" number, taking into account the decimal separator (period or comma) currently set in GuiXT, using commercial rounding. This allows you to perform numeric comparisons or use aggregate functions such as SUM(), etc.
    If the specification is invalid, the SQL value is set to NULL.

  • "toLower"
    The specified value is converted to a lowercase string.

  • "toUpper"
    The specified value is converted to an uppercase string.

GuiXTProcess(proc, value1, value2, value3)
The function can be called with two, three, or four parameters. The first parameter is the name of a "process" script; this is called by GuiXT in the same way as with the "process" keyword. 1-3 values are passed as parameters, and the return value of the process function is returned to SQL as the function value.  Example:

"SELECT vbeln,posnr,matnr..."

Result:
3974  000010  TG12
1804  000010  MZ-FG-C990
1804  000020  EWMS4-20             
"SELECT vbeln,GuiXTProcess('no_leading_zeros', posnr) as posnr,matnr..."

Result:
3974  10  TG12
1804  10  MZ-FG-C990
1804  20  EWMS4-20             
For each value in the "posnr" column, the GuiXT script "process_no_leading_zeros.txt" is called during SQL processing:
GuiXT
Parameter str

// remove leading zeros
Set V[str2] "&V[str]" regex="[^0].*"

// return result
Return  "&V[str2]"
System fields V[_sqlrowcount]
Number of rows returned

V[_sqlchangecount]

Number of rows changed

V[_sqlerrortext]

Error text in case of an error, e.g. incorrect SQL syntax. The text is also displayed in the Script Editor.

V[_sqlstatement]

The SQL statement (only in case of an error).
Tips & Tricks
Component GuiXT + Controls