$LISTSAME (SQL)

A list function that compares two lists and returns a boolean value.

Synopsis

$LISTSAME(list1,list2)

Description

$LISTSAME compares the contents of two lists and returns 1 if the lists are the same. If the lists are not the same, $LISTSAME returns 0. $LISTSAME compares the two lists element-by-element. For two lists to be the same, they must contain the same number of elements and each element in list1 must match the corresponding element in list2.

$LISTSAME compares list elements using their string representations. $LISTSAME comparisons are case-sensitive. $LISTSAME compares the two lists element-by-element in left-to-right order. Therefore $LISTSAME returns a value of 0 when it encounters the first non-matching pair of list elements; it does not check subsequent items to determine if they are valid list elements.

This function returns data of type SMALLINT.

Arguments

list (list1 and list2)

A list is 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.

The following are examples of valid lists:

$LISTBUILD('a','b','c'): a three-element list.
$LISTBUILD('a','','c'): a three-element list, the second element of which has a null string value.
$LISTBUILD('a',,'c') or $LISTBUILD('a',NULL,'c'): a three-element list, the second element of which has no value.
$LISTBUILD(NULL,NULL) or $LISTBUILD(,NULL): a two-element list, the elements of which have no values.
$LISTBUILD(NULL) or $LISTBUILD(): a one-element list, the element has no value.

If a list argument is NULL, $LISTSAME returns NULL. If a list argument is not a valid list (and is not NULL), InterSystems SQL generates an SQLCODE -400 fatal error.

Examples

The following embedded SQL example uses $LISTSAME to compare two list arguments:

  SET a=$LISTBUILD("Red",,"Yellow","Green","","Violet")
  SET b=$LISTBUILD("Red",,"Yellow","Green","","Violet")
 &sql(SELECT $LISTSAME(:a,:b)
      INTO :c )
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSEIF c=1 { WRITE "lists a and b are the same",! }
  ELSE { WRITE "lists a and b are not the same",! }

The following SQL example compares lists with NULL, absent, or empty string elements:

  SELECT $LISTSAME($LISTBUILD('Red',NULL,'Blue'),$LISTBUILD('Red',,'Blue')) AS NullAbsent,
         $LISTSAME($LISTBUILD('Red',NULL,'Blue'),$LISTBUILD('Red','','Blue')) AS NullEmpty,
         $LISTSAME($LISTBUILD('Red',,'Blue'),$LISTBUILD('Red','','Blue')) AS AbsentEmpty

$LISTSAME comparison is not the same equivalence test as the one used by the ObjectScript equal sign. An equal sign compares the two lists as encoded strings (character-by-character); $LISTSAME compares the two lists element-by-element. This distinction is easily seen when comparing a number and a numeric string, as in the following example:

   SET a = $LISTBUILD("365")
   SET b = $LISTBUILD(365)
   IF a=b 
     { WRITE "Equal sign: lists a and b are the same",! }
   ELSE { WRITE "Equal sign: lists a and b are not the same",! }
  &sql(SELECT $LISTSAME(:a,:b)
      INTO :c )
  IF SQLCODE'=0 {
    WRITE !,"Error code ",SQLCODE }
  ELSEIF c=1 { WRITE "$LISTSAME: lists a and b are the same",! }
  ELSE { WRITE "$LISTSAME: lists a and b are not the same",! }

The following SQL example compares lists containing numbers and numeric strings in canonical and non-canonical forms. When comparing a numeric list element and a string list element, the string list element must represent the numeric in canonical form; this is because InterSystems IRIS always reduces numbers to canonical form before performing a comparison. In the following example, $LISTSAME compares a string and a number. The first three $LISTSAME functions return 1 (identical); the fourth $LISTSAME function returns 0 (not identical) because the string representation is not in canonical form:

  SELECT $LISTSAME($LISTBUILD('365'),$LISTBUILD(365)),
         $LISTSAME($LISTBUILD('365'),$LISTBUILD(365.0)),
         $LISTSAME($LISTBUILD('365.5'),$LISTBUILD(365.5)),
         $LISTSAME($LISTBUILD('365.0'),$LISTBUILD(365.0))