SELECT (SQL)
Synopsis
SELECT * FROM table
SELECT selectItem FROM table
SELECT selectItem, selectItem2, ... FROM table
SELECT ... FROM table, table2, ...
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... [WHERE condition] GROUP BY column
SELECT ... FROM ... [WHERE condition][GROUP BY column]
HAVING condition
SELECT ... FROM ... [WHERE condition][GROUP BY column]
[HAVING condition] ORDER BY itemOrder [ASC | DESC]
SELECT selectItem AS columnAlias FROM ...
SELECT selectItem AS columnAlias, selectItem2 AS columnAlias2, ... FROM ...
SELECT ... FROM table AS tableAlias ...
SELECT ... FROM table AS tableAlias, table2 AS tableAlias2, ...
SELECT DISTINCT ... FROM ...
SELECT DISTINCT BY (distinctItem) ... FROM ...
SELECT DISTINCT BY (distinctItem, distinctItem2, ...) ... FROM ...
SELECT TOP numRows ... FROM ...
SELECT DISTINCT TOP ... FROM ...
SELECT TOP ALL ... FROM ...
SELECT ALL ... FROM ...
SELECT selectItem INTO :var FROM ...
SELECT selectItem, selectItem2, ... INTO :var, :var2, ... FROM ...
SELECT * INTO :var() FROM ...
SELECT %keyword ... FROM ...
(SELECT ... FROM ...)
SELECT ... FROM table WHERE %ID %FIND %SQL.SAMPLE( tablename, percent, seed )
SELECT WITH PREDICTIONS( model-name ) ... FROM ...
Description
The SELECT statement performs a query that retrieves data from an InterSystems IRIS® database. In its simplest form, it retrieves data from one or more columns of a single table. The SELECT selectItem clause specifies the columns to select. The FROM table clause specifies the table to select from, and the optional WHERE clause supplies one or more condition elements that determine which rows to return column values for.
In more complex queries, a SELECT statement can retrieve column data, aggregate data, computed column data, and data from multiple tables using joins. It can also retrieve data using views.
You can use a SELECT statement in these contexts:
-
An independent InterSystems SQL query.
-
A subquery that supplies values to an enclosing SELECT statement.
-
A subset of a UNION. The UNION statement allows you to combine two or more SELECT statements into a single query.
-
Part of a CREATE VIEW defining the data available to the view.
-
Part of an INSERT with a SELECT statement. An INSERT statement can use a SELECT to insert data values for multiple rows into a table, selecting the data from another table. For more details, see Multi-Row Inserts.
-
An independent query prepared as a Dynamic SQL query, Embedded SQL query, or Class Query. You can also use Dynamic SQL to return metadata about a SELECT 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.
-
Part of a DECLARE CURSOR used with Embedded SQL.
SELECT returns the results of a query in a result set, where the results are formatted according to the SelectMode. The command also sets a status variable, SQLCODE, indicating success or failure of the query. For more details, see SELECT Status and Return Values.
SELECT clauses must be specified in the order shown in the syntaxes. Specifying SELECT clauses in the incorrect order generates an SQLCODE -25 error. The SELECT syntax order is not the same as the SELECT clauses semantic processing order. For further details, refer to SELECT Clause Order of Execution.
Basic Selection
-
SELECT * FROM table selects all items from a table. Typically, these items are the columns in the table.
This query selects all columns from the Sample.Person table.
SELECT * FROM Sample.Person
For more details on this format, see All Column Selections (Asterisk Syntax).
-
SELECT selectItem FROM table selects a single item from a table.
This query selects the Name column from the Sample.Person table.
SELECT Name FROM Sample.Person
-
SELECT selectItem, selectItem2, ... FROM table selects multiple items from a table using a comma-separated list of selectItem values.
This query selects the Name and Age columns from the Sample.Person table.
SELECT Name,Age FROM Sample.Person
-
SELECT ... FROM table, table2, ... selects the items from multiple tables using a comma-separated list of table names. If you try to select column names that exist in multiple tables without using an alias to specify which table to select the field from, the system raises a SQLCODE -27.
This query selects the SSN and Mission columns from both the Sample.Person and Sample.Company table. For each SSN, it returns all Missions:
SELECT SSN,Mission FROM Sample.Person,Sample.Company
To associate tables in a SELECT statement and select data from the intersection of the tables, use JOIN expressions.
For statements that do not reference table data, such as ones that return data from functions, the FROM clause is optional. For more details on this clause, see FROM.
Predicate Conditions
-
SELECT ... FROM ... WHERE condition returns the rows of the table for which condition, a set of predicates linked by logical operators, is true. For example, this statement selects only people who are over 40 and live in Massachusetts:
SELECT Name,Age,Home_State FROM Sample.Person WHERE Age > 40 AND Home_State = 'MA'
The condition argument also limits the values supplied to aggregate functions to the values from those rows. A WHERE clause predicate does not directly accept aggregate functions. These values must be passed to the WHERE clause from other clauses. For more details, see WHERE.
-
SELECT ... FROM ... [WHERE condition] GROUP BY column organizes query result sets into groups, returning one row for each distinct value retrieved from the specified table columns.
The column argument can be a comma-separated list of column names or a scalar expression that evaluates to a column name. The GROUP BY clause is often used in conjunction with aggregate functions.
This query returns one row for each distinct state found in the Home_State column, with the computed COUNT(Home_State) selection returning the count of each state.
SELECT Home_State, COUNT(Home_State) FROM Sample.Person GROUP BY Home_State
For more details on this clause, see GROUP BY .
-
SELECT ... FROM ... [WHERE condition][GROUP BY column] HAVING condition returns the table rows for which HAVING condition is true. Unlike the WHERE clause, the HAVING clause operates on groups and is often used in combination with the GROUP BY clause.
HAVING condition determines which rows are returned, but by default does not limit the values supplied to aggregate functions to the values from those rows. To override this default, use the %AFTERHAVING keyword.
Unlike WHERE clauses, HAVING clauses can specify aggregate functions.
This query returns rows for which Age is greater than average age of all people in the database.
SELECT Name,AVG(Age %AFTERHAVING) FROM Sample.Person HAVING (Age > AVG(Age))
For more details on this clause, see HAVING.
-
SELECT ... FROM ... [WHERE condition][GROUP BY column][HAVING condition] ORDER BY itemOrder [ASC | DESC] specifies the order in which to display the returned rows. Specify itemOrder as a selection item specified in the SELECT selectItem clause or as a comma-separated list of such items.
Each item can have an optional ASC (ascending order) or DESC (descending order) keyword specifying the order of the returned values for that item. The default is ascending order.
This query returns the selected columns for all rows in the database in ascending order by age:
SELECT Home_State, Name, Age FROM Sample.Person ORDER BY Age
The SELECT command applies the ORDER BY clause to the results of a query. This clause is frequently paired with a TOP clause. If you use an ORDER BY clause in a subquery or CREATE VIEW query, then the TOP clause is required.
An ORDER BY clause can contain a window function, as described in Overview of Window Functions.
For more details on this clause, see ORDER BY.
Aliases
-
SELECT selectItem AS columnAlias FROM ... sets an alias for the name of a column or other selectItem value. The columnAlias value is displayed as the column head of the result set. Use aliases to make the returned data easier to understand. If you do not specify an alias, then the result set uses the column name specified in the select item.
This query returns the Home_State results in a column titled US_State_Abbrev:
Select Home_State AS US_State_Abbrev FROM Sample.Person
Example: Change Case of Columns in Result Set
-
SELECT selectItem AS columnAlias, selectItem2 AS columnAlias2, ... FROM ... sets aliases for multiple select items.
This query returns the results of Name and Home_State in PersonName and State columns, respectively.
Select Name AS PersonName,Home_State AS State FROM Sample.Person
Example: Distinguish Between Column Names in Multi-Table Queries
-
SELECT ... FROM table AS tableAlias ... sets an alias for the table name. You can use table aliases as the table prefixes in selectItem values.
-
SELECT ... FROM table AS tableAlias, table2 AS tableAlias2, ... sets an alias for multiple tables. You can use table aliases to indicate which table the selected column belong to. For example:
SELECT P.Name, E.Name FROM Sample.Person AS P, Sample.Employee AS E
Example: Distinguish Between Column Names in Multi-Table Queries
Selection Criteria
DISTINCT Clause
-
SELECT DISTINCT ... FROM ... returns only one row per unique combination of selectItem values. Use this clause to exclude redundant column values from the result set. You can specify one or more select items.
This query returns one row per unique combination of Home_State and Age values:
SELECT DISTINCT Home_State,Age FROM Sample.Person
-
SELECT DISTINCT BY (distinctItem) ... FROM ... returns one row per unique value of distinctItem, which must be an item from selectItem. Enclose distinctItem in parentheses. Use DISTINCT BY to return distinct values based on an item other than the one specified in selectItem. The query SELECT DISTINCT BY (item) item FROM Sample.Person is equivalent to SELECT DISTINCT item FROM Sample.Person.
This query returns a row with Name and Age values for each unique Age value:
SELECT DISTINCT BY (Age) Name,Age FROM Sample.Person
-
SELECT DISTINCT BY (distinctItem, distinctItem2, ...) ... FROM ... returns one row for each unique combination of distinctItem values using a comma-separated list.
This query returns a row with Name and Age values for each unique combination of Home_State and Age values:
SELECT DISTINCT BY (Home_State,Age) Name,Age FROM Sample.Person
TOP Clause
-
SELECT TOP numRows ... FROM ... returns the specified number of rows, numRows, that appear at the “top” of the returned table. The default “top” rows can be unpredictable. For more control over the top rows returned, include a DISTINCT clause to return only unique values and an ORDER BY clause to order the values based on specific rows. The SELECT command applies these clauses before selecting the TOP rows.
This query returns the top 10 Name values in alphabetical order.
SELECT TOP 10 Name FROM Sample.Person ORDER BY Name ASC
For more details, see TOP.
-
SELECT DISTINCT TOP ... FROM ... returns the unique “top” number of rows.
-
SELECT TOP ALL ... FROM ... selects all rows. This syntax 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 statement. The TOP ALL operation does not restrict the number of rows returned.
ALL Clause
-
SELECT ALL ... FROM ... returns all rows that meet the SELECT criteria. This is the default for InterSystems SQL. The ALL keyword performs no operation and is provided only for SQL compatibility.
Embedded SQL Host Variables
-
SELECT selectItem INTO :var FROM ... selects a single column from a table and saves it into host variable var. You can supply host variables for Embedded SQL queries only.
-
SELECT selectItem, selectItem2, ... INTO :var, :var2, ... FROM ... selects multiple columns and saves them to corresponding host variables. The number of columns must match the number of host variables. You can also use host variables in predicate clauses.
This class snippet declares a cursor that selects two fields and stores them in host variables for later fetches. The cursor sorts and filters the fetched results by storing the class input arguments as separate host variables.
ClassMethod AgeThreshold(ageThreshold As %Integer, orderBy As %String = "") As %Status { write "People who are age " _ageThreshold_ " and up:" &sql(declare CC cursor for SELECT Name,Age INTO :name,:age FROM Demo.Person WHERE (Age >= :ageThreshold) ORDER BY :orderBy) // ... }
Example: Select Data from Within ObjectScript Programs Using Embedded SQL and Dynamic SQL
-
SELECT * INTO :var() FROM ... selects all columns from a table and saves them to the subscripted variable var. Columns specified as private in the class definition are not included. To access the host variables, use the syntax var(colIndex), where colIndex is the column order index as determined by the SqlColumnNumber of the column. For more details, see Host Variable Subscripted by Column Number.
Keyword Options
-
SELECT %keyword ... FROM ... sets one or more %keyword options, separated by spaces. Valid options are %NOFPLAN, %NOLOCK, %NORUNTIME, %PROFILE, and %PROFILE_ALL. To use a %keyword argument, you must have the corresponding administration privilege for the current namespace. For more details, see GRANT.
Subqueries and Cached Queries
-
(SELECT ... FROM ...) generates a separate cached query for each set of parentheses added. Queries in parentheses are also a requirement for specifying subqueries.
In SELECT statements, you can specify a subquery in the selectItem list, the FROM clause, or in the WHERE clause with an EXISTS or IN predicate. You can also specify a subquery in an UPDATE or DELETE statement. A subquery must be enclosed in parentheses.
One or more sets of parentheses are optional for independent SELECT queries, UNION subset SELECT queries, CREATE VIEW SELECT queries, and DECLARE CURSOR SELECT queries. Enclosing a SELECT query in parentheses causes it to follow the syntax rules for a subquery. Specifically, you must pair an ORDER BY clause with a TOP clause.
Parentheses are not permitted for an INSERT ... SELECT statement.
Sample Selection
-
SELECT ... FROM table WHERE %ID %FIND %SQL.SAMPLE( tablename, percent, seed ) selects a random sample from the table. The percent argument specifies what percentage of each data block to sample; since the number of rows stored in a data block is variable, sampling 10% of the table does not necessarily return 10% of all the rows in the table. table and tablename must be the qualified name of the table you wish to sample from, but table must be an identifier and tablename must be a string.
Additionally, table cannot refer to a view, must have a bitmap extent index, and must be block-sampling enabled. The RowID field must be declared or implied to be a positive integer, as is the default behavior.
The seed argument is optional. If it is not provided, the default is the empty string.
Selection From a Time Series Machine Learning Model
-
SELECT WITH PREDICTIONS( model-name ) ... FROM ... selects the specified columns from a time series model, appending or pre-pending any predicted rows to the results, depending on the prediction window you specified when creating the model. You can use a WHERE clause to limit the results to the predicted rows by filtering on column that the time series was created from.
For more information about IntegratedML, see “Introduction to IntegratedML.”
Arguments
selectItem
selectItem is a mandatory argument for all SELECT statements that specifies an item, or comma-separated list of items to select from tables. You can specify each selectItem as one of the following:
-
The column name of a table specified in the FROM clause. See Table Column Selections.
-
A subquery that references table columns or an entire table. See Subquery Selections.
-
The column name of a table that is not specified in the FROM clause, using implicit joins (also known as arrow syntax). See Implicit Join Selections.
-
All columns in a table, using the asterisk (*) syntax. See All Column Selections.
-
Properties in an embedded serial object, using the underscore (_) syntax. See Embedded Serial Object Selections.
You can also specify selectItem as a function that modifies data selected from table columns or computes new data from the selection. You can specify these items:
-
A SQL function that aggregates selected column data and returns a single value. See Aggregate Function Selections.
-
A window function that calculates aggregates, ranking, and other functions for each row, based on a "window frame" specific to that row. See Window Function Selections.
-
An SQL function, ObjectScript class method call, or ObjectScript function call that operates on selected table data. See Function and Method Call Selections.
Finally, you can use selectItem to generate columns with the same value for all returned rows. You can also insert the same text or other data into each row of a selected column. These selections do not operate on the table data. See Non-Table Data Selections.
Table Column Selections
Commonly, a selectItem refers to a column in the table specified in the FROM clause. To specify multiple columns, use a comma-separated list. For example:
SELECT Name,Age FROM Sample.Person
Column names are not case-sensitive. However, the label associated with the column in the result set does not use the letter case specified in selectItem. Instead, it uses the letter case of the corresponding SqlFieldName ObjectScript property, as specified in the table definition. For more details on letter case resolution, see Change Case of Columns in Result Set example.
To list all of the column names defined for a specified table, see Column Names and Numbers.
To display the RowID (record ID), you can use the %ID pseudo-field variable alias, which displays the RowID regardless of what name it is assigned. By default, the name of the RowID is ID, but if the table already includes a column named ID, then InterSystems IRIS might rename it. By default, RowID is a hidden column.
Running a SELECT query on a stream column returns the OREF (object reference) of the opened stream object. For example:
SELECT Name,Picture FROM Sample.Employee WHERE Picture IS NOT NULL
When the FROM clause specifies more than one table or view, you must include the table name as part of the selectItem, separating the table name and column name with a period. For example:
SELECT Sample.Person.Name,Sample.Employee.Company
FROM Sample.Person, Sample.Employee
If you specified a table alias, specify that alias in the selectItem instead. For example:
SELECT p.Name, e.Company
FROM Sample.Person AS p, Sample.Employee AS e
If a table name already has an assigned alias, specifying the full table name as part of a selectItem results in an SQLCODE -23 error.
Subquery Selections
Specifying selectItem as a subquery returns a single column from a specified table. This column can contain the values of a single table column or of multiple table columns returned as a single column. You can return multiple columns in a single column by using either concatenation (SELECT Home_City||Home_State) or by specifying a container column (SELECT Home). A subquery can use implicit joins (arrow syntax). A subquery cannot use asterisk syntax, even when the table cited in the subquery has only one data column.
You can use subqueries to specify an aggregate function that is not subject to the GROUP BY clause. In the following example, the GROUP BY clause groups ages by decades (for example, 25 through 34). The AVG(Age) selectItem gives the average age of each group, as defined by the GROUP BY clause. To get the average age of all records across all groups, it uses a subquery:
SELECT Age AS Decade,
COUNT(Age) AS PeopleInDecade,
AVG(Age) AS AvgAgeForDecade,
(SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllDecades
FROM Sample.Person
GROUP BY ROUND(Age,-1)
ORDER BY Age
Implicit Join Selections (Arrow Syntax)
To access a column from a table other than the FROM clause table, you can specify selectItem as an implicit join by using the arrow syntax (->). In the following example, the Sample.Employee table contains a Company column, which in turn contains the RowID for the corresponding company name in the Sample.Company table. The arrow syntax retrieves the company name from that table:
SELECT Name,Company->Name AS CompanyName
FROM Sample.Employee
In this case, you must have SELECT privileges for the referenced table: either table-level SELECT privileges or column-level SELECT privileges for both the referenced column and the RowID column of the referenced table. For more details on arrow syntax, see Implicit Joins (Arrow Syntax).
All Column Selections (Asterisk Syntax)
To select all the columns in a table, use the asterisk syntax (*). For example:
SELECT TOP 5 * FROM Sample.Person
Items are returned in column number order. Asterisk syntax selections include embedded serial object properties, including properties from a serial object nested within a serial object. A column referencing a serial object is not selected. For example, the Home_City property from an embedded serial object is selected, but the Home referencing column used to access the Sample.Address embedded serial class, which contains the City property, is not selected.
Asterisk syntax does not select hidden columns. By default, the RowID is hidden (not displayed by SELECT *). However, if the table was defined with %PUBLICROWID, then SELECT * returns the RowID column and all non-hidden columns. By default, the name of this column is ID, but if a user-defined column named ID already exists, InterSystems IRIS might rename it.
A query with an asterisk and more than one table selects all the columns in all the joined tables. For example, this query selects all columns for the top 5 rows of both Sample.Company and Sample.Employee.
SELECT TOP 5 * FROM Sample.Company,Sample.Employee
The asterisk syntax can be qualified or unqualified. If the selectItem is qualified by prefixing a table name (or table name alias) and period (.) before the asterisk, the selectItem selects all the columns in the specified table. You can combine the qualified asterisk syntax with other select items for other tables. In this example, selectItem consists of an unqualified asterisk syntax that selects all columns from the table. Note that you can also specify duplicate column names (in this case Name) and non-column selectItem elements (in this case {fn NOW}):
SELECT TOP 5 {fn NOW} AS QueryDate,
Name AS Client,
*
FROM Sample.Person
In this example, selectItem consists of the qualified asterisk syntax that selects all columns from one table and a list of column names from another table.
SELECT TOP 5 E.Name AS EmpName,
C.*,
E.Home_State AS EmpState
FROM Sample.Employee AS E, Sample.Company AS C
SELECT * is a fully supported part of InterSystems SQL that can be extremely convenient during application development and debugging. However, in production applications, the preferred programming practice is to explicitly list the selected columns. Explicitly listing columns makes your application clearer and easier to understand, easier to maintain, and easier to search for columns by name.
Embedded Serial Object Selections (Underscore Syntax)
To select an embedded serial object property (embedded serial class data), specify selectItem using underscore syntax. Underscore syntax consists of the name of the object property, an underscore, and the property within the embedded object: for example, Home_City and Home_State. (In other contexts, index tables for example, these are represented using dot syntax: Home.City.)
Consider this example:
SELECT Home_City,Home_Phone_AreaCode FROM Sample.Person
For the column name Home_City, the Sample.Person table contains a referencing column Home. This column references an embedded serial object that defines the property City. For the column name Home_Phone_AreaCode, the table contains a referencing column Home that references an embedded serial object property Phone that references a nested embedded serial object that defines the property AreaCode. If you select a referencing column such as Home or Home_Phone, you receive the values of all of properties in the serial object in %List data type format.
You can use SELECT to directly query a referencing column (such as Home), rather than using the underscore syntax. Because the data returned is in list format, you can use a $LISTTOSTRING or $LISTGET function to display the data. For example:
SELECT $LISTTOSTRING(Home,'^') AS HomeAddress FROM Sample.Person
Aggregate Function Selections
A selectItem can contain one or more SQL aggregate functions. An aggregate function always returns a single value. This table shows the type of aggregate functions that you can specify.
Aggregate Function Type | Example |
---|---|
A single column name, which computes the aggregate for all non-null values of the rows selected by the query. |
SELECT AVG(Age) FROM Sample.Person |
A scalar expression that computes an aggregate. |
SELECT SUM(Age) / COUNT(*) FROM Sample.Person |
Asterisk syntax (*), used with the COUNT function to compute the number of rows in the table. |
SELECT COUNT(*) FROM Sample.Person |
A SELECT ... DISTINCT function, which computes the aggregate by eliminating redundant values. |
SELECT COUNT(DISTINCT Home_State) FROM Sample.Person |
A combination of column names and aggregate functions in a single SELECT statement (allowed in InterSystems SQL but not ANSI SQL). |
SELECT Name, COUNT(DISTINCT Home_State) FROM Sample.Person |
An aggregate function using %FOREACH, which computes the aggregate for each distinct value of a column or columns. |
SELECT DISTINCT Home_State, AVG(Age %FOREACH(Home_State)) FROM Sample.Person |
An aggregate function using %AFTERHAVING. This causes the aggregate to be computed on a sub-population specified with the HAVING clause. In the example shown, the query returns records where Age is greater than average age of all people in the database. |
SELECT Name,AVG(Age %AFTERHAVING) FROM Sample.Person HAVING (Age > AVG(Age)) |
Window Function Selections
Specify selectItem as a window function to calculate aggregates, rankings, and other functions for each row, based on a "window frame" specific to that row. The following syntax is supported:
windowFunction() OVER (
PARTITION BY partColumn
ORDER BY orderColumn)
-
windowFunction: The following window functions are supported: AVG(), ROW_NUMBER(), RANK(), PERCENT_RANK(), FIRST_VALUE(column), LAST_VALUE(column), NTH_VALUE(column, n), LAG(column, offset), LEAD(column, offset), MAX(column), MIN(column), and SUM(column).
-
OVER: The OVER keyword followed by parentheses is mandatory. Clauses within these parentheses are optional.
-
PARTITION BY partColumn: An optional clause that partitions rows by the specified partColumn. The partColumn argument can be a single column or a comma-separated list of columns. If specified, PARTITION BY must be specified before ORDER BY.
-
ORDER BY orderColumn: An optional clause that orders rows by the specified orderColumn. The orderColumn can be a single column or a comma-separated list of columns.
Columns specified in a window function can take a table alias prefix.
A window function can specify a column alias. By default the column is labeled Window_n.
For more details, see Overview of Window Functions.
Function and Method Call Selections
A selectItem can apply additional processing to the column values it selects by using function and method operations.
Note that the SelectMode is applied to the column values selected before they are returned, but not before a function or method is applied to them. This order of application can have implications on how the function or method operates on a piece of data and affects any operation applied to a column. While this order of application applies to all operations, it primarily has an impact on data types representing dates because of the variation in how such data types can be presented.
You can specify these operation types:
-
An arithmetic operation. For example, this selection generates a new column by subtracting the average age from the Age column. The SelectMode is not applied until after this operation has taken place.
SELECT Name, Age,Age-AVG(Age) FROM Sample.Person
If a selectItem arithmetic operation includes division, and any column value produces a divisor of 0 or NULL, you cannot rely on order of testing to avoid division by zero. Instead, use a case statement to suppress the risk.
-
An SQL function. For example, this query generates a column for the length of each value in the Name column.
SELECT Name,$LENGTH(Name) FROM Sample.Person
This query converts the case of the Name column to uppercase and returns it in a new column.
SELECT Name,UCASE(Name) FROM Sample.Person
This query generates a column for the length of each value in the Date column. In this example, the $LENGTH function is applied to the stored Logical value of the Date and the resulting value is returned according to the SelectMode. The Date column itself is returned according to the SelectMode.
SELECT Date,$LENGTH(Date) FROM Sample.Holidays
-
An XMLELEMENT, XMLFOREST, or XMLCONCAT function, which place XML or HTML tags around the data values retrieved from specified column names. For more details, see XMLELEMENT.
-
A collation function, which specifies the sorting and display of a selectItem column. You can supply the collation function without parentheses (SELECT %SQLUPPER Name) or with parentheses (SELECT %SQLUPPER(Name)). If the collation function specifies truncation, the parentheses are required (SELECT %SQLUPPER(Name,10)).
-
A user-defined class method stored as a procedure. The class method can be an unqualified method name (for example, RandLetter()) or a qualified method name (for example, Sample.RandLetter()). In this class, the Cube() class method returns the cube of the input integer:
Class Sample.Person Extends %Persistent [DdlAllowed] { /// Find the Cube of a number ClassMethod Cube(val As %Integer) As %Integer [SqlProc] { RETURN val * val * val } }
This query calls the Cube class method on the Age column to return the cubed age.
SELECT Age, Person_Cube(Age) FROM Sample.Person
InterSystems IRIS coverts the method return value from Logical to Display/ODBC format. By default, inputs to the method are not converted from Display/ODBC to Logical format. However,you can configure input display-to-logical conversion system-wide using the $SYSTEM.SQL.Util.SetOption("SQLFunctionArgConversion")Opens in a new tab method. To determine the current configuration of this option, use $SYSTEM.SQL.Util.GetOption("SQLFunctionArgConversion")Opens in a new tab.
If the specified method does not exist in the current namespace, the system generates an SQLCODE -359 error. If the specified method is ambiguous, meaning it could refer to more than one method, the system generates an SQLCODE -358 error. For more details on class method creation, see CREATE METHOD.
-
A user-supplied ObjectScript function call (extrinsic function) operating on a database column. For example:
SELECT $$REFORMAT(Name)FROM MyTable
To call such functions in an SQL statement, you must configure the Allow extrinsic functions in SQL statements option system-wide. For more details, see Functions: Intrinsic and Extrinsic. By default, extrinsic functions are disabled and attempting to call user-supplied functions generates an SQLCODE -372 error.
Trying to use a user-supplied function to call a % routine generates an SQLCODE -373 error.
Non-Table Data Selections
The selectItem argument can return the same value for all records without referencing the table that is in the FROM clause. When no selectItem elements reference table data, the FROM clause is optional. If you include the FROM clause, the specified table must exist. For more details, see FROM.
Common uses for this format selection are as follows:
-
Arithmetic operations.
SELECT Name, Age, 9 - 6 FROM Sample.Person
-
A string literal or a function operating on a string literal.
SELECT UCASE('fred') FROM Sample.Person
-
A string literal added to produce a more readable output.
SELECT TOP 10 Name,'was born on',%EXTERNAL(DOB) FROM Sample.Person
How you specify the numeric literal determines its data type. For example, the string '123' is of data type VARCHAR, and the numeric value 123 is of data type INTEGER or NUMERIC.
-
A %TABLENAME, or %CLASSNAME pseudo-field variable keyword. %TABLENAME returns the current table name. %CLASSNAME returns the name of the class corresponding to the current table. If the query references multiple tables, you can prefix the keyword with a table alias. For example, t1.%TABLENAME.
-
One of the following ObjectScript special variables (or their abbreviations): $HOROLOG, $JOB, $NAMESPACE, $TLEVEL, $USERNAME, $ZHOROLOG, $ZJOB, $ZNSPACE, $ZPI, $ZTIMESTAMP, $ZTIMEZONE, $ZVERSION.
table
One or more tables, views, table-valued functions, or subqueries from which data is being retrieved. You can specify any combination of these table types as a comma-separated list or with the JOIN syntax.
-
If you specify a single table name, the specified data is retrieved from that table or view.
-
If you specify multiple table names, InterSystems SQL performs a join operation on the tables, merging their data into a results table from which the specified data is retrieved.
A valid table reference is required for every FROM clause, even if the SELECT makes no reference to that table.
-
To determine whether a table or view exists in the current namespace, use the $SYSTEM.SQL.Schema.TableExists("schema.tname")Opens in a new tab or $SYSTEM.SQL.Schema.ViewExists("schema.vname")Opens in a new tab method.
-
To determine if you have SELECT privileges for a table or view, use the $SYSTEM.SQL.Security.CheckPrivilege()Opens in a new tab method
table can be either qualified (schema.tablename) or unqualified (tablename). An unqualified table is supplied either the default schema name or a schema name from the schema search path.
You can optionally assign an alias, tableAlias, to each table.
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. For more details on these options, see FROM.
condition
Logical tests (predicates) used in WHERE and HAVING clauses to specify the rows of data to retrieve. In SELECT statements, WHERE condition and HAVING condition return the rows for which condition evaluates to true.
To combine logical predicate conditions, use AND and OR logical operators. To invert a condition, use the NOT unary logical operator.
This table shows sample predicate conditions.
Predicate | Description | Example |
---|---|---|
Equality Comparisons |
Return rows using =, <, >, and other comparison operators. | SELECT Name,Age FROM Sample.Person WHERE Age < 21 |
Return rows between certain values | SELECT Name,Age FROM Sample.Person WHERE Age BETWEEN 18 AND 21 | |
Return rows that match items in a list. | SELECT Name, Home_State FROM Sample.Person WHERE Home_State IN ('ME','NH,'VT') | |
Substring Comparisons |
Return rows that match a substring | SELECT Name FROM Sample.Person WHERE Name %STARTSWITH 'S' |
Return rows based on the detection of undefined values | SELECT Name,Age FROM Sample.Person WHERE Age IS NOT NULL | |
Return rows based on the existence of at least one row in a table. Often used with subqueries. | SELECT Name FROM Sample.Person WHERE EXISTS (SELECT * FROM Employee WHERE Employee.Number = Person.Number) | |
Return rows based on a condition test of certain column values. Often used to test whether a value in one table appears in another table. | SELECT Name,COUNT(Name) FROM Sample.Person WHERE FOR SOME (Sample.Employee)(Sample.Employee.Name=Sample.Person.Name) | |
Return rows that match certain list element values. | SELECT Name,FavoriteColors FROM Sample.Person WHERE FOR SOME %ELEMENT(FavoriteColors) (%VALUE='Red') | |
Match row that fit a specific pattern. | SELECT Name FROM Sample.Person WHERE Name LIKE '%Mac%' |
For more details on these logical predicates, see WHERE.
condition cannot contain aggregate functions. To specify a selection condition using a value returned by an aggregate function, use a HAVING clause.
In WHERE clauses, condition can specify an explicit join between two tables using the = (inner join) symbolic join operators. For more details, see JOIN.
A WHERE clause can specify an implicit join between the base table and a column from another table using the arrow syntax (–>) operator. For more details, see Implicit Joins.
column
A comma-separated list of columns specifying how to organize retrieved data. Valid column values include:
-
A column name (GROUP BY City)
-
An %ID (returns all rows)
-
A scalar function specifying a column name (GROUP BY ROUND(Age,-1))
-
A collation function specifying a column name (GROUP BY %EXACT(City))
For more details, see GROUP BY.
itemOrder
A selectItem 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) keyword. The default is ascending order. The ORDER BY clause operates on the results of a query. An ORDER BY clause in a subquery, such as 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. An ORDER BY clause can include window functions. For more details, see ORDER BY.
columnAlias
In SELECT queries, each column in selectItem can have an alias. The column alias is displayed as the column header in the result set. If you do not specify a column alias, the name of the select item is used as the column name in the result set. The AS keyword separates the selectItem from the columnAlias. This keyword is optional but recommended for readability. Therefore, these syntaxes are equivalent and valid:
SELECT Name AS PersonName, DOB AS BirthDate FROM Sample.Person
SELECT Name PersonName, DOB BirthDate FROM Sample.Person
InterSystems SQL displays column aliases with the specified letter case, but aliases are not case-sensitive when referenced in an ORDER BY clause. The columnAlias name must be a valid identifier, including a delimited identifier. Using a delimited identifier permits a column alias to contain spaces, other punctuation characters, or to be an SQL reserved name (for example, SELECT Name AS "Customer Name" or SELECT Home_State AS "From").
SQL does not perform uniqueness checking for column aliases. It is possible (though not desirable) for a column and a column alias to have the same name, or for two column aliases to be identical. Such non-unique column aliases can 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.
Referencing a column alias in these places is not allowed:
-
Another selectItem in the select list
-
DISTINCT BY clause
-
WHERE clause
-
GROUP BY clause
-
HAVING clause
-
ON or USING clause of a JOIN operation
You can, however, use a subquery to make a column alias available for use by these other SELECT clauses, as described in Querying the Database.
In addition to setting column aliases, you can also set aliases for aggregate functions, expressions, or other computed columns. Computed columns are automatically assigned a column name. If you do not provide an alias, InterSystems SQL supplies a unique column name, such as Expression_1 or Aggregate_3. The integer suffix refers to the selectItem position as specified in the SELECT statement (that is, the selectItem column number). These values are not a count of columns of that type.
The following list shows the automatically assigned column names, where n is an integer. These names are listed in increasingly inclusive order. For example, adding a plus or minus sign to a number promotes it from a HostVar to an Expression; concatenating a HostVar and a Literal promotes it to an Expression; specifying a Literal, HostVar, Aggregate, or Expression in a subquery promotes it to a SubQuery:
-
Literal_n: A pseudo-field variable such as %TABLENAME, or the NULL specifier. Note that %ID is not Literal_n; it is given the column name of the actual RowID column.
-
HostVar_n: a host variable. This can be a literal, such as ‘text’, 123, or the empty string (''), an input variable (:myvar), or a ? input parameter replaced by a literal. Any expression evaluation on a literal, such as appending a sign to a number, string concatenation, or an arithmetic operation, makes it an Expression_n. A literal value supplied to a ? parameter is returned unchanged without expression evaluation. For example, supplying 5+7 returns the string '5+7' as HostVar_n.
-
Aggregate_n: An aggregate function, such as AVG(Age) or COUNT(*). A column is named Aggregate_n if the outermost operation is an aggregate function, even when this aggregate contains an expression. For example, COUNT(Name)+COUNT(Spouse) is Expression_n, but MAX(COUNT(Name)+COUNT(Spouse)) is Aggregate_n, -AVG(Age) is Expression_n, but AVG(-Age) is Aggregate_n. In this example, the aggregate column created by the AVG function is given the column alias AvgAge. Its default name is Aggregate_3 (an aggregate column in position 3 in the SELECT list).
SELECT Name, Age, AVG(Age) AS AvgAge FROM Sample.Person
-
Expression_n: Any operation in the selectItem list on a literal, a column, or on an Aggregate_n, HostVar_n, Literal_n, or Subquery_n selectItem changes its column name to Expression_n. This includes unary operations on numbers (-Age), arithmetic operations (Age+5), concatenation ('USA:'||Home_State), data type CAST operations, SQL collation functions (%SQLUPPER(Name) or %SQLUPPER Name), SQL scalar functions ($LENGTH(Name)), user-defined class methods, CASE expressions, and special variables (such as CURRENT_DATE or $ZPI).
-
Window_n: The result of a window function. You specify the column alias after the closing parenthesis of the OVER keyword.
-
Subquery_n: The result of a subquery that specifies a single selectItem. The selectItem can be a column, aggregate function, expression, or literal. Specify the column alias after, not within, the subquery. For example:
SELECT Name AS PersonName, (SELECT Name FROM Sample.Employee) AS EmpName, Age AS YearsOld FROM Sample.Person
tableAlias
In a SELECT statement, you can specify an optional alias for a table or view name (table) as a valid identifier, including a delimited identifier. The AS keyword separates the table from the tableAlias. This keyword is optional but recommended for readability. Therefore, these syntaxes are equivalent and valid:
SELECT P.Name FROM Sample.Person AS P
SELECT P.Name FROM Sample.Person P
A tableAlias must be unique among table aliases within the query. A tableAlias, like all identifiers, is not case-sensitive. Specifying two tableAlias names that differ only in letter case results in an SQLCODE -20 “Name conflict” error.
The table alias is used as a prefix (with a period) to a column name to indicate the table to which the column belongs. For example:
SELECT P.Name, E.Name
FROM Sample.Person AS P, Sample.Employee AS E
When a query specifies multiple tables that have the same column name, you must use a table reference prefix. A table reference prefix can be a tableAlias, as shown in the previous example, or a fully qualified table name, as shown in this equivalent example:
SELECT Sample.Person.Name, Sample.Employee.Name
FROM Sample.Person, Sample.Employee
If you assign a tableAlias to a table name, then specifying a full table name as part of a selectItem results in an SQLCODE -23 error. Table aliases are required or optional depending on the query scenario.
Scenario | Table Alias |
---|---|
A query references only one table. |
Optional |
A query references multiple tables and the column names referenced are unique to each table. |
Optional (but recommended) |
A query references multiple tables and the column names referenced are the same in different tables. |
Required Failing to specify a tableAlias (or fully qualified table name) prefix results in an SQLCODE -27 “Field %1 is ambiguous among the applicable tables” error. |
You can also optionally use a tableAlias when specifying a subquery like this one:
SELECT Name,(SELECT Name FROM Sample.Vendor)
FROM Sample.Person
A tableAlias only uniquely identifies a column for query execution. To uniquely identify a column for query result set display, you must also use a column alias (columnAlias). This query combines the use of 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 column, a column alias, and/or a table alias without a naming conflict.
Use the tableAlias prefix to distinguish which table is being referred to. For example:
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
distinctItem
A comma-separated list of selectItem columns from which you want to exclude redundant rows in the result set. The distinctItem argument accepts any valid selectItem value. It does not accept the asterisk (*) keyword that selects all items. It also does not accept column name aliases.
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 more details, see DISTINCT.
numRows
The number of rows to return, when used in conjunction with a TOP clause, as in TOP numRows. If the query does not contain an ORDER BY clause, the returned “top” rows is unpredictable. If the query contains an ORDER BY clause, the top rows are based on the specified order. If the query includes the DISTINCT keyword before TOP, then the query returns numRows unique values. Specify numRows as either a positive integer or a Dynamic SQL input parameter using the question mark (?) syntax 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.
var
One or more host variables into which you place selectItem values. Specify multiple host variables as a comma-separated list or as a single-host variable array. For more details, see INTO.
Specifying an INTO clause in a SELECT query processed via ODBC, JDBC, or Dynamic SQL results in an SQLCODE -422 error.
%keyword
One or more %keyword arguments, separated by spaces. These keywords affect processing as follows:
-
%NOFPLAN — The frozen plan (if any) is ignored for this operation; the operation generates a new query plan. The frozen plan is retained but not used. For more details, see Frozen Plans.
-
%NOLOCK — InterSystems IRIS performs no locking on any of the tables. If you specify this keyword, the query retrieves data in READ UNCOMMITTED mode, regardless of current transaction’s isolation mode. For more details, see Transaction Processing.
-
%NORUNTIME — Runtime Plan Choice (RTPC) optimization is not used.
-
%PROFILE or %PROFILE_ALL — Generate SQLStats collecting code. This is the same code that would be generated with PTools turned ON. The difference is that SQLStats collecting code is only generated for this specific statement. All other SQL statements within the routine or class being compiled generate code as if PTools is turned OFF. This enables you to profile and inspect specific problem SQL statements within an application without collecting irrelevant statistics for SQL statements that are not being investigated. For further details, see SQL Performance Analysis Toolkit.
%PROFILE collects SQLStats for the main query module. %PROFILE_ALL collects SQLStats for the main query module and all its subquery modules.
Examples
Select Subsets of Data Using Predicate Conditions
Select subsets of data from a table using different combinations of predicate conditions. The clauses shown in these examples must be specified in the correct order. In all four examples, you select three columns (Name, Home_State, and Age) from the Sample.Person table and compute two other columns (AvgAge and AvgMiddleAge).
HAVING and ORDER BY
This query computes the AvgAge column on all records in Sample.Person. The HAVING clause governs the AvgMiddleAge computed column, calculating the average age of people 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 column 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, and ORDER BY
In this query, the WHERE clause limits the selection to the seven specified northeastern states. The query computes the AvgAge column on the records from those states. The HAVING clause governs the AvgMiddleAge computed column, calculating the average age of those over 40 from the records from the specified Home_State column. 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 column 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, and ORDER BY
Here, the GROUP BY clause causes the query to compute the AvgAge column 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 column, 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 column 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, and ORDER BY
In this query, the WHERE clause limits the selection to the seven specified northeastern states. The GROUP BY clause causes the query to compute the AvgAge column separately 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 column, 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 column 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
Select Data from Within ObjectScript Programs Using Embedded SQL and Dynamic SQL
You can use Embedded SQL and Dynamic SQL to issue a SELECT query from within an ObjectScript program.
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 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 more details, see Embedded 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 column values using %GetData methods that specify the column position as specified in the SELECT statement:
SET tname="Sample.Person"
IF $SYSTEM.SQL.Schema.TableExists(tname)
& $SYSTEM.SQL.Security.CheckPrivilege($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 qStatus = tStatement.%Prepare(.myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) 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 column */
WRITE rset.%GetData(1)," " /* Home_State column */
WRITE rset.%GetData(4),! /* Age column */
}
WRITE !,"End of Data"
WRITE !,"SQLCODE=",rset.%SQLCODE," Row Count=",rset.%ROWCOUNT
}
ELSE {
WRITE !,"SELECT failed, SQLCODE=",rset.%SQLCODE }
For more details, see Dynamic SQL.
Change Case of Columns in Result Set
Column names specified in selectItem are not case-sensitive. However, unless you supply a column alias, the name of a 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 column name as specified in the table definition, not as specified in the selectItem list. Therefore, SELECT name FROM Sample.Person returns the column label as Name. Using a column alias allows you to specify the letter case to display. For example, this query displays the Name column in the result set as NAME (all caps).
SELECT name AS NAME
FROM Sample.Person
Letter case resolution takes time. To maximize SELECT performance, specify the exact letter case of the column name as specified in the table definition. However, determining the exact letter case of a column in the table definition is often inconvenient and prone to error. Instead, you can use a column alias to avoid letter case issues. Note that all references to the column alias must match in letter case.
The following Dynamic SQL example requires letter case resolution (the SqlFieldNames are “Latitude” and “Longitude”):
set query = "SELECT latitude,longitude FROM Sample.USZipCode"
set statement = ##class(%SQL.Statement).%New()
set status = statement.%Prepare(query)
if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
set rset = statement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
while rset.%Next()
{
write rset.latitude," ",rset.longitude,!
}
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
The following Dynamic SQL example does not require letter case resolution and therefore executes faster:
set query = "SELECT latitude AS northsouth,longitude AS eastwest FROM Sample.USZipCode"
set statement = ##class(%SQL.Statement).%New()
set status = statement.%Prepare(query)
if $$$ISERR(status) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(status) quit}
set rset = statement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
while rset.%Next()
{
write rset.northsouth," ",rset.eastwest,!
}
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
Distinguish Between Column Names in Multi-Table Queries
The returned result set of SELECT queries do not include the table alias prefix, tableAlias. Therefore, this query returns two columns named 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 such queries, specify column aliases. For example, this revised query returns the two columns as PersonName and EmployeeName:
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
Security and Privileges
To perform a SELECT query on one or more tables, you must have one or more of the following:
-
Column-level SELECT privileges for all of the specified selectItem columns
-
Table-level SELECT privileges for the specified table tables or views
-
SELECT privileges on the schema of the table
A selectItem column specified using a table alias (such as t.Name or "MyAlias".Name) requires only column-level SELECT privileges, not table-level SELECT privileges.
When using SELECT *, column-level privileges cover all table columns named in the GRANT statement. Table-level privileges cover all table columns, including columns added after the privilege assignment.
Failing to have the necessary privileges results in an SQLCODE -99 error (Privilege Violation). To 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.Security.CheckPrivilege()Opens in a new tab method. For more on privilege assignment, see GRANT.
Having table-level SELECT privileges for a table is not a sufficient test that the table actually exists. If the specified user has the %All role, then CheckPrivilege() 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 privileges, even if no column data is accessed by the query.
More About
SELECT Status and Return Values
When you perform a SELECT operation, InterSystems IRIS sets a status variable, SQLCODE, that indicates the success or failure of the operation. In addition, the SELECT operation sets the %ROWCOUNT local variable to the number of selected rows. Successful completion of a SELECT operation generally sets SQLCODE=0 and %ROWCOUNT to the number of rows selected. If embedded SQL code contains a simple SELECT statement, data from (at most) one row is selected, so SQLCODE=0 and %ROWCOUNT is set to either 0 or 1. If an embedded SQL SELECT statement declares a cursor and fetches data from multiple rows, the operation completes when the cursor advances to the end of the data (SQLCODE=100). At that point, %ROWCOUNT is set to the total number of rows selected. For more details, see FETCH.
The values returned from a SELECT query are known as a result set. In Dynamic SQL, SELECT retrieves values into the %SQL.StatementOpens in a new tab class. For more details, see Dynamic SQL and the %SQL.StatementOpens in a new tab class reference page.
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 query returns only non-database values, the FROM clause is optional. For more details, see FROM.
Sharding
Sharding is transparent to SQL queries, and no special query syntax is required. A query does not need to know whether a table specified in the FROM clause is sharded or non-sharded. The same query can access sharded and non-sharded tables. A query can include joins between sharded and non-sharded tables.
A sharded table is defined using the CREATE TABLE command. It must be defined in the master namespace on the shard master data server. This master namespace can also include non-sharded tables.
Transaction Processing
A transaction performing a query is defined as either READ COMMITTED or READ UNCOMMITTED. The default is READ UNCOMMITTED. A query that is not in a transaction is defined as READ UNCOMMITTED.
-
In READ UNCOMMITTED mode, a SELECT statement returns the current state of the data, including changes made to the data by transactions in progress that have not been committed. These changes can be subsequently rolled back.
-
In READ COMMITTED mode, the behavior depends on the contents of the SELECT statement. Normally, a SELECT statement in read committed mode returns only insert and update changes to data that has been committed. Data rows deleted by a transaction in progress are not returned, even though these deletes have not been committed and can be rolled back.
However, if the SELECT statement contains a %NOLOCK keyword, a DISTINCT clause, or a GROUP BY clause, the SELECT query returns the current state of the data, including changes made to data during the current transaction that have not been committed. An aggregate function in a SELECT statement also returns the current state of the data for the specified columns, including uncommitted changes.
For more details, see SET TRANSACTION and START TRANSACTION.