Caché SQL Reference
%DLIST
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

An aggregate function that creates a Caché list of values.
Synopsis
%DLIST([ALL | DISTINCT [BY(col-list)]] string-expr [%FOREACH(col-list)] [%AFTERHAVING])
Arguments
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 list elements are, by default, listed in the RowId sequence. You can use an ORDER BY clause to sequence these elements in ascending collation sequence. This use of ORDER BY is subject to various conditions and restrictions, as described in the With ORDER BY section of the Aggregate Functions overview page.
Related Aggregate Functions
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