Article
Eduard Lebedyuk · Feb 5, 2016
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 queriesSimply 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: %SQLQueryAll arguments of your SQL query must be listed in the list of argumentsQuery type: SELECTUse 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 resultsAdd 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_QueryNameCaché Studio provides the built-in wizard for creating class queriesSample 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
(ROWSPEC="ID:%Integer,Name:%String,DOB:%Date,SSN:%String",
CONTAINID = 1, SELECTMODE = "RUNTIME",
COMPILEMODE = "IMMEDIATE") [ SqlName = SP_Sample_By_Name, SqlProc ]
{
SELECT ID, Name, DOB, SSN
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}
}
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 useIf data is stored in globals (without classes)If you need to escalate rights in order to access dataIf you need to call an external API in order to access dataIf you need to gain access to the file system in order to access dataYou 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 queryqueryNameFetch — obtains the next row result of a queryqueryNameClose — destructs a query
Now let's analyze these methods in more detail.
The queryName method
The queryName method represents information about a query.
Type: %QueryLeave body blankDefine 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 methodThe queryNameExecute method fully initializes a query. The signature of this method is as follows:
ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status
where:
qHandle is used for communication with other methods of the query implementationThis method must set qHandle into the state which will then be passed to the queryNameFetch methodqHandle can be set to OREF, variable or a multi-dimensional variableargs 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 methodThe 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 ]
where:
qHandle is used for communication with other methods of the query implementationWhen 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 processedAtEnd 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:
Check whether we've reached the end of dataIf there is still some data left: Create a new %List and assign a value to the Row variableOtherwise, set AtEnd to 1Prepare qHandle for the next result fetchReturn 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 methodThe 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 ]
where:
Caché executes this method after the final call to the queryNameFetch methodIn other words, this is a query destructorTherefore you should dispose all SQL cursors, queries and local variables in its implementationThe 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 globalStatic SQLDynamic SQL
Iteration through a globalThe 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 codeThe results must be/can be sorted by the global's subscript
Static SQLThe approach is based on cursors and static SQL. This is used for:
Making the int code more readableMaking the work with cursors easierSpeeding up the compilation process (static SQL is included into the class query and is therefore compiled only once).
Note:
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
{
&sql(DECLARE C CURSOR FOR
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 SQLThe 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 speedROWSPEC is unnecessary, since all metadata is obtained from the class definitionCompliance with the object-oriented design principles
To create query from the subclass of %SQL.CustomResultSet class, make sure to perform the following steps:
Define the properties corresponding to the resulting fieldsDefine the private properties where the query context will be storedOverride the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as wellOverride 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 remainingOverride 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.
Summary
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.
References
Class queries
Iteration through a global
Static SQL
Dynamic SQL
%SQL.CustomResultSet
The Utils.CustomQuery class
The Utils.CustomQueryRS class
The author would like to thank Alexander Koblov for his assistance in writing this article. 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:
ID:Name:DOB:SSN: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!
Quick followup note - so folks don't freak out about the output from the query example - the names and birthdates and SSN's displayed in the example were from a SAMPLES testing environment where data was auto-generated at random - so the data is fake and not for real people :) Thanks!
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:
Name
Adams,Chris Z.
Adams,Danielle P
To achieve it I created %ZLANGC00 mac routine with the following code:
; %ZLANGC00
; custom commands for ObjectScript
; http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSTU_customize
Quit
/// Execute Query and display the results
/// Call like this:
/// zsql "SELECT TOP 10 Name FROM Sample.Person"
ZSQL(Query)
#Dim ResultSet As %SQL.StatementResult
Set ResultSet = ##class(%SQL.Statement).%ExecDirect(, Query)
Do ResultSet.%Display()
Quit
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.SAMPLES>>?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 entermultiline statement mode, enter the each statement line andenter GO to exit multiline statement mode and execute the statement.etc...This uses %SQL.Statement to do all the work and has a number of options including saving of queries, etc. I would also recommend to try Caché Web Terminal SQL mode:This mode appeared with 4.0 version. for me personally, the greatest benefit of custom SQL queries is that you don't need any (persistent) data, you can just generate it on the fly, either computing them or bringing from any arbitrary external system. Dan Fantastic post @Eduard.Lebedyuk
Alternative approach: %SQL.CustomResultSet is icing on the cake which is not covered by Intersystems documentation. It really should be. This is exactly what I needed for my current project. Thanks. 💡 The article is considered as InterSystems Data Platform Best Practice. For 2017.1CE and later as well as all IRIS versions, %SQL.CustomResultSet should not be used. Instead, use %SQL.CustomQuery. There are several good reasons for this. There is good class documentation available. I am happy to post examples if anyone is interested. Hi @Daniel.Pasco
It would be great if you can share some examples with us.
Best Regards,Henrique 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.
Define a new class that extends %SQL.CustomQuery;
Override the SQLNAME parameter, assign a valid SQL identifier that is to be the name of the TVF;
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;
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";
Override %OpenCursor. Add parameters to this method override that correspond to the input parameters that will be passed when instantiating the custom query;
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;
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('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7','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 = data
} else {
set ..data = [].%FromJSON(data)
}
set ..iterator = ..data.%GetIterator()
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 ..name = value.%Get(0)
set ..population = value.%Get(1)
set ..stateCode = value.%Get(3)
set response = 1
} else {
set ..atEnd = 1
set ..iterator = ""
}
} else {
set ..name = ""
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 ..data = ""
}
}
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.
Dan 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(,"https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*&DATE_=7","Default")
USER>write result.%Next()
1
USER>write result.name
Alabama
USER>write result.population
4849377
USER>while result.%Next() { write !,result.name,": ",result.population }
Alaska: 736732
Arizona: 6731484
Arkansas: 2966369
California: 38802500
Another unknown feature that isn't mentioned anywhere else. I am a fan Robert...but...
https://community.intersystems.com/post/new-video-sql-%E2%80%93-things-you-should-know
Somewhere around the 36 minute mark of this video. Buried perhaps. Still, %SQL.CustomQuery has a lot of interesting capabilities. Incredible! I really missed something important!@Daniel.Pasco Thanks for the pointer. I never stop learning. This is a great feature Dan!
I'm using it to write SQL queries to Pandas dataframes in Python (code). 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.
