Skip to main content

%DLIST

An aggregate function that creates a Caché list of values.

Synopsis

%DLIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])

Arguments

Argument Description
ALL Optional — Specifies that %DLIST returns a list of all values for string-expr. This is the default if no keyword is specified.
DISTINCT Optional — A DISTINCT clause that specifies that %DLIST returns a %List structured list containing only the unique string-expr values. DISTINCT can specify a BY(col-list) subclause, where col-list can be a single field or a comma-separated list of fields.
string-expr An SQL expression that evaluates to a string. Usually the name of a column from the selected table.
%FOREACH(col-list) Optional — A column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
%AFTERHAVING Optional — Applies the condition found in the HAVING clause.

Description

The %DLIST aggregate function returns a Caché %List structure containing the values in the specified column as list elements.

A simple %DLIST (or %DLIST ALL) returns Caché list composed of all the non-NULL values for string-expr in the selected rows. Rows where string-expr is NULL are not included as elements in the list structure.

A %DLIST DISTINCT returns a Caché list composed of all the distinct (unique) non-NULL values for string-expr in the selected rows: %DLIST(DISTINCT col1). NULL is not included as an element in the %List structure. %DLIST(DISTINCT BY(col2) col1) returns a %List of elements including only those col1 field values in records where the col2 values are distinct (unique). Note however that the distinct col2 values may include a single NULL as a distinct value.

For further information about Caché list structures, see $LIST and related functions.

%DLIST and %SelectMode

You can use the %SelectMode property to specify the data display mode returned by %DLIST: 0=Logical (the default), 1=ODBC, 2=Display.

Note that %DLIST in ODBC mode separates column value lists with commas, and $LISTTOSTRING (by default) returns elements within a %List column value separated with commas.

%DLIST and ORDER BY

The %DLIST function combines the values of a table column from multiple rows into %List structured list of values. Because an ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this list. Under certain circumstances, %DLIST results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.

Related Aggregate Functions

  • %DLIST returns a Caché list of values.

  • LIST returns a comma-separated list of values.

  • JSON_ARRAYAGG returns a JSON array of values.

  • XMLAGG returns a concatenated string of values.

Examples

The following Embedded SQL example returns a host variable containing a Caché list of all of the values listed in the Home_State column of the Sample.Person table that start with the letter “A”:

  &sql(SELECT %DLIST(Home_State)
       INTO :statelist
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "The states (as list):",statelist,!
  WRITE "The states (as string):",$LISTTOSTRING(statelist,"^")

Note that this Caché list contains elements with duplicate values.

The following Embedded SQL example returns a host variable containing a Caché list of all of the distinct (unique) values listed in the Home_State column of the Sample.Person table that start with the letter “A”:

  &sql(SELECT %DLIST(DISTINCT Home_State)
       INTO :statelist
       FROM Sample.Person
       WHERE Home_State %STARTSWITH 'A')
  WRITE "The states (as list):",statelist,!
  WRITE "The states (as string):",$LISTTOSTRING(statelist,"^")

The following SQL example creates a Caché list of all of the values found in the Home_City column for each of the states, and a count of these city values by state. Every Home_State row contains a list of all of the Home_City values for that state. These lists may include duplicate city names:

SELECT Home_State,
       %DLIST(Home_City) AS AllCities,
       COUNT(Home_City) AS CityCount
FROM Sample.Person
GROUP BY Home_State

Perhaps more useful would be a list of all of the distinct values found in the Home_City column for each of the states, as shown in the following example:

SELECT Home_State,
       %DLIST(DISTINCT Home_City) AS CitiesList,
       COUNT(DISTINCT Home_City) AS DistinctCities,
       COUNT(Home_City) AS TotalCities
FROM Sample.Person
GROUP BY Home_State

Note that this example returns integer counts of both the distinct city names and the total city names for each state.

The following example returns %List structures of Home_State values that begin with “A”. It returns as %List elements the distinct Home_State values (DISTINCT Home_State); the Home_State values corresponding to distinct Home_City values (DISTINCT BY(Home_City) Home_State), which may possibly including one unique NULL for Home_City; and all Home_State values:

SELECT %DLIST(DISTINCT Home_State) AS DistStates,
       %DLIST(DISTINCT BY(Home_City) Home_State) AS DistCityStates,
       %DLIST(Home_State) AS AllStates
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'   

The following Dynamic SQL example uses the %SelectMode property to specify the ODBC display mode for the %List structure FavoriteColors date field. ODBC mode returns the value for each column as a comma-separated list, and the $LISTTOSTRING function specifies a different delimiter (in this example, ||) to separate the values from the different columns:

  ZNSPACE "SAMPLES"
  SET myquery = "SELECT %DLIST(FavoriteColors) AS colors FROM Sample.Person WHERE Name %STARTSWITH 'A'"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatement.%SelectMode=1
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  WHILE rset.%Next() {
    WRITE $LISTTOSTRING(rset.colors,"||"),!
   }
   WRITE !,"End of data"

The following example uses the %AFTERHAVING keyword. It returns a row for each Home_State that contains at least one Name value that fulfills the HAVING clause condition (a name that begins with “M”). The first %DLIST function returns a list of all of the names for that state. The second %DLIST function returns a list containing only those names that fulfill the HAVING clause condition:

SELECT Home_State,
       %DLIST(Name) AS AllNames,
       %DLIST(Name %AFTERHAVING) AS HaveClauseNames
    FROM Sample.Person
    GROUP BY Home_State
    HAVING Name LIKE 'M%'
    ORDER BY Home_state

See Also

FeedbackOpens in a new tab