· Feb 5, 2016 11m read

Class Queries in InterSystems IRIS

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!

Basic class queries

Simply put, basic class queries allow you to represent SQL SELECT queries. SQL optimizer and compiler handle them just as they would standard SQL queries, but they are more convenient when it comes to executing them from Caché Object Script context. They are declared as Query items in class definitions (similar to Method or Property) in the following way:

  • Type: %SQLQuery
  • All arguments of your SQL query must be listed in the list of arguments
  • Query type: SELECT
  • Use the colon to access each argument (similar to static SQL)
  • Define the ROWSPEC parameter which contains information about names and data types of the output results along with the order of fields
  • (Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't need to return ID, don't assign any values to CONTAINID
  • (Optional) Define the COMPILEMODE parameter which corresponds to the similar parameter in static SQL and specifies when the SQL expression must be compiled. When this parameter is set to IMMEDIATE (by default), the query will be compiled simultaneously with the class. When this parameter is set to DYNAMIC, the query will be compiled before its first execution (similar to dynamic SQL)
  • (Optional) Define the SELECTMODE parameter which specifies the format of the query results
  • Add the SqlProc property, if you want to call this query as an SQL procedure.
  • Set the SqlName property, if you want to rename the query. The default name of a query in SQL context is as follows: PackageName.ClassName_QueryName
  • Caché Studio provides the built-in wizard for creating class queries

Sample definition of the Sample.Person class with the ByName query which returns all user names that begin with a specified letter

Class Sample.Person Extends %Persistent
Property Name As %String;
Property DOB As %Date;
Property SSN As %String;
Query ByName(name As %String = "") As %SQLQuery
     COMPILEMODE = "IMMEDIATE") [ SqlName = SP_Sample_By_Name, SqlProc ]
FROM Sample.Person

You can call this query from Caché Object Script in the following way: 

