$LIST (SQL)

A list function that returns elements in a list.

Synopsis

$LIST(list[,position[,end]])

Description

$LIST returns elements from a list. The elements returned depend on the arguments used.

$LIST(list) returns the first element in the list as a text string.
$LIST(list,position) returns the element indicated by the specified position as a text string, where a position value of 1 represents the first element in the list. The position argument must evaluate to an integer.
$LIST(list,position,end) returns a “sublist” (an encoded list string) containing the elements of the list from the specified start position through the specified end position.

This function returns data of type VARCHAR.

Arguments

list

An encoded character string containing one or more elements. You can create a list using the SQL $LISTBUILD function or the ObjectScript $LISTBUILD function. You can convert a delimited string into a list using the SQL $LISTFROMSTRING function or the ObjectScript $LISTFROMSTRING function. You can extract a list from an existing list using the SQL $LIST function or the ObjectScript $LIST function.

position

The position of a list element to return. List elements are counted from 1. If position is omitted, the first element is returned. If the value of position is 0 or greater than the number of elements in the list, InterSystems SQL does not return a value. If the value of position is negative one (–1), $LIST returns the final element in the list. For example, the following command will return “Green”:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),-1)

If the end argument is specified, position specifies the first element in a range of elements. Even when only one element is returned (when position and end are the same number) this element is returned as an encoded list string. Thus, $LIST(x,2) (which returns the element as an ordinary string) is not identical to $LIST(x,2,2) (which returns the element as an encoded list string).

end

The position of the last element in a range of elements. You must specify position to specify end. When end is specified, the value returned is an encoded list string. Because of this encoding, such strings should only be processed by other $LIST functions.

If the value of end is:

greater than position, an encoded string containing a list of elements is returned.
equal to position, an encoded string containing the one element is returned.
less than position, no value is returned.
greater than the number of elements in list, it is equivalent to specifying the final element in the list.
negative one (–1), it is equivalent to specifying the final element in the list.

When specifying end, you can specify a position value of zero (0). In this case, 0 is equivalent to 1.

Working with Lists

A table can contain one or more List fields. Because a list is an encoded string, these fields can be defined as data type %List (%Library.List) or data type VARCHAR. A field of data type %List can be identified as CType (client data type) = 6.

The data type does not restrict the field’s permitted values upon insert or update. The user must therefore make sure that all data values in a List field are List encoded character strings. If the SQL $LIST function encounters a non-encoded string data value, the SELECT operation fails with an SQLCODE -400 alongside a %msg such as the following: Unexpected error occurred: <LIST>%0AmBuncommitted+1^%sqlcq.USER.cls61.1.

A list can be supplied to the SQL $LIST function by using a host variable, or by specifying a $LISTBUILD within SQL. Both are shown in the following Embedded SQL example:

   SET mylist=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LIST(:mylist,2),$LIST($LISTBUILD('Red','Blue','Green'),3)
   INTO :a,:b )
   IF SQLCODE'=0 {
     WRITE "Error code ",SQLCODE,! }
   ELSE {
     WRITE !,"The host varable list element is ",a,!
     WRITE !,"The SQL $LISTBUILD list element is ",b,! }

A list can be extracted from another list by using the $LIST function:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),2,3)

In the following Embedded SQL example, sublist is not a valid list argument, because it is a single element returned as an ordinary string, not an encoded list string. Only the three-argument form of $LIST returns an encoded list string. In this case, an SQLCODE -400 fatal error is generated:

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LIST(:a,2)
   INTO :sublist )
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
   &sql(SELECT $LIST(:sublist,1)
   INTO :c )
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"The sublist is"
     ZZDUMP c   ; Variable not set 
     }
   }

Examples

In the following example, both commands return “Red”, the first element in the list. The first returns the first element by default, and the second returns the first element because the position argument is set to 1:

SELECT $LIST($LISTBUILD("Red","Blue","Green"))
SELECT $LIST($LISTBUILD("Red","Blue","Green"),1)

The following example returns “Blue”, the second element in the list:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),2)

The following Embedded SQL example returns “Red Blue”, a two-element list string beginning with the first element and ending with the second element in the list. ZZDUMP is used rather than WRITE, because a list string contains special (non-printing) encoding characters:

   SET a=$LISTBUILD("Red","Blue","Green")
   &sql(SELECT $LIST(:a,1,2)
   INTO :b )
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"The encoded sublist is"
     ZZDUMP b   ; Prints "Red Blue " 
     }

The following example returns the last element in a list of unknown length. The first SELECT statement returns the last element as an ordinary string, whereas the second statement returns it as an encoded list string:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),-1)
SELECT $LIST($LISTBUILD("Red","Blue","Green"),$LISTLENGTH($LISTBUILD("Red","Blue","Green")),-1)

Notes

Invalid Argument Values

If the expression in the list argument does not evaluate to a valid list, an SQLCODE -400 fatal error is generated:

SELECT $LIST("the quick brown fox",1)

If the value of the position argument or the end argument is less than -1, an SQLCODE -400 fatal error is generated:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),-2,3)

If the value of the position argument refers to a nonexistent list member and no end argument is used, an SQLCODE -400 fatal error is generated:

SELECT $LIST($LISTBUILD("Red","Blue","Green"),7)

However, if an end argument is used, no error occurs, and the null string is returned.

SELECT $LIST($LISTBUILD("Red","Blue","Green"),7,-1)

If the value of the position argument identifies an element with an undefined value, an SQLCODE –400 fatal error is generated:

SELECT $LIST($LISTBUILD("Red",,"Green"),2)

Two-Argument and Three-Argument $LIST

$LIST(list,1) is not equivalent to $LIST(list,1,1) because the former returns a string, while the latter returns a single-element list string. If there are no elements to return, the two-argument form does not return a value; the three-argument form returns a null string.

Unicode

If one Unicode character appears in a list element, that entire list element is represented as Unicode (wide) characters. Other elements in the list are not affected.

The following Embedded SQL example shows two lists. The a list consists of two elements which contain only ASCII characters. The b list consists of two elements: the first element contains a Unicode character ($CHAR(960) = the pi symbol); the second element contains only ASCII characters.

   SET a=$LISTBUILD("ABC"_$CHAR(68),"XYZ")
   SET b=$LISTBUILD("ABC"_$CHAR(960),"XYZ")
   &sql(SELECT $LIST(:a,1),$LIST(:a,2),$LIST(:b,1),$LIST(:b,2)
   INTO :a1,:a2,:b1,:b2 )
   IF SQLCODE'=0 {
     WRITE !,"Error code ",SQLCODE }
   ELSE {
     WRITE !,"The ASCII list a elements: "
     ZZDUMP a1
     ZZDUMP a2
     WRITE !,"The Unicode list b elements: "
     ZZDUMP b1
     ZZDUMP b2 }

Note that InterSystems IRIS encodes the first element of b entirely in wide Unicode characters. The second element of b contains no Unicode characters, and thus InterSystems IRIS encodes it using narrow ASCII characters.