Skip to main content

This is documentation for Caché and Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

Please read this important announcement about the future of Caché and Ensemble maintenanceOpens in a new tab. Contact your account team to migrate to InterSystems IRIS.

FROM clause

Uses the contents of a select list as query input.

Synopsis

FROM slist

Description

The FROM clause uses the items in the specified active select list as the input set for the query. You can further limit the query results using the WITH clause and other query clauses. You can use the FROM clause with any of the various LIST or SELECT commands; these are listed in the CMQL Commands list.

FROM specifies a numbered select list. You must explicitly specify FROM 0 to specify the default select list (Select List 0). However, if a numbered select list specified in the FROM clause is not active, FROM uses the default select list (Select List 0). If neither the numbered select list specified in FROM nor the default select list is active, the FROM clause is ignored.

Valid slist values are 0 through 10 (inclusive). Specifying a slist value outside of this range generates a [819] error.

Note that an active select list can only be used once.

FROM and REQUIRE.SELECT

The FROM clause and the REQUIRE.SELECT clause perform the same operation. They differ when there is no active select list. If there is no active select list, the FROM clause is ignored and CMQL executes the other clauses of the query. If there is no active select list, REQUIRE.SELECT causes the query to fail with a [7013] error message.

Examples

The following example uses SELECT to populate Select List 3, then uses LIST with a FROM clause to query using the input set in Select List 3:

SELECT VOC WITH @ID > "T" TO 3
LIST VOC WITH F1="K" FROM 3

The SELECT stores 36 items in Select List 3. The LIST lists 19 of those items that pass the WITH clause restriction.

The following example uses SELECT to populate Select List 4 with all items that begin with the letter S, then uses another SELECT with a FROM clause to populate Select List 5 with the subset of those items that only contain alphabetic characters. It then uses LIST to list those items in uppercase and lowercase:

SELECT VOC WHERE @ID LIKE S... TO 4
SELECT VOC WHERE @ID LIKE "0A" FROM 4 TO 5
LIST VOC @ID CONV "MCL" FROM 5

See Also

HEADER clauseNext section
Previous sectionFOOTING clause
FeedbackOpens in a new tab