Caché SQL Reference
SELECT
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

Retrieves rows from one or more tables within a database.
Synopsis
[(] SELECT [%NOFPLAN] [%NOLOCK]
    [DISTINCT [BY (item {,item})] | ALL] 
    [TOP {int | ALL}]
    select-item {,select-item}
    [INTO host-variable-list]
    [FROM [optimize-option] table-ref [[AS] t-alias]
      {,table-ref [[AS] t-alias]} ]
    [WHERE condition-expression]
    [GROUP BY scalar-expression]
    [HAVING condition-expression]
    [ORDER BY item-order-list [ASC | DESC] ]
[)]

select-item ::= 
  [t-alias.]*   |
  [t-alias.]scalar-expression [[AS] c-alias]
    {,[t-alias.]scalar-expression [[AS] c-alias]}
Arguments
%NOFPLAN Optional — The %NOFPLAN keyword specifies that Caché will ignore the frozen plan (if any) for this query and generate a new query plan. The frozen plan is retained, but not used. For further details, refer to Frozen Plans in Caché SQL Optimization Guide.
%NOLOCK Optional — The %NOLOCK keyword specifies that Caché will perform no locking on any of the specified tables. If you specify this keyword, the query retrieves data in READ UNCOMMITTED mode, regardless of current transaction’s isolation mode. For further details, refer to Transaction Processing in the “Modifying the Database” chapter of Using Caché SQL.
DISTINCT
DISTINCT BY (item)
ALL
Optional — The DISTINCT clause specifies that each row returned must contain a unique value for the specified field or combination of fields. A DISTINCT keyword specifies that the select-item value(s) must be unique. A DISTINCT BY keyword clause specifies that item value(s) must be unique. An item (or a comma-separated list of items) is enclosed in parentheses. Commonly, an item is the name of a column. It may or may not also be listed as a select-item.
Optional — The ALL keyword specifies that all rows that meet the SELECT criteria be returned. This is the default for Caché SQL. The ALL keyword performs no operation; it is provided for SQL compatibility.
See DISTINCT clause for more details.
TOP int
TOP ALL
Optional — The TOP clause limits the number of rows returned to the number specified in int. If no ORDER BY clause is specified in the query, which records are returned as the “top” rows is unpredictable. If an ORDER BY clause is specified, the top rows accord to the specified order. The DISTINCT keyword (if specified) is applied before TOP, specifying that int number of unique values are to be returned. The int argument can be either a positive integer or a Dynamic SQL ? input parameter that resolves to a positive integer. If no TOP keyword is specified, the default is to display all the rows that meet the SELECT criteria.
TOP ALL is only meaningful in a subquery or in a CREATE VIEW statement. It is used to support the use of an ORDER BY clause in these situations, fulfilling the requirement that an ORDER BY clause must be paired with a TOP clause in a subquery or a query used in a CREATE VIEW. TOP ALL does not restrict the number of rows returned.
See TOP clause for more details.
select-item One or more columns (or other values) to be retrieved. Multiple select-items are specified as a comma-separated list. You can also retrieve all columns by using the * symbol.
INTO host-variable-list Optional — (Embedded SQL only): One or more host variables into which select-item values are placed. Multiple host variables are specified as a comma-separated list or as a single host variable array. See INTO clause for more details.
FROM table-ref
Optional — A reference to one or more tables from which data is being retrieved. A valid table-ref is required for every FROM clause, even if the SELECT makes no reference to that table. A SELECT that makes no references to table data can omit the FROM clause.
A table-ref can be specified as one or more tables, views, table-valued functions, or subqueries, specified as a comma-separated list or with JOIN syntax. Some restrictions apply on using views with JOIN syntax. A subquery must be enclosed in parentheses.
A table-ref is either qualified (schema.tablename) or unqualified (tablename). An unqualified table-ref is supplied either the system-wide default schema name, or (if provided) a schema name from the schema search path. In Embedded SQL you can use the #SQLCompile Path macro directive to supply a schema search path. In Dynamic SQL you can use the %SQL.Statement %New method to supply a schema search path.
Multiple tables can be specified as a comma-separated list or associated with ANSI join keywords. Any combination of tables or views can be specified. If you specify a comma between two table-refs here, Caché performs a CROSS JOIN on the tables and retrieves data from the results table of the JOIN operation. If you specify ANSI join keywords between two table-refs here, Caché performs the specified join operation. For further details, refer to the JOIN page of this manual.
You can optionally assign an alias (t-alias) to each table-ref. The AS keyword is optional.
You can optionally specify one or more optimize-option keywords to optimize query execution. The available options are: %ALLINDEX, %FIRSTTABLE, %FULL, %INORDER, %IGNOREINDEX, %NOFLATTEN, %NOMERGE, %NOREDUCE, %NOSVSO, %NOTOPOPT, %NOUNIONOROPT, %PARALLEL, and %STARTTABLE. See FROM clause for more details.
WHERE condition-expression Optional — A qualifier specifying one or more predicate conditions for what data is to be retrieved. See WHERE clause for more details.
GROUP BY scalar-expression Optional — A comma-separated list of one or more scalar expressions specifying how the retrieved data is to be organized; these may include column names. See GROUP BY clause for more details.
HAVING condition-expression Optional — A qualifier specifying one or more predicate conditions for what data is to be retrieved. See HAVING clause for more details.
ORDER BY item-order-list Optional — A select-item or a comma-separated list of items that specify the order in which rows are displayed. Each item can have an optional ASC (ascending order) or DESC (descending order). The default is ascending order. An ORDER BY clause is used on the results of a query. An ORDER BY clause in a subquery (for example, in a UNION statement) must be paired with a TOP clause. If no ORDER BY clause is specified, the order of the records returned is unpredictable. See ORDER BY clause for more details.
scalar-expression A field identifier, an expression containing a field identifier, or a general expression, such as a function call or an arithmetic operation.
AS t-alias Optional — An alias for a table or view name (table-ref). An alias must be a valid identifier; it can be a delimited identifier. For further details see the “Identifiers” chapter of Using Caché SQL. The AS keyword is optional.
AS c-alias Optional — An alias for a column name (select-item). An alias must be a valid identifier. For further details see the “Identifiers” chapter of Using Caché SQL. The AS keyword is optional.
Description
The SELECT statement performs a query that retrieves data from a Caché database. In its simplest form, it retrieves one or more items from a single table. The items are specified by the select-item list and the table is specified by the FROM table-ref clause. In more complex queries, a SELECT can retrieve data from multiple tables and can retrieve data using views.
A SELECT can also be used to return a value from an SQL function, a host variable, or a literal. A SELECT query can combine returning these non-database values with retrieving values from tables or views. When a SELECT is only used to return such non-database values, the FROM clause is optional. See FROM clause for more details.
The values returned from a SELECT query are known as a result set. In Dynamic SQL, SELECT retrieves values into the %SQL.Statement class. Refer to the Dynamic SQL chapter of Using Caché SQL, and the %SQL.Statement class in the InterSystems Class Reference.
Caché sets a status variable SQLCODE, which indicates the success or failure of the SELECT. In addition, the SELECT operation sets the %ROWCOUNT local variable to the number of selected rows. Successful completion of a SELECT generally sets SQLCODE=0 and %ROWCOUNT to the number of rows selected. In the case of an embedded SQL containing a simple SELECT, data from (at most) one row is selected, so SQLCODE=0 and %ROWCOUNT is set to either 0 or 1. However, in the case of an embedded SQL SELECT that declares a cursor and fetches data from multiple rows, the operation completes when the cursor has been advanced to the end of the data (SQLCODE=100); at that point, %ROWCOUNT is set to the total number of rows selected. Refer to the FETCH command for further details.
Uses of SELECT
You can use a SELECT statement in the following contexts:
You can enclose the entire SELECT statement with one or more sets of parentheses, as follows:
Specifying optional parentheses generates a separate cached query for each set of parentheses added.
Privileges
To perform a SELECT query on one or more tables, you must either have column-level SELECT privileges for all of the specified select-item column(s), or table-level SELECT privileges for the specified table-ref table(s) or view(s). A select-item column specified using a table alias (such as t.Name or "MyAlias".Name) only requires column-level SELECT privilege, not table-level SELECT privilege.
When using SELECT *, note that column-level privileges cover all table columns named in the GRANT statement; table-level privileges cover all table columns, including those added after the privilege was assigned.
Failing to have the necessary privileges results in an SQLCODE -99 error (Privilege Violation). You can determine if the current user has SELECT privilege by invoking the %CHECKPRIV command. You can determine if a specified user has table-level SELECT privilege by invoking the $SYSTEM.SQL.CheckPriv() method. For privilege assignment, refer to the GRANT command.
Note:
Having table-level SELECT privilege for a table is not a sufficient test that the table actually exists. If the specified user has the %All role, CheckPriv() returns 1 even if the specified table or view does not exist.
A SELECT query that does not have a FROM clause does not require any SELECT privileges. A SELECT query that contains a FROM clause requires SELECT privilege, even if no column data is accessed by the query.
Required Clauses
The following are required clauses for all SELECT statements:
Optional Clauses
The following optional clauses operate on the virtual table that a FROM clause returns. All are optional, but, if used, must appear in the order specified:
The DISTINCT Clause
The DISTINCT keyword clause causes redundant field values to be eliminated. It has two forms:
Either type of DISTINCT clause can specify more than one item to test for uniqueness. Listing more than one item retrieves all rows that are distinct for the combination of both items. DISTINCT does consider NULL a unique value. For further details, see the DISTINCT clause reference page.
The TOP Clause
The TOP keyword clause specifies that the SELECT statement return only a specified number of rows. It returns the specified number of rows that appear at the “top” of the returned virtual table. By default, which rows are the “top” rows of the table is unpredictable. However, Caché applies the DISTINCT and ORDER BY clauses (if specified) before selecting the TOP rows. For further details, see the TOP clause reference page.
The select-item
This is a mandatory element for all SELECT statements. Commonly, a select-item refers to a field in the table(s) specified in the FROM clause. A select-item consists of one or more of the following items, with multiple items separated by commas:
The Column Alias
When specifying a select-item, you can use the AS keyword to specify an alias for the name of a column:
SELECT Name AS PersonName, DOB AS BirthDate, ...
The column alias is displayed as the column header in the result set. Specifying a column alias is optional; a default is always provided. A column alias is displayed with the specified letter case; it is not, however, case sensitive when referenced in an ORDER BY clause. The c-alias name must be a valid identifier. A c-alias name can be a delimited identifier. For further details see the “Identifiers” chapter of Using Caché SQL.
The AS keyword is not required, but makes the query text easier to read. Thus the following is also valid syntax:
SELECT Name PersonName, DOB BirthDate, ...
SQL does not perform uniqueness checking for column aliases. It is possible (though not desirable) for a field column and a column alias to have the same name, or for two column aliases to be identical. Such non-unique column aliases may cause an SQLCODE -24 “Ambiguous sort column” error when referenced by an ORDER BY clause. Column aliases, like all SQL identifiers, are not case sensitive.
Use of column aliases in other SELECT clauses is governed by query semantic processing order. You can reference a column by its column alias in an ORDER BY clause. You cannot reference a column alias in another select-item in the select list, in a DISTINCT BY clause, a WHERE clause, a GROUP BY clause, or a HAVING clause. You cannot reference a column alias in a JOIN operation’s ON clause or USING clause. You can, however, use a subquery to make a column alias available for use by other these other SELECT clauses, as shown in the Querying the Database chapter of Using Caché SQL.
Field Column Aliases
A select-item field name is not case-sensitive. However, unless you supply a column alias, the name of a field column in the result set follows the letter case of the SqlFieldName associated with the column property. The letter case of the SqlFieldName corresponds to the field name as specified in the table definition, not as specified in the select-item list. Therefore, SELECT name FROM Sample.Person returns the field column label as Name. Using a field column alias allows you to specify the letter case to display, as shown in the following example:
SELECT name,name AS NAME
FROM Sample.Person
 
