On this page

LIST

Core command

Assigns a SQL scripting variable that holds a list of values, typed or read from a query, or copies a list.

Syntax

LIST <name> = <value>, <value>, ... | LIST <name> = SELECT ...

Quick start

LIST codes = 'FRA', 'DEU';
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN ${codes};

Sets the list codes, then uses it in an IN condition: the query runs as IN ('FRA', 'DEU').

Arguments

Argument	Required	Values	Default
`<name>`	Yes	Letters, digits and `_`, in any case; not `ENV`, `NULL`, `TRUE` or `FALSE`	None
`<value>, ...`	Yes or a query	1 to 1000 numbers, single-quoted strings, `TRUE`, `FALSE` or `NULL`, separated by commas; `${other}` copies the list `other`	None
`SELECT ...`	Or values	A `SELECT`, `WITH` or `VALUES` query returning one column of texts, numbers or booleans, 1 to 1000 rows	None

Examples

LIST countries = 'FRA', 'DEU', 'ITA';
LIST ids = 2974, 3068;
SELECT CODE, NAME FROM COUNTRY WHERE CODE IN ${countries};
LIST european = SELECT CODE FROM COUNTRY WHERE CONTINENT = 'Europe';
LIST saved_codes = ${countries};
@countries/by-codes.sql codes=${saved_codes};

Details

Values

A list is written as values separated by commas, or comes from a query:

LIST countries = 'FRA', 'DEU'; or LIST ids = 123, 987;: each value a number, a single-quoted string, TRUE, FALSE or NULL. One value makes a list of one. The same values between parentheses, ('FRA', 'DEU'), are accepted too.
LIST codes = SELECT CODE FROM COUNTRY WHERE CONTINENT = 'Europe';: a query starting with SELECT, WITH or VALUES that returns one column. It runs on the current connection; each row gives one value, with the type of the column: a number stays a number, and a text stays a text even when it looks like a number ('123'). One row makes a list of one. No row, several columns, or a column that is not a text, a number or a boolean (a date, for example: convert it in the query) is refused.

A list has 1 to 1000 values, all of one kind: texts, numbers or booleans. NULL values are kept.

Using a list

${name} writes the list as a SQL value list, parentheses included: WHERE CODE IN ${countries} runs as WHERE CODE IN ('FRA', 'DEU'), and WHERE ID IN ${ids} as WHERE ID IN (123, 987). A text is written between single quotes with its quotes doubled ('O''Brien'), a number as it is. Where the reference already sits inside parentheses, as in IN (${countries}), the form of earlier releases, no second pair is added. The list is written for the database the statement runs on, so a list read on one connection can be used on another: a boolean is TRUE / FALSE, or 1 / 0 on Oracle and SQL Server.

A NULL value is written NULL: x IN (1, NULL) never matches a row where x is NULL, and x NOT IN (1, NULL) matches no row at all.

Copying a list

LIST saved = ${codes}; copies the list codes; the copy keeps its values when codes changes. In a Script, this keeps a list parameter after the call ends, since a parameter is bound for the call only. @report.sql codes=${saved} passes the list to a Script, where a -- @param codes LIST OF <type> declaration converts its values.

Names and lifetime

The same as for LET: one namespace for the session, shared by the prompt and every Script; a failed assignment leaves the variable unchanged. A name holds either a single value (LET) or a list (LIST): LIST refuses a name that holds a single value, and LET a name that holds a list. SHOW SCRIPT VARIABLES lists a list with its kind: LIST OF TEXT, LIST OF NUMBER or LIST OF BOOLEAN.

Last modified in release 5.5.5.

Found something wrong or unclear on this page? Tell us, with the page address.