%DLIST (SQL)
Synopsis
%DLIST([ALL | DISTINCT [BY(col-list)]]
string-expr
[%FOREACH(col-list)] [%AFTERHAVING])
Description
The %DLIST aggregate function returns an ObjectScript %List structure containing the values in the specified column as list elements.
A simple %DLIST (or %DLIST ALL) returns InterSystems IRIS 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 an InterSystems IRIS 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 InterSystems IRIS 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.
Arguments
ALL
An optional argument specifying that %DLIST returns a list of all values for string-expr. This is the default if no keyword is specified.
DISTINCT
An optional 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)
An optional column name or a comma-separated list of column names. See SELECT for further information on %FOREACH.
%AFTERHAVING
An optional argument that applies the condition found in the HAVING clause.
Examples
The following example returns an InterSystems IRIS list of all of the values listed in the Home_State column of the Sample.Person table that start with the letter “A”:
SELECT %DLIST(Home_State)
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
Note that this InterSystems IRIS list contains elements with duplicate values.
The following example returns an InterSystems IRIS 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”:
SELECT %DLIST(DISTINCT Home_State)
FROM Sample.Person
WHERE Home_State %STARTSWITH 'A'
The following example creates an InterSystems IRIS 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 the following 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:
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 $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}
set rset = tStatement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
while rset.%Next()
{
write $LISTTOSTRING(rset.colors,"||"),!
}
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
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
-
Aggregate Functions overview
-
$LIST function
-
JSON_ARRAYAGG aggregate function
-
LIST aggregate function
-
XMLAGG aggregate function