Letter case resolution takes time. To maximize SELECT performance, you can specify the exact letter case of the field name, as specified in the table definition. However, determining the exact letter case of a field in the table definition is often inconvenient and prone to error. Instead, you can use a field column alias to avoid letter case issues. Note that all references to the field column alias must match in letter case.
The following Dynamic SQL example requires letter case resolution (the SqlFieldNames are “Latitude” and “Longitude”):
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT latitude,longitude FROM Sample.USZipCode"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WHILE rset.%Next() {WRITE rset.latitude," ",rset.longitude,! }
The following Dynamic SQL example does not requires letter case resolution, and therefore executes faster:
  ZNSPACE "SAMPLES"
  SET myquery = "SELECT latitude AS northsouth,longitude AS eastwest FROM Sample.USZipCode"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 { WRITE "%Prepare failed",$System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
   WHILE rset.%Next() {WRITE rset.northsouth," ",rset.eastwest,! }
The t-alias table alias prefix is not included in the column name. Therefore, in the following example, both columns are labeled as Name:
SELECT p.Name,e.Name
FROM Sample.Person AS p LEFT JOIN Sample.Employee AS e ON p.Name=e.Name
 
To distinguish the columns in a query that specifies multiple tables, you should specify column aliases:
SELECT p.Name AS PersonName,e.Name AS EmployeeName
FROM Sample.Person AS p LEFT JOIN Sample.Employee AS e ON p.Name=e.Name
 
You may also wish to provide a column alias to make the data easier to understand. In the following example, the table column “Home_State” is renamed “US_State_Abbrev”:
SELECT Name,Home_State AS US_State_Abbrev
FROM Sample.Person
 
Note that %ID references a specific column, and therefore returns that field name (ID, by default), or a specified column alias, as shown in the following example:
SELECT %ID,%ID AS Ident,Name
FROM Sample.Person
 
Non-Field Column Aliases
Non-field columns are automatically assigned a column name. If you provide no alias for such fields, Caché SQL supplies a unique column name, such as “Expression_1”, or “Aggregate_3”. The integer suffix refers to the select-item position as specified in the SELECT statement. They are not a count of fields of that type.
The following are automatically assigned column names (n is an integer):
In the following example, the aggregate field column created by the AVG function is given the column alias “AvgAge”; its default name is “Aggregate_3” (an aggregate field in position 3 in the SELECT list).
SELECT Name, Age, AVG(Age) AS AvgAge FROM Sample.Person
 
The following example is identical to the previous, except that the AS keyword is here omitted. The use of this keyword is recommended, but not required.
SELECT Name, Age, AVG(Age) AvgAge FROM Sample.Person
 
The following example show how to specify a column alias for a select-item subquery:
SELECT Name AS PersonName,
       (SELECT Name FROM Sample.Employee) AS EmpName,
       Age AS YearsOld
FROM Sample.Person
 
FROM Clause
The FROM table-ref clause specifies one or more tables, views, table-valued functions, or subqueries. You can specify any combination of these table-ref types as a comma-separated list or with JOIN syntax. If you specify a single table-ref, the specified data is retrieved from that table or view. If you specify multiple table-refs, SQL performs a join operation on the tables, merging their data into a results table from which the specified data is retrieved.
If you specify more than one table-ref, you can separate these table names with commas or with explicit join syntax keywords. For further details on specifying a comma-separated list of table names, see the FROM clause reference page. For further details on specifying multiple table names with explicit JOIN syntax (such as RIGHT JOIN or *=) see the JOIN reference page.
You can use the $SYSTEM.SQL.TableExists() or $SYSTEM.SQL.ViewExists() method to determine whether a table or view exists in the current namespace. You can use the $SYSTEM.SQL.CheckPriv() method to determine if you have SELECT privileges for that table or view.
The Table Alias
When specifying a table-ref, you can use the AS keyword to specify an alias for that table name or view name:
FROM Sample.Person AS P
The AS keyword is not required, but makes the query text easier to read. The following is valid equivalent syntax:
FROM Sample.Person P
The t-alias name must be a valid identifier. A t-alias name can be a delimited identifier. A t-alias must be unique among table aliases within the query. A t-alias, like all identifiers, is not case sensitive. Therefore, you cannot specify two t-alias names that differ only in letter case. This results in an SQLCODE -20 “Name conflict” error. For further details see the “Identifiers” chapter of Using Caché SQL.
The table alias is used as a prefix (with a period) to a field name to indicate the table to which the field belongs. For example:
SELECT P.Name, E.Name
FROM Sample.Person AS P, Sample.Employee AS E
 
You must use a table reference prefix when a query specifies multiple tables that have the same field name. A table reference prefix can be a t-alias (as shown above) or it can be the fully qualified table name, as shown in the following equivalent example:
SELECT Sample.Person.Name, Sample.Employee.Name
FROM Sample.Person, Sample.Employee
 
Specifying a table alias is optional when a query references only one table (or view). Specifying a table alias is optional (but recommended) when a query references multiple tables (and/or views) and the field names referenced are unique to each table. Specifying a table alias is required when a query references multiple tables (and/or views) and the field names referenced are the same in different tables. Failing to specify a t-alias (or fully qualified table name) prefix results in an SQLCODE -27 “Field %1 is ambiguous among the applicable tables” error.
A t-alias only uniquely identifies a field for query execution; to uniquely identify a field for query result set display you must also use a column alias (c-alias). The following example uses both table aliases (Per and Emp) and column aliases (PName and Ename):
SELECT Per.Name AS PName, Emp.Name AS EName
FROM Sample.Person AS Per, Sample.Employee AS Emp
WHERE Per.Name %STARTSWITH 'G'
 
You can use the same name for a field, a column alias, and/or a table alias without a naming conflict.
A t-alias prefix is used wherever it is necessary to distinguish which table is being referred to. Some examples of this are shown in the following:
SELECT P.%ID As PersonID,
       AVG(P.Age) AS AvgAge,
       Z.%TABLENAME||'=' AS Tablename,
       Z.*
FROM Sample.Person AS P, Sample.USZipCode AS Z
WHERE P.Home_City = Z.City
GROUP BY P.Home_City
ORDER BY Z.City 
 
WHERE Clause
The WHERE clause qualifies or disqualifies specific rows from the query selection. The rows that qualify are those for which the condition-expression is true. The condition-expression is a list of logical tests (predicates) which can be linked by the AND and OR logical operators. These predicates may be inverted using the NOT unary logical operator.
The SQL predicates fall into the following categories:
For further details on these logical predicates, see the WHERE clause reference page. The condition-expression cannot contain aggregate functions. If you wish to specify a selection condition using a value returned by an aggregate function, use a HAVING clause.
A WHERE clause can specify an explicit join between two tables using the = (inner join), =* (left outer join), and *= (right outer join) symbolic join operators. For further details, refer to the JOIN page of this manual.
A WHERE clause can specify an implicit join between the base table and a field from another table using the arrow syntax (–>) operator. For further details, refer to Implicit Joins in Using Caché SQL.
GROUP BY Clause
The GROUP BY clause takes the resulting rows of a query and breaks them up into individual groups according to one or more database columns. When you use SELECT in conjunction with GROUP BY, one row is retrieved for each distinct value of the GROUP BY fields. The GROUP BY clause is conceptually similar to the Caché extension %FOREACH, but GROUP BY operates on an entire query, while %FOREACH allows selection of aggregates on sub-populations without restricting the entire query population. For instance:
SELECT Home_State, COUNT(Home_State) AS Population
 FROM Sample.Person
  GROUP BY Home_State
 
This query returns one row for each distinct Home_State.
For further details, see the GROUP BY clause reference page.
HAVING Clause
The HAVING clause is like a WHERE clause that operates on groups. It is typically used in combination with the GROUP BY clause, or with the %AFTERHAVING keyword. The HAVING clause qualifies or disqualifies specific rows from the query selection. The rows that qualify are those for which the condition-expression is true. The condition-expression is a list of logical tests (predicates) which can be linked by the AND and OR logical operators. The condition-expression can contain aggregate functions. For further details, see the HAVING clause reference page.
ORDER BY Clause
An ORDER BY clause consists of the ORDER BY keywords followed by a select-item or a comma-separated list of items that specify the order in which rows are displayed. Each item can have an optional ASC (ascending order) or DESC (descending order). The default is ascending order. An ORDER BY clause is applied to the results of a query, and is frequently paired with a TOP clause. For further details, see the ORDER BY clause reference page.
The following example returns the selected fields for all rows in the database, and orders these rows in ascending order by age:
SELECT Home_State, Name, Age 
FROM Sample.Person
ORDER BY Age
 
SELECT and Transaction Processing
A transaction performing a query is defined as either READ COMMITTED or READ UNCOMMITTED. A query that is not in a transaction is defined as READ UNCOMMITTED.
For further details, refer to SET TRANSACTION and START TRANSACTION.
Query Metadata
You can use Dynamic SQL to return metadata about the query, such as the number of columns specified in the query, the name (or alias) of a column specified in the query, and the data type of a column specified in the query. For further details, refer to the Dynamic SQL chapter of Using Caché SQL, and the %SQL.Statement class in the InterSystems Class Reference.
Examples
The following four examples perform similar queries, using different combinations of SELECT clauses. Note that these clauses must be specified in the correct order. In all four examples, three fields are selected from the Sample.Person table: Name, Home_State, and Age, and two fields (AvgAge and AvgMiddleAge) are computed.
HAVING/ORDER BY
In the following example, the AvgAge computed field is computed on all records in Sample.Person. The HAVING clause governs the AvgMiddleAge computed field, calculating the average age of those over 40 from all records in Sample.Person. Thus, every row has the same value for AvgAge and AvgMiddleAge. The ORDER BY clause sequences the display of the rows alphabetically by the Home_State field value.
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 HAVING Age > 40
 ORDER BY Home_State
 
WHERE/HAVING/ORDER BY
In the following example, the WHERE clause limits the selection to the seven specified northeastern states. The AvgAge computed field is computed on the records from those Home_States. The HAVING clause governs the AvgMiddleAge computed field, calculating the average age of those over 40 from the records from the specified Home_States. Thus, every row has the same value for AvgAge and AvgMiddleAge. The ORDER BY clause sequences the display of the rows alphabetically by the Home_State field value.
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 HAVING Age > 40
 ORDER BY Home_State
 
GROUP BY/HAVING/ORDER BY
The GROUP BY clause causes the AvgAge computed field to be separately computed for each Home_State group. The GROUP BY clause also limits the output display to the first record encountered from each Home_State. The HAVING clause governs the AvgMiddleAge computed field, calculating the average age of those over 40 in each Home_State group. The ORDER BY clause sequences the display of the rows alphabetically by the Home_State field value.
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State
 
WHERE/GROUP BY/HAVING/ORDER BY
The WHERE clause limits the selection to the seven specified northeastern states. The GROUP BY clause causes the AvgAge computed field to be separately computed for each of these seven Home_State groups. The GROUP BY clause also limits the output display to the first record encountered from each specified Home_State. The HAVING clause governs the AvgMiddleAge computed field, calculating the average age of those over 40 in each of the seven Home_State groups. The ORDER BY clause sequences the display of the rows alphabetically by the Home_State field value.
SELECT Name,Home_State,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 WHERE Home_State IN ('ME','NH','VT','MA','RI','CT','NY')
 GROUP BY Home_State
 HAVING Age > 40
 ORDER BY Home_State
 
Embedded SQL and Dynamic SQL Examples
Embedded SQL and Dynamic SQL can be used to issue a SELECT query from within a program in another language. Embedded SQL can be included in Caché ObjectScript code. Dynamic SQL can be included in either Caché ObjectScript code or Caché Basic code.
The following embedded SQL program retrieves data values from one record and places them in the output host variables specified in the INTO clause.
   NEW SQLCODE,%ROWCOUNT
   &sql(SELECT Home_State,Name,Age
        INTO :a, :b, :c
        FROM Sample.Person)
   IF SQLCODE=0 {
     WRITE !,"  Name=",b
     WRITE !,"  Age=",c
     WRITE !,"  Home State=",a
     WRITE !,"Row count is: ",%ROWCOUNT }
   ELSE {
     WRITE !,"SELECT failed, SQLCODE=",SQLCODE  }
 
This program retrieves (at most) one row, so the %ROWCOUNT variable is set to either 0 or 1. To retrieve multiple rows, you must declare a cursor and use the FETCH command. For further details, refer to the Embedded SQL chapter in Using Caché SQL.
The following Dynamic SQL example first tests whether the desired table exists and checks the current user’s SELECT privilege for that table. It then executes the query and returns a result set. It uses the WHILE loop to repeatedly invoke the %Next method for the first 10 records of the result set. It displays three field values using %GetData methods that specify the field position as specified in the SELECT statement:
  ZNSPACE "Samples"
  SET tname="Sample.Person"
  IF $SYSTEM.SQL.TableExists(tname)
     & $SYSTEM.SQL.CheckPriv($USERNAME,"1,"_tname,"s")
     {GOTO SpecifyQuery}
  ELSE {WRITE "Table unavailable"  QUIT}
SpecifyQuery
  SET myquery = 3
  SET myquery(1) = "SELECT Home_State,Name,SSN,Age"
  SET myquery(2) = "FROM "_tname
  SET myquery(3) = "ORDER BY Name"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET tStatus = tStatement.%Prepare(.myquery)
  IF tStatus '= 1 { WRITE "%Prepare error",!
                  QUIT }
  SET rset = tStatement.%Execute()
  IF rset.%SQLCODE=0 {
    SET x=0
    WHILE x < 10 {
     SET x=x+1
     SET status=rset.%Next()
     WRITE rset.%GetData(2)," "   /* Name field */
     WRITE rset.%GetData(1)," "   /* Home_State field */
     WRITE rset.%GetData(4),!     /* Age field */
    }
    WRITE !,"End of Data"
    WRITE !,"SQLCODE=",rset.%SQLCODE," Row Count=",rset.%ROWCOUNT
  }
  ELSE {
    WRITE !,"SELECT failed, SQLCODE=",rset.%SQLCODE }
 
For further details, refer to the Dynamic SQL chapter in Using Caché SQL.
See Also