$LISTBUILD
Synopsis
$LISTBUILD(element,...)
Argument
| Argument | Description |
|---|---|
| element | Any expression, or comma-separated list of expressions. |
Description
$LISTBUILD takes one or more expressions and returns a list with one element for each expression.
The following functions can be used to create a list:
$LISTBUILD, which creates a list from multiple strings, one string per element.
$LISTFROMSTRING, which creates a list from a single string containing multiple delimited elements.
$LIST, which extracts a sublist from an existing list.
$LISTBUILD is used with the other InterSystems SQL list functions: $LIST, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, and $LISTTOSTRING.
$LISTBUILD and the other $LIST functions use an optimized binary representation to store data elements. For this reason, equivalency tests may not work as expected with some $LIST data. Data that might, in other contexts, be considered equivalent, may have a different internal representation. For example, $LISTBUILD(1) is not equal to $LISTBUILD('1').
For the same reason, a list string value returned by $LISTBUILD should not be used in character search and parse functions that use a delimiter character, such as $PIECE and the two-argument form of $LENGTH. Elements in a list created by $LISTBUILD are not marked by a character delimiter, and thus can contain any character.
Examples
The following Embedded SQL example takes three strings and produces a three-element list:
SET x="Red"
SET y="White"
SET z="Blue"
&sql(SELECT $LISTBUILD(:x,:y,:z)
INTO :listout)
IF SQLCODE=0 {WRITE listout," length ",$LISTLENGTH(listout)}
ELSE {WRITE "Error code:",SQLCODE}
Notes
Omitting Arguments
Omitting an element expression yields an element whose value is NULL. For example, the following Embedded SQL contains two $LISTBUILD statements that both produce a three-element list whose second element has an undefined (NULL) value:
SET x="Red"
SET y="White"
SET z="Blue"
&sql(SELECT $LISTBUILD(:x,,:z),
$LISTBUILD(:x,'',:z)
INTO :list1,list2)
IF SQLCODE=0 {WRITE list1," length ",$LISTLENGTH(list1),!
WRITE list2," length ",$LISTLENGTH(list2)}
ELSE {WRITE "Error code:",SQLCODE}
Additionally, if a $LISTBUILD expression is undefined, the corresponding list element has an undefined value. The following Embedded SQL example produces a two-element list whose first element is "Red" and whose second element has an undefined value:
&sql(SELECT $LISTBUILD('Red',:z)
INTO :list1)
IF SQLCODE=0 {WRITE list1," length ",$LISTLENGTH(list1)}
ELSE {WRITE "Error code:",SQLCODE}
The following Embedded SQL example produces a two-element list. The trailing comma indicates the second element has an undefined value:
&sql(SELECT $LISTBUILD('Red',)
INTO :list1)
IF SQLCODE=0 {WRITE list1," length ",$LISTLENGTH(list1)}
ELSE {WRITE "Error code:",SQLCODE}
Providing No Arguments
Invoking the $LISTBUILD function with no arguments returns a list with one element whose data value is undefined. This is not the same as NULL. The following are valid $LISTBUILD statements that create “empty” lists:
&sql(SELECT $LISTBUILD(),
$LISTBUILD(NULL)
INTO :list1,:list2)
IF SQLCODE=0 {
ZZDUMP list1
WRITE !,"length ",$LISTLENGTH(list1),!
ZZDUMP list2
WRITE !,"length ",$LISTLENGTH(list2),!
}
ELSE {WRITE "Error code:",SQLCODE}
The following are valid $LISTBUILD statements that create a list element that contains an empty string:
&sql(SELECT $LISTBUILD(''),
$LISTBUILD(CHAR(0))
INTO :list1,:list2)
IF SQLCODE=0 {
ZZDUMP list1
WRITE !,"length ",$LISTLENGTH(list1),!
ZZDUMP list2
WRITE !,"length ",$LISTLENGTH(list2),!
}
ELSE {WRITE "Error code:",SQLCODE}
Nesting Lists
An element of a list may itself be a list. For example, the following statement produces a three-element list whose third element is the two-element list, "Walnut,Pecan":
SELECT $LISTBUILD('Apple','Pear',$LISTBUILD('Walnut','Pecan'))
Concatenating Lists
The result of concatenating two lists with the SQL Concatenate operator (||) is another list. For example, the following SELECT items produce the same list, "A,B,C":
SELECT $LISTBUILD('A','B','C') AS List,
$LISTBUILD('A','B')||$LISTBUILD('C') AS CatList
In the following example, the first two select items result in the same two-element list; the third select item results in NULL (because concatenating NULL to anything results in NULL); the fourth and fifth select items result in the same three-element list:
SELECT
$LISTBUILD('A','B') AS List,
$LISTBUILD('A','B')||'' AS CatEStr,
$LISTBUILD('A','B')||NULL AS CatNull,
$LISTBUILD('A','B')||$LISTBUILD('') AS CatEList,
$LISTBUILD('A','B')||$LISTBUILD(NULL) AS CatNList
Unicode
If one or more characters in a list element is a wide (Unicode) character, all characters in that element are represented as wide characters. To ensure compatibility across systems, $LISTBUILD always stores these bytes in the same order, regardless of the hardware platform. Wide characters are represented as byte strings. For further details, refer to the ObjectScript $LISTBUILD function in the ObjectScript Reference.
See Also
SQL functions: $LIST, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, $LISTSAME, $LISTTOSTRING, $PIECE
ObjectScript functions: $LIST, $LISTBUILD, $LISTDATA, $LISTFIND, $LISTFROMSTRING, $LISTGET, $LISTLENGTH, $LISTNEXT, $LISTSAME, $LISTTOSTRING, $LISTVALID