Set statement=##class(%SQL.Statement).%New()   
Set status=statement.%PrepareClassQuery("Sample.Person","ByName")   
If $$$ISERR(status) {
    Do $system.OBJ.DisplayError(status) 
Set resultset=statement.%Execute("A")   
While resultset.%Next() {
    Write !, resultset.%Get("Name")   

Alternatively, you can obtain a resultset using the automatically generated method queryNameFunc:

Set resultset = ##class(Sample.Person).ByNameFunc("A")    
While resultset.%Next() {
    Write !, resultset.%Get("Name")   

This query can also be called from SQLcontext in these two ways:

Call Sample.SP_Sample_By_Name('A')
Select * from Sample.SP_Sample_By_Name('A')

This class can be found in the SAMPLES default Caché namespace And that's all about simple queries. Now let's proceed to custom ones.

Custom class queries

Though basic class queries work fine in most cases, sometimes it is necessary to execute full control over the query behavior in applications, e.g.:

  • Sophisticated selection criteria. Since in custom queries you implement a Caché Object Script method that returns the next row on your own, these criteria can be as sophisticated as you require.
  • If data is accessible only via API in a format that you don't want to use
  • If data is stored in globals (without classes)
  • If you need to escalate rights in order to access data
  • If you need to call an external API in order to access data
  • If you need to gain access to the file system in order to access data
  • You need to perform additional operations before running the query (e.g. establish a connection, check permissions, etc.)

So, how do you create custom class queries? First of all, you should define 4 methods which implement the entire workflow for your query, from initialization to destruction:

  • queryName — provides information about a query (similar to basic class queries)
  • queryNameExecute — constructs a query
  • queryNameFetch — obtains the next row result of a query
  • queryNameClose — destructs a query

Now let's analyze these methods in more detail.

The queryName method

The queryName method represents information about a query.

  • Type: %Query
  • Leave body blank
  • Define the ROWSPEC parameter which contains the information about names and data types of the output results along with the field order
  • (Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't return ID, don't assign any value to CONTAINID

For example, let's create the AllRecords query (queryName = AllRecords, and the method is simply called AllRecords) which will be outputting all instances of the new persistent class Utils.CustomQuery, one by one. First, let's create a new persistent class Utils.CustomQuery:

Class Utils.CustomQuery Extends (%Persistent, %Populate){ 
Property Prop1 As %String; 
Property Prop2 As %Integer;

Now let's write the AllRecords query:

Query AllRecords() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllRecords, SqlProc ]

The queryNameExecute method
The queryNameExecute method fully initializes a query. The signature of this method is as follows:

ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status


  • qHandle is used for communication with other methods of the query implementation
  • This method must set qHandle into the state which will then be passed to the queryNameFetch method
  • qHandle can be set to OREF, variable or a multi-dimensional variable
  • args are additional parameters passed to the query. You can add as many args as you need (or don't use them at all)
  • The method must return query initialization status

But let's get back to our example. You are free to iterate through the extent in multiple ways (I will describe the basic working approaches for custom queries below), but as for this example let's iterate through the global using the $Order function. In this case, qHandle will be storing the current ID, and since we don't need any additional arguments, the arg argument is not required. The result looks like this:

ClassMethod AllRecordsExecute(ByRef qHandle As %Binary) As %Status {  
    Set qHandle = ""    Quit $$$OK

The queryNameFetch method
The queryNameFetch method returns a single result in $List form. The signature of this method is as follows:

ClassMethod queryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ]


  • qHandle is used for communication with other methods of the query implementation
  • When the query is executed, qHandle is being assigned values specified by queryNameExecute or by previous call of queryNameFetch.
  • Row will be set either to a value of %List or to an empty string, if all data has been processed
  • AtEnd must be set to 1, once the end of data is reached.
  • The PlaceAfter keyword identifies the method's position in the int code . The "Fetch" method must be positioned after the "Execute" method, but this is important only for static SQL, i.e. cursors inside queries.

In general, the following operations are performed within this method:

  1. Check whether we've reached the end of data
  2. If there is still some data left: Create a new %List and assign a value to the Row variable
  3. Otherwise, set AtEnd to 1
  4. Prepare qHandle for the next result fetch
  5. Return the status

This is how it will look like in our example:

ClassMethod AllRecordsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status {
    #; Iterating through ^Utils.CustomQueryD    
    #; Writing the next id to qHandle and writing the global's value with the new id into val
    Set qHandle = $Order(^Utils.CustomQueryD(qHandle),1,val)
    #; Checking whether there is any more data left
       If qHandle = "" {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK    
    #; If not, create %List
    #; val = $Lb("", Prop1, Prop2) see Storage definition
    #; Row =$lb(Id,Prop1, Prop2)  see ROWSPEC for the AllRecords request
    Set Row = $Lb(qHandle, $Lg(val,2), $Lg(val,3))
    Quit $$$OK 

The queryNameClose method
The queryNameClose method terminates the query, once all the data is obtained. The signature of this method is as follows:

ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ]


  • Caché executes this method after the final call to the queryNameFetch method
  • In other words, this is a query destructor
  • Therefore you should dispose all SQL cursors, queries and local variables in its implementation
  • The methods return the current status

In our example, we have to delete the local variable qHandle:

ClassMethod AllRecordsClose(ByRef qHandle As %Binary) As %Status {
    Kill qHandle
    Quit $$$OK

And here we are! Once you compile the class, you will be able to use the AllRecords query from %SQL.Statement – just as the basic class queries.

Iteration logic approaches for custom queries

So, what approaches can be used for custom queries? In general, there exist 3 basic approaches:

Iteration through a global
The approach is based on using $Order and similar functions for iteration through a global. It can be used in the following cases:

  • Data is stored in globals (without classes)
  • You want to reduce the number of glorefs is the code
  • The results must be/can be sorted by the global's subscript

Static SQL
The approach is based on cursors and static SQL. This is used for:

  • Making the int code more readable
  • Making the work with cursors easier
  • Speeding up the compilation process (static SQL is included into the class query and is therefore compiled only once).


  • Cursors generated from queries of the %SQLQuery type are named automatically, e.g. Q14.
  • All cursors used within a class must have different names.
  • Error messages are related to the internal names of cursors which have an additional characters at the end of their names. For example, an error in cursor Q140 is actually caused by cursor Q14.
  • Use PlaceAfter and make sure that cursors are used in the same int routinewhere they have been declared.
  • INTO must be used in conjunction with FETCH, but not DECLARE.

Example of static SQL for Utils.CustomQuery:

Query AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllStatic, SqlProc ]

ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status
        SELECT Id, Prop1, Prop2
        FROM Utils.CustomQuery
     &sql(OPEN C)
    Quit $$$OK

ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ]
    #; INTO must be with FETCH
    &sql(FETCH C INTO :Id, :Prop1, :Prop2)
    #; Check if we reached end of data
    If (SQLCODE'=0) {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    Set Row = $Lb(Id, Prop1, Prop2)
    Quit $$$OK

ClassMethod AllStaticClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ]
    &sql(CLOSE C)
    Quit $$$OK

Dynamic SQL
The approach is based on other class queries and dynamic SQL. This is reasonable when in addition to an SQL query itself, you also need to perform some additional operations, e.g. execute an SQL query in several namespaces or escalate permissions before running the query.

Example of dynamic SQL for Utils.CustomQuery:

Query AllDynamic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllDynamic, SqlProc ]

ClassMethod AllDynamicExecute(ByRef qHandle As %Binary) As %Status
    Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery")
    Quit $$$OK

ClassMethod AllDynamicFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
    If qHandle.%Next()=0 {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$OK
    Set Row = $Lb(qHandle.%Get("Id"), qHandle.%Get("Prop1"), qHandle.%Get("Prop2"))
    Quit $$$OK

ClassMethod AllDynamicClose(ByRef qHandle As %Binary) As %Status
    Kill qHandle
    Quit $$$OK

Alternative approach: %SQL.CustomResultSet

Alternatively, you can create a query by subclassing from the %SQL.CustomResultSet class. Benefits of this approach are as follows:

  • Slight increase in speed
  • ROWSPEC is unnecessary, since all metadata is obtained from the class definition
  • Compliance with the object-oriented design principles

To create query from the subclass of %SQL.CustomResultSet class, make sure to perform the following steps:

  1. Define the properties corresponding to the resulting fields
  2. Define the private properties where the query context will be stored
  3. Override the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as well
  4. Override the %Next method (similar to queryNameFetch) which obtains the next result. Fill in the properties. The method returns 0 if all the data has been processed and 1 if some data is still remaining
  5. Override the %CloseCursor method (similar to queryNameClose) if necessary

Example of %SQL.CustomResultSet for Utils.CustomQuery:

Class Utils.CustomQueryRS Extends %SQL.CustomResultSet
Property Id As %String;
Property Prop1 As %String;
Property Prop2 As %Integer;
Method %OpenCursor() As %Library.Status
    Set ..Id = ""
    Quit $$$OK

Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ]
    Set sc = $$$OK
    Set ..Id = $Order(^Utils.CustomQueryD(..Id),1,val)
    Quit:..Id="" 0
    Set ..Prop1 = $Lg(val,2)
    Set ..Prop2 = $Lg(val,3)
    Quit $$$OK

You can call it from Caché Object Script code in the following way:

 Set resultset= ##class(Utils.CustomQueryRS).%New()
       While resultset.%Next() {
        Write resultset.Id,!

Another example is available in the SAMPLES namespace – it's the Sample.CustomResultSet class which implements a query for Samples.Person.


Custom queries will help you to separate SQL expressions from Caché Object Script code and implement sophisticated behavior which can be too difficult for pure SQL.


Class queries

Iteration through a global

Static SQL

Dynamic SQL


The Utils.CustomQuery class

The Utils.CustomQueryRS class

The author would like to thank Alexander Koblov for his assistance in writing this article.

Discussion (17)6
Log in or sign up to continue

In regards to the first part of your write-up, as a developer, if you want a quick and easy way to test the query from your terminal window (i.e. Cache Terminal, PowerTerm, Putty, Reflections, etc...), then you can run the query as follows:

Do ##class(%ResultSet).RunQuery({className},{queryName}) - if you have input parameters, then you would pass those values as subscripts 3 - n;

Example of running query with two input parameters:

Do ##class(%ResultSet).RunQuery({className},{queryName},{inputValue1},{inputValue2})

So an example of running the first query in your write-up from a terminal window might be:

Do ##class(%ResultSet).RunQuery("Sample.Person","ByName","A")

The output from the query might be:

53:Adam,Ralph O.:41730:657-43-6149:
33:Adam,Zoe X.:56117:982-36-6928:
80:Ahmed,Edgar Q.:33719:546-61-2688:
110:Alton,Umberto B.:30116:877-79-1057:
146:Avery,Valery P.:39515:310-11-8847:

Hope this Helps and Have a Great Day!!!   Happy Coding/Testing those queries!


I often need to run queries from a terminal, so I extended Caché ObjectScript with zsql command to run queries and display the results. Here's how it works:

 zsql "SELECT TOP 2 Name FROM Sample.Person"

Would output:

Adams,Chris Z.
Adams,Danielle P

To achieve it I created %ZLANGC00 mac routine with the following code:

 ; %ZLANGC00
 ; custom commands for ObjectScript

/// Execute Query and display the results
/// Call like this:
/// zsql "SELECT TOP 10 Name FROM Sample.Person"
  #Dim ResultSet As %SQL.StatementResult
  Set ResultSet = ##class(%SQL.Statement).%ExecDirect(, Query)
  Do ResultSet.%Display()

Save and compile it and then you can execute sql (class queries too) in a terminal with zsql command:

zsql "SELECT * FROM Sample.SP_Sample_By_Name('Z')"

That said I myself prefer executing SQL queries in SMP because you don't need to type them there (drag&drop from the left panel or copy&paste from the code) - it's very convenient.

I typically use the SQL shell in the terminal when doing this. It is very powerful.

SAMPLES>do $system.SQL.Shell()

SQL Command Line Shell

The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.


Interactive SQL Shell
               (c) InterSystems Corporation 1999-2009 All Rights Reserved
To execute an SQL statement, type it in and press ENTER.
To execute a multiline SQL statement, press <enter> to enter
multiline statement mode, enter the each statement line and
enter GO to exit multiline statement mode and execute the statement.


This uses %SQL.Statement to do all the work and has a number of options including saving of queries, etc.

First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement.

The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here.

  1. Define a new class that extends %SQL.CustomQuery;
  2. Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF;
  3. Define properties, in order, that are the columns of each row returned by this query. Let's call these "result columns". Each result column is defined as a non-private property;
  4. Define properties that you will need to maintain the source data, pointers, etc. that you will use to manage the data used to produce rows. These properties are defined as "private";
  5. Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query;
  6. Override %FetchCursor. In this method, check for end of data. If not at the end then populate all of the result properties with data and return 1 (true). Otherwise, clear all result properties and return 0;
  7.  Override %CloseCursor. In this override, release any resources acquired during instantiation and perform any necessary cleanup.

I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument.

The query:

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_query(',POP&for=state:*...','Default') 
ORDER BY population DESC

and the results:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

Using this version of a custom query class:

Class example.custom.JsonQuery Extends %SQL.CustomQuery

Parameter SQLNAME As String = "sample_custom_json_query";

Property data As %Library.DynamicAbstractObject [ Private ];

Property iterator As %Iterator.AbstractIterator [ Private ];

Property atEnd As %Integer [ InitialExpression = 0, Private ];

Property stateCode As %String;

Property name As %String;

Property population As %Integer;

Method %OpenCursor(data As %String(MAXLEN="")) [ Private ]
    try {
        if $isobject(data) {
            set = data
        } else {
            set = [].%FromJSON(data)
        set ..iterator =
        if '..iterator.%GetNext(.key,.value) {
            set ..atEnd = 0
            set ..iterator = ""
    } catch exception {
            // this is just a place holder, this method reports errors by throwing an exception
            // but a catch can allow the user to log errors or perform some self-healing action
        throw exception

Method %FetchCursor() As %Library.Integer
    set response = 0
    if ($isObject(..iterator)) && ('..atEnd) {
        if ..iterator.%GetNext(.key,.value) {
            set = value.%Get(0)
            set ..population = value.%Get(1)
            set ..stateCode = value.%Get(3)
            set response = 1
        } else {
            set ..atEnd = 1
            set ..iterator = ""
    } else {
        set = ""
        set ..population = ""
        set ..stateCode = ""
    return response

Method %CloseCursor() [ PlaceAfter = %Next, Private ]
        // not really necessary as %OnClose will automatically close the cursor during destruction
        // but users can place code here to clean up other resources allocated for this query instance
        // that are external to the query instance. Like a temporary global.
    set ..iterator = ""
	set = ""


and this query

SELECT top 5 stateCode,name,population 
FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]') 
ORDER BY population DESC

produces the same result:

stateCode name population
06 California 38802500
48 Texas 26956958
12 Florida 19893297
36 New York 19746227
17 Illinois 12880580

5 row(s) affected

I am happy to post other examples if you wish.


Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet.

USER>set result = ##class(example.custom.Query).%New(,",POP&for=state:*&DATE_=7","Default")

USER>write result.%Next()
USER>write result.population
USER>while result.%Next() { write !,,": ",result.population }

Alaska: 736732
Arizona: 6731484
Arkansas: 2966369
California: 38802500

Some of the reasons why I focus on utilizing class queries include

  • Studio and other editors are much better at providing coloring/syntax checking vs a strategy of setting a tSQL string as some arbitrary SQL statement
  • As mentioned in the original post it provides the separation that allows for easy re-use and testing.  If I have a class query I can decide to allow it to be reflected as a stored procedure, I can expose it to ZEN reports, ODBC/JDBC, JSON projection, %XML.DataSet, I can use it to satisfy a control in ZEN that allows for binding to a class query,  as well as others.  Basically, it provides for great separation.
  • I also like the idea of considering writing the statement using SQL as %Query.  If for some reason I run into performance issues that I cannot overcome I can change the implementation to %SQLQuery and implement COS code, the calling application would see the improved performance but does not have to understand the internal implementation.  However, I've only done this on extremely rare occasions.
  • I think one of the most important aspects of SQL to always read the query plan, it's there where you can understand what the optimizer is going to do.  I care all about the query plan and almost always validate what it reports.  Having a class query allows for easy Show Plan examination whereas it's generally hard to do if you take the approach of setting a tSQL string(sometimes with concatenation over several lines).

Class Queries are really worth investing in IMHO.