XQL syntax and examples

Rules

A script holds one or more statements, separated by semicolons. The last statement needs none. Keywords are not case sensitive. Names of tables, fields, function modules and reports are written as in SAP.

Strings stand in single or double quotes, a quote inside a string is doubled. A pass-through string between square brackets or between pipes goes to SAP unchanged and may span several lines. It carries MDX statements and names with special characters. Comments start with two slashes and run to the end of the line, or stand between /* and */.

Every statement writes its result into a variable. Without INTO the variable is @RETVAL, the table the caller receives. INTO @name stores a result under another name for a later statement. INTO RESULTSET is the same as INTO @RETVAL.

// The company codes in Germany
SELECT BUKRS, BUTXT, ORT01 FROM T001 WHERE LAND1 = 'DE';

/* Kept for a later statement */
SELECT TOP 100 * FROM T001W INTO @plants

Tables

The form of the statement:

SELECT [TOP n [SKIP n]] * | field [AS alias], ...
  FROM [TABLE] name [INTO @variable] [WHERE expression] [WITH-OPTIONS(...)]

TOP limits the result, SKIP leaves out the first rows before that. The field list names columns of the table, AS gives a column another name. A name with spaces stands in square brackets.

SELECT TOP 10 MATNR, MAKTX AS [Short text] FROM MAKT WHERE SPRAS = 'E'

Rows 201 to 300 of the material master:

SELECT TOP 100 SKIP 200 * FROM MARA

The WHERE clause is a boolean expression with AND, OR and parentheses. It compares a field with a value in the internal SAP format, so a date is written as YYYYMMDD. The operators:

=  EQ   equal               LIKE  MP    pattern with % for any characters and _ for one
<> NE   not equal           BETWEEN BT  from one value to another
>  GT   greater             IN (...)    one of the listed values
<  LT   less                IS NULL     empty, also IS NOT NULL
>= GE   greater or equal    NOT         in front of LIKE, BETWEEN and IN
<= LE   less or equal       AND OR ( )  combine conditions

Finished and semi-finished products in pieces, outside the material groups starting with Z, created in 2020:

SELECT MATNR, MTART, MEINS FROM MARA
  WHERE (MTART = 'FERT' OR MTART = 'HALB')
    AND MEINS = 'ST'
    AND MATKL NOT LIKE 'Z%'
    AND ERSDA BETWEEN '20200101' AND '20201231'

SY-DATUM stands for the current date of the SAP system. The sales orders created today:

SELECT VBELN, AUART, VKORG FROM VBAK WHERE ERDAT = SY-DATUM

The standard module for table reads, RFC_READ_TABLE, returns at most 512 characters per row. Wide tables like MARA need the module Z_XTRACT_IS_TABLE from Theobald in the SAP system, named in WITH-OPTIONS. WITH is a synonym for WITH-OPTIONS.

SELECT * FROM MARA WITH-OPTIONS(CUSTOMFUNCTIONNAME = 'Z_XTRACT_IS_TABLE')

Function modules

EXECUTE FUNCTION 'name'
  [EXPORTS parameter = value, ...]
  [IMPORTS parameter INTO @variable | @variable = parameter, ...]
  [TABLES parameter [= value] [INTO @variable], ...]
  [CHANGING parameter [= value] [INTO @variable], ...]

The module must be remote-enabled. The sections are named from the caller’s side. EXPORTS holds the values the statement sends in, IMPORTS the values it takes back, TABLES the table parameters in both directions and CHANGING the parameters that go in and come back. In the SAP function builder the same parameters appear the other way round, as Import and Export. EXPORTING and IMPORTING are synonyms.

All customers whose name starts with Th. The table CUSTOMER_T is the result:

EXECUTE FUNCTION 'SD_RFC_CUSTOMER_GET'
  EXPORTS NAME1 = 'Th*'
  TABLES CUSTOMER_T INTO @RETVAL

Single values land in one row of @RETVAL, in the columns named after the dollar sign:

EXECUTE FUNCTION 'GET_SYSTEM_TIME_REMOTE'
  IMPORTS L_TIME INTO @RETVAL$local, K_TIME INTO @RETVAL$kernel, L_DATE INTO @RETVAL$date

A field of a structure is addressed with a hyphen, one level deep. The order header goes to @header, the components become the result:

EXECUTE FUNCTION 'BAPI_PRODORD_GET_DETAIL'
  EXPORTS NUMBER = '000060003912', ORDER_OBJECTS-HEADER = 'X', ORDER_OBJECTS-COMPONENTS = 'X'
  TABLES HEADER INTO @header, COMPONENT INTO @RETVAL

A table value starts with its column names, then one row follows for each parenthesis. All values are strings:

EXECUTE FUNCTION 'BAPI_USER_GETLIST'
  TABLES
    SELECTION_RANGE = ((PARAMETER, FIELD, SIGN, OPTION, LOW, HIGH),
      ('LOGONDATA', 'USTYP', 'I', 'EQ', 'A', ''),
      ('USERNAME', '', 'I', 'BT', 'A', 'C')),
    USERLIST INTO @RETVAL

IMPORTS @name = parameter keeps a single value. A later statement uses it wherever a value is allowed, in a WHERE clause, in EXPORTS or as a table parameter. The calling application can pass variables in the same way. The sales orders of the current SAP date, read in two steps:

EXECUTE FUNCTION 'GET_SYSTEM_TIME_REMOTE' IMPORTS @today = L_DATE;
SELECT VBELN, AUART FROM VBAK WHERE ERDAT = @today

Reports

EXECUTE REPORT 'name' [INTO @variable] [WHERE criteria] [USING 'variant'] [WITH-OPTIONS(...)]

The report runs in SAP and its list output comes back as a table. This needs the module Z_XTRACT_IS_REMOTE_REPORT from Theobald in the SAP system. The clauses keep the order shown.

With the variant VAR01:

EXECUTE REPORT 'RLT10010' USING 'VAR01'

With a selection parameter instead. Several criteria are separated by commas or AND:

EXECUTE REPORT 'RLT10010' WHERE T1_LGNUM EQ '001'

Ranges as in a select option, with the sign I or E for include and exclude, an operator and one or two values:

EXECUTE REPORT 'RLT10010'
  WHERE T1_LGNUM IN ((I, BT, '001', '010'), (E, EQ, '005'))

CUSTOMFUNCTION names another module, SKIPTOPROWS drops header lines from the list, USEBATCH runs the report as a background job and waits for it:

EXECUTE REPORT 'RQMELL10' USING 'VAR01'
  WITH-OPTIONS(CUSTOMFUNCTION = 'Z_MY_REMOTE_REPORT', SKIPTOPROWS = '3', USEBATCH = 'true')

SAP queries

SELECT [TOP n] fields FROM QUERY 'workspace|user group|query'
  [INTO @variable] [WHERE criteria] [USING 'variant'] [WITH-OPTIONS(CUSTOMFUNCTIONNAME = '...')]

The workspace is G for the global area or S for the standard area. Field names are written as in the query definition, for example LIPS-LFIMG. The criteria address the selection fields of the query, separated by commas or AND, each field once. OR and parentheses are not available. The operators are EQ, NE, GT, LT, GE, LE, MP for a pattern with an asterisk, BT with two values and IN with ranges.

SELECT * FROM QUERY 'G|/SAPQUERY/ME/|MEBESTWERTAN' USING 'PLANT1000'

SELECT TOP 20 * FROM QUERY 'G|/SAPQUERY/ME/|MEBESTWERTAN'
  WHERE S_WAERS EQ 'EUR', S_MATNR MP '100-4*'

SELECT TOP 30 LIPS-LFIMG, LIPS-MATNR, TEXT_LIKP_KUNNR AS CustomerID
  FROM QUERY 'S|ZTHEO02|ZLIKP'
  WHERE SP$00002 BT '0080011000' AND '0080011999'

BW cubes

Two ways lead to a BW cube. BWQUERY takes the variables of a BW query as criteria, in the same form as the report ranges. EXECUTE MDX passes an MDX statement through unchanged, in a pass-through string between pipes.

SELECT * FROM BWQUERY '0D_DECU/VARDEMO01' WHERE SALESORG IN ((I, BT, '1000', '4000'))

EXECUTE MDX |
  SELECT {[Measures].[0D_COST]} ON COLUMNS,
    NON EMPTY [0D_CO_CODE].[LEVEL01].MEMBERS ON ROWS
  FROM [$0D_DECU]
|

Metadata

DESCRIBE returns the structure of an object instead of its data, for example the fields of a table, the parameters of a function module or the variants of a report. CATALOG searches objects by name, LIKE takes an asterisk as a wildcard. Every DESCRIBE accepts INTO.

DESCRIBE TABLE 'MAKT' GET FIELDS
DESCRIBE TABLE CATALOG WHERE TABLENAME LIKE 'MAR*'
DESCRIBE STRUCTURE 'BAPIRET2'

GET EXPORTS lists the parameters a statement sends in, GET IMPORTS the ones it receives, GET TABLES the table parameters and GET TABLES-STRUCTURE the fields of one of them:

DESCRIBE FUNCTION 'BAPI_USER_GETLIST' GET EXPORTS
DESCRIBE FUNCTION 'BAPI_USER_GETLIST' GET TABLES
DESCRIBE FUNCTION 'SD_RFC_CUSTOMER_GET' GET TABLES-STRUCTURE OF 'CUSTOMER_T'
DESCRIBE FUNCTION CATALOG WHERE FUNCTIONNAME LIKE 'BAPI_USER*'

DESCRIBE REPORT 'RLT10010' GET VARIANTS
DESCRIBE REPORT 'RLT10010' GET SELECTION-PARAMETERS
DESCRIBE REPORT CATALOG WHERE REPORTNAME LIKE 'RLT*'

DESCRIBE QUERY 'G|/SAPQUERY/ME/|MEBESTWERTAN' GET FIELDS
DESCRIBE QUERY 'G|/SAPQUERY/ME/|MEBESTWERTAN' GET SELECTION-PARAMETERS
DESCRIBE QUERY USERGROUP WHERE WORKSPACE EQ 'G'
DESCRIBE QUERY CATALOG WHERE WORKSPACE EQ 'G', USERGROUP LIKE '/SAPQUERY/*'

DESCRIBE BWQUERY '0D_DECU' GET DIMENSIONS
DESCRIBE BWQUERY '0D_DECU' GET MEASURES
DESCRIBE BWQUERY '0D_DECU' GET DIMENSIONS-PROPERTIES OF '0D_CO_CODE'
DESCRIBE BWQUERY '0D_DECU/VARDEMO01' GET VARIABLES
DESCRIBE BWQUERY CATALOG WHERE CUBENAME LIKE '0D_DECU'