Language Elements
InterSystems SQL supports the following language elements:
Commands and Keywords
An InterSystems SQL command (also known as an SQL statement) begins with a keyword followed by one or more arguments. Some of these arguments may be clauses or functions, identified by their own keywords.

InterSystems SQL commands do not have a command terminator, except in specific cases such as SQL procedure code or trigger code, in which case SQL commands are terminated by a single semicolon (;). Otherwise, InterSystems SQL commands do not require or accept a semicolon command terminator. Specifying a semicolon command terminator in InterSystems SQL results in an SQLCODE 25 error. InterSystems IRIS® data platform implementation of TSQL (TransactSQL) accepts, but does not require, a semicolon command terminator. When importing SQL code to InterSystems SQL, semicolon command terminators are stripped out.

InterSystems SQL commands have no whitespace restrictions. If command items are separated by a space, at least one space is required. If command items are separated by a comma, no space is required. No space is required before or after arithmetic operators. You may insert line breaks or multiple spaces between spaceseparated items, between items in a commaseparated list of arguments, or before or after arithmetic operators.
InterSystems SQL keywords include command names, function names, predicate condition names, data type names, field constraints, optimization options, and special variables. They also include the AND, OR, and NOT logical operators, the NULL column value indicator, and ODBC function constructs such as {d dateval} and {fn CONCAT(str1,str2)}.

Keywords are not casesensitive. By convention, keywords are represented by capital letters in this documentation, but InterSystems SQL has no letter case restriction.

Many, but not all, keywords are SQL Reserved Words. InterSystems SQL only reserves those keywords that cannot be unambiguously parsed. SQL reserved words can be used as delimited identifiers.
Functions: Intrinsic and Extrinsic
A function performs an operation and returns a value. Commonly in InterSystems SQL a function is specified in a SELECT statement as a selectitem or in a WHERE clause, performing an operation either on a table field value or on a literal value.

Intrinsic: InterSystems SQL supports a large number of intrinsic (systemsupplied) functions. These include numeric functions, string functions, and date and time functions. These functions are described in the InterSystems SQL Reference. The arithmetic and trigonometric functions are also listed in this chapter.
Aggregate functions are SQL intrinsic functions that evaluate all of the values of a column and return a single aggregate value. Aggregate functions are described separately in the InterSystems SQL Reference.

Extrinsic: InterSystems SQL can also support usersupplied ObjectScript function calls (extrinsic functions), as shown in the following example:
MySQL &sql(SELECT Name,$$MyFunc() INTO :n,:f FROM Sample.Person) IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg QUIT} ELSEIF SQLCODE=100 {WRITE "Query returns no results" QUIT} WRITE "name is: ",n,! WRITE "function value is: ",f,! QUIT MyFunc() SET x="my text" QUIT x
An SQL statement can only invoke usersupplied (extrinsic) functions if their use is configured as a systemwide option. The default is “No”; by default, attempting to invoke usersupplied functions issues an SQLCODE 372 error. You can configure SQL use of extrinsic functions systemwide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("AllowExtrinsicFunctions",1,.oldval). To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays the Allow extrinsic functions in SQL statements option.
You cannot use a usersupplied function to call a % routine (a routine with a name that begins with the % character). Attempting to do so issues an SQLCODE 373 error.
Literals
InterSystems SQL literals have the following syntax:
literal ::= number  stringliteral number ::= {digit}[.]digit{digit}[E[+]digit{digit}] digit ::= 0..9 stringliteral ::= stdstringliteral  ObjectScriptemptystring stdstringliteral ::= ' {stdcharacterrepresentation} ' stdcharacterrepresentation ::= nonquotecharacter  quotesymbol quotesymbol ::= '' ObjectScriptemptystring ::= ""
A literal is a series of characters that represents an actual (literal) value. It can be either a number or a character string.

A number does not require any delimiter character. It can consist of the digits 0 through 9, the decimal point character, the exponent symbol and the plus and minus signs. Only one decimal point character can be used in a number. This decimal point can only be used in the base portion of a number, not in the exponent portion. The decimal point does not need to be followed by a digit. Leading and trailing zeros are permitted. The exponent (scientific notation) symbol is the letter E; both uppercase and lowercase E are accepted, but uppercase E is the preferred usage. A plus or minus sign can prefix a base number or an exponent. Multiple plus and minus signs can prefix a base number; SQL treats these signs as operators. Only a single plus or minus sign can prefix an exponent; SQL treats this sign as part of the literal. No commas or blanks are permitted in a number.

A character string literal consists of a pair of delimiter characters enclosing a string of characters of any type. The preferred delimiter character is the singlequote character (see below). To specify a delimiter character as a literal within a character string, double the character; for example: 'Mary''s office'.
The empty string is a literal string; it is represented by two singlequote characters (''). NULL is not a literal value; it represents the absence of any value. For further details, see the NULL and the Empty String section of this chapter.
In Embedded SQL, a few character sequences that begin with ## are not permitted within a string literal, as described in Literal Values in the “Using Embedded SQL” chapter. This restriction does not apply to other invocations of SQL, such as Dynamic SQL.
String Delimiters
Use single quote (') characters as string delimiters. The use of the doublequote character (") is supported for SQL compatibility, but this use is strongly discouraged because of conflict with the delimited identifier standard. A pair of double quote characters "" is parsed as an invalid delimited identifier and generates an SQLCODE 1 error.
To specify a single quote character as a literal character within a string, specify a pair of these characters as the literal escape sequence. For example, 'a ''normal'' string'.
Concatenation
The double vertical bar () is the preferred SQL concatenation operator. It can be used to concatenate two numbers, two character strings, or a number and a character string.
The underscore (_) is provided as an SQL concatenation operator for ObjectScript compatibility. This concatenation operator can only be used to concatenate two character strings.
If the two operands are both character strings, and both strings have the same collation type, the resulting concatenated string has that collation type. In all other cases, the result of concatenation is of collation type EXACT.
NULL and the Empty String
Use the NULL keyword to indicate that a value is not specified. NULL is always the preferred way in SQL to indicate that a data value is unspecified or nonexistent for any reason.
The SQL zerolength string (empty string) is specified by two single quote characters. The empty string ('') is not the same thing as NULL.
The SQL zerolength string is not recommended for use as a field input value or a field default value; in ObjectScript, this corresponds to a string of length one that contains the $CHAR(0) character. Use NULL to represent the absence of a data value, which corresponds to the ObjectScript empty string (""). See “ObjectScript and SQL” for more information.
The SQL zerolength string should be avoided in SQL coding. However, because many SQL operations delete trailing blank spaces, a data value that contains only whitespace characters (spaces and tabs) may result in an SQL zerolength string.
Note that different SQL length functions return different values: LENGTH, CHAR_LENGTH, and DATALENGTH return SQL lengths. $LENGTH returns ObjectScript representation length. See “The Length of NULL” below. LENGTH does not count trailing blank spaces; all other length functions count trailing blank spaces.
NULL Processing
The NOT NULL data constraint requires that a field must receive a data value; specifying NULL rather than a value is not permitted. This constraint does not prevent the use of an empty string value. For further details, refer to the CREATE TABLE command.
The IS NULL predicate in the WHERE or HAVING clause of a SELECT statement selects NULL values; it does not select empty string values.
The IFNULL function evaluates a field value and returns the value specified in its second argument if the field evaluates to NULL. It does not treat an empty string value as a nonNULL value.
The COALESCE function selects the first nonNULL value from supplied data. It treats empty string values as nonNULL.
When the CONCAT function or the concatenate operator () concatenate a string and a NULL, the result is NULL. This is shown in the following example:
SELECT {fn CONCAT('fred',NULL)} AS FuncCat,  returns <null>
'fred'NULL AS OpCat  returns <null>
The AVG, COUNT, MAX, MIN, and SUM aggregate functions ignore NULL values when performing their operations. (COUNT * counts all rows, because there cannot be a record with NULL values for all fields.) The DISTINCT keyword of the SELECT statement includes NULL in its operation; if there are NULL values for the specified field, DISTINCT returns one NULL row.
The AVG, COUNT, and MIN, aggregate functions are affected by empty string values. The MIN function considers an empty string to be the minimum value, even when there are rows that have a value of zero. The MAX and SUM aggregate functions are not affected by empty string values.
NULL in Expressions
Supplying NULL as an operand to most SQL functions returns NULL.
Any SQL arithmetic operation that has NULL as an operand returns a value of NULL. Thus, 7+NULL=NULL. This includes the binary operations addition (+), subtraction (), multiplication (*), division (/), integer division (\), and modulo (#), and the unary sign operators plus (+) and minus ().
An empty string specified in an arithmetic operation is treated as a value of 0 (zero). Division (/), integer division (\), or modulo (#) by empty string (6/'') results in a <DIVIDE> error.
The Length of NULL
Within SQL, the length of a NULL is undefined (it returns <null>). The length of an empty string, however, is defined as length zero. This is shown in the following example:
SELECT LENGTH(NULL) AS NullLen,  returns <null>
LENGTH('') AS EmpStrLen  returns 0
As shown in this example, the SQL LENGTH function returns the SQL lengths.
You can convert an SQL zerolength string to a NULL by using the ASCII function, as shown in the following example:
SELECT LENGTH(NULL) AS NullLen,  returns <null>
LENGTH({fn ASCII('')}) AS AsciiEmpStrLen,  returns <null>
LENGTH('') AS EmpStrLen  returns 0
However, certain InterSystems IRIS extensions to standard SQL treat the length of NULL and the empty string differently. The $LENGTH function returns the InterSystems IRIS internal representation of these values: NULL is represented as a defined value with length 0, the SQL empty string is represented as a string of length 0. This functionality is compatible with ObjectScript.
SELECT $LENGTH(NULL) AS NullLen,  returns 0
$LENGTH('') AS EmpStrLen,  returns 0
$LENGTH('a') AS OneCharStrLen,  returns 1
$LENGTH(CHAR(0)) AS CharZero  returns 0
Another place where the internal representation of these values is significant is in the %STRING, %SQLSTRING and %SQLUPPER functions, which append a blank space to a value. Since a NULL truly has no value, appending a blank to it creates a string of length 1. But an empty string does have a character value, so appending a blank to it creates a string of length 2. This is shown in the following example:
SELECT CHAR_LENGTH(%STRING(NULL)) AS NullLen,  returns 1
CHAR_LENGTH(%STRING('')) AS EmpStrLen  returns 2
Note that this example uses CHAR_LENGTH, not LENGTH. Because the LENGTH function removes trailing blanks, LENGTH(%STRING(NULL)) returns a length of 0; LENGTH(%STRING('')) returns a length of 2, because %STRING appends a leading blank, not a trailing blank.
ObjectScript and SQL
When an SQL NULL is output to ObjectScript, it is represented by an ObjectScript empty string (""), a string of length zero.
When an SQL zerolength string data is output to ObjectScript, it is represented by a string containing $CHAR(0), a string of length 1.
&sql(SELECT NULL,''
INTO :a,:b)
WRITE !,"NULL length: ",$LENGTH(a) // returns 0
WRITE !,"empty string length: ",$LENGTH(b) // returns 1
In ObjectScript, the absence of a value is usually indicated by an empty string (""). When this value is passed into embedded SQL, it is treated as a NULL value, as shown in the following example:
set x=""
set myquery="SELECT NULL As NoVal,:x As EmpStr"
set tStatement=##class(%SQL.Statement).%New()
set qStatus = tStatement.%Prepare(myquery)
if $$$ISERR(qStatus) {write "%Prepare failed:" do $System.Status.DisplayError(qStatus) quit}
set rset = tStatement.%Execute()
if (rset.%SQLCODE '= 0) {write "%Execute failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
while rset.%Next()
{
write "NoVal:",rset.%Get("NoVal")," length ",$LENGTH(rset.%Get("NoVal")),! // length 0
write "EmpStr:",rset.%Get("EmpStr")," length ",$LENGTH(rset.%Get("EmpStr")),! // length 0
}
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
write "End of data"
If you specify an input host variable that is not defined, embedded SQL treats its value as NULL.
In the following example, the SQL empty string with an appended blank is passed out as string of length 2:
&sql(SELECT %SQLUPPER('')
INTO :y )
WRITE !,"SQL empty string length: ",$LENGTH(y)
Arithmetic Operators and Functions
InterSystems SQL supports the following arithmetic operators:
Operator  Description 

+  Addition operator. For example, 17+7 equals 24. 
–  Subtraction operator. For example, 177 equals 10. Note that a pair of these characters is the InterSystems SQL comment indicator. Therefore, to specify two or more subtraction operators or negative signs you must use either spaces or parentheses. For example, 17 7 or 17(7) equals 24. 
*  Multiplication operator. For example, 17*7 equals 119. 
/  Division operator. For example, 17/7 equals 2.428571428571428571. 
\  Integer division operator. For example, 17\7 equals 2. 
#  Modulo operator. For example, 17 # 7 equals 3. Note that because the # character is also a valid identifier character, to use it as a modulo operator you should specify it separated from its operands by spaces before and after. 
E  Exponentiation (scientific notation) operator. Can be uppercase or lowercase. For example, 7E3 equals 7000. A toolarge exponent results in an SQLCODE 7 “Exponent out of range” error. For example, 1E309 or 7E308. 
()  Grouping operators. Used to nest arithmetic operations. Operations are executed according to ANSI operator precedence. For example, 17+7*2 equals 31, but (17+7)*2 equals 48. 
  Concatenate operator. For example, 177 equals 177. 
Arithmetic operations are performed on numbers in their canonical form.
Resulting Data Type
When performing an arithmetic operation on two numeric values having different data types, the resulting data type is determined as follows:
For addition (+), subtraction (), integer division (\), and modulo (#):
Data Type  NUMERIC  INTEGER  TINYINT  SMALLINT  BIGINT  DOUBLE 

NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
INTEGER  NUMERIC  BIGINT  BIGINT  BIGINT  BIGINT  DOUBLE 
TINYINT  NUMERIC  BIGINT  SMALLINT  INTEGER  BIGINT  DOUBLE 
SMALLINT  NUMERIC  BIGINT  INTEGER  INTEGER  BIGINT  DOUBLE 
BIGINT  NUMERIC  BIGINT  BIGINT  BIGINT  BIGINT  DOUBLE 
DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE 
For multiplication (*) or division (/):
Data Type  NUMERIC  INTEGER  TINYINT  SMALLINT  BIGINT  DOUBLE 

NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
INTEGER  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
TINYINT  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
SMALLINT  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
BIGINT  NUMERIC  NUMERIC  NUMERIC  NUMERIC  NUMERIC  DOUBLE 
DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE  DOUBLE 
Concatenating two numbers of any data type results in a VARCHAR string.
In Dynamic SQL you can use SQL column metadata to determine the data type of a result set field. For further details on numeric data types refer to SQL Data Types.
Operator Precedence
The SQL92 standard is imprecise with regard to operator precedence; assumptions on this matter differ amongst SQL implementations. InterSystems SQL can be configured to support either type of precedence:

At InterSystems IRIS 2019.1 and subsequent, InterSystems SQL supports ANSI precedence of arithmetic operators by default. This is a systemwide configuration setting. When ANSI precedence is configured, the "*", "\", "/", and "#" operators have a higher precedence than the "+", "", and "" operators. Operators with a higher precedence are executed before operators with a lower precedence. Thus, 3+3*5 equals 18. You can use parentheses to override precedence when desired. Thus, (3+3)*5 equals 30.
Default ANSI precedence is supported for a clean install of InterSystems IRIS 2019.1; when you upgrade InterSystems IRIS 2018.1 to InterSystems IRIS 2019.1, the operator precedence remains configured to the InterSystems IRIS 2018.1 default: strict lefttoright order.

At InterSystems IRIS 2018.1, InterSystems SQL does not provide precedence of arithmetic operators by default. By default, InterSystems SQL executes arithmetic expressions in strict lefttoright order, with no operator precedence. This is the same convention used in ObjectScript. Thus, 3+3*5 equals 30. You can use parentheses to enforce the desired precedence. Thus, 3+(3*5) equals 18. Careful developers should use parentheses to explicitly state their intentions.
You can configure either type of SQL operator precedence systemwide using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",1,.oldval) sets ANSI precedence; SET status=$SYSTEM.SQL.Util.SetOption("ANSIPrecedence",0,.oldval) sets strict lefttoright evaluation. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab which displays the Apply ANSI operator precedence option. Changing this SQL option takes effect immediately systemwide. Changing this option causes all cached queries to be purged systemwide.
Changing SQL precedence has no effect on ObjectScript. ObjectScript always follows strict lefttoright execution of arithmetic operators.
Precision and Scale
The precision (maximum number of digits present in the number) for a NUMERIC result for:

addition or subtraction is determined using the following algorithm: resultprecision=max(scale1, scale2) + max(precision1–scale1, precision2–scale2)+1. If the calculated resultprecision is greater than 36, the precision value is set to 36.

multiplication is determined using the following algorithm: resultprecision=min(36, precision1+precision2+1).

division (value1 / value2) is determined using the following algorithm: resultprecision=min(36, precision1–scale1+scale2+max(6, scale1+precision2+1)).
The scale (maximum number of fractional digits) for a NUMERIC result for:

addition or subtraction is determined using the following algorithm: resultscale=max(scale1, scale2).

multiplication is determined using the following algorithm: resultscale=min(17, scale1+scale2).

division (value1 / value2) is determined using the following algorithm: resultscale=min(17, max(6, scale1+precision2+1)).
For further details on data types, precision, and scale, refer to SQL Data Types.
Arithmetic and Trigonometric Functions
InterSystems SQL supports the following arithmetic functions:
Function  Description 

ABS  Returns the absolute value of a numeric expression. 
CEILING  Returns the smallest integer greater than or equal to a numeric expression. 
EXP  Returns the log exponential (base e) value of a numeric expression. 
FLOOR  Returns the largest integer less than or equal to a numeric expression. 
GREATEST  Returns the largest number from a commaseparated list of numbers. 
ISNUMERIC  Returns a boolean code specifying whether an expression is a valid number. 
LEAST  Returns the smallest number from a commaseparated list of numbers. 
LOG  Returns the natural log (base e) value of a numeric expression. 
LOG10  Returns the base–10 log value of a numeric expression. 
MOD  Returns the modulus value (remainder) of a division operation. Same as the # operator. 
PI  Returns the numeric constant pi. 
POWER  Returns the value of a numeric expression raised to a specified power. 
ROUND  Returns a numeric expression rounded (or truncated) to a specified number of digits. 
SIGN  Returns a numeric code specifying whether a numeric expression evaluates to positive, zero, or negative. 
SQRT  Returns the square root of a numeric expression. 
SQUARE  Returns the square of a numeric expression. 
TRUNCATE  Returns a numeric expression truncated to a specified number of digits. 
InterSystems SQL supports the following trigonometric functions:
Function  Description 

ACOS  Returns the arccosine of a numeric expression. 
ASIN  Returns the arcsine of a numeric expression. 
ATAN  Returns the arctangent of a numeric expression. 
COS  Returns the cosine of a numeric expression. 
COT  Returns the cotangent of a numeric expression. 
SIN  Returns the sine of a numeric expression. 
TAN  Returns the tangent of a numeric expression. 
InterSystems SQL also supports the following angle conversion functions:
Function  Description 

DEGREES  Converts radians to degrees. 
RADIANS  Converts degrees to radians. 
Relational Operators
A conditional expression evaluates to a boolean value. A conditional expression can use the following relational operators:
Operator  Description 

=  Equals operator. 
!=
<> 
Does not equal operator. The two syntactical forms are functionally identical. 
<  Less than operator. 
>  Greater than operator. 
<=  Less than or equal to operator. 
>=  Greater than or equal to operator. 
When comparing a table field value, these equality operators use the field’s default collation. The InterSystems IRIS default is not casesensitive. When comparing two literals, the comparison is casesensitive.
Equality operators (equals, does not equal) should be avoided when comparing floating point numbers. Floating point numbers (data types classes %Library.DecimalOpens in a new tab and %Library.DoubleOpens in a new tab) are stored as binary values, not as fixedprecision numbers. During conversion, rounding operations may result in two floating point numbers that are intended to represent the same number not being precisely equal. Use lessthan / greaterthan tests to determine if two floating point numbers are “the same” to the desired degree of precision.
Contains and Follows Operators
InterSystems SQL also supports the Contains and Follows comparison operators:
Operator  Description 

[  Contains operator. Returns all values that contain the operand, including values equal to the operand. This operator uses EXACT (casesensitive) collation. The inverse is NOT[. 

The Contains operator determines if a value contains a specified character or string of characters. It is casesensitive.

The %STARTSWITH predicate condition determines if a value starts with a specified character or string of characters. It is not casesensitive.

InterSystems SQL Search can be used to determine if a value contains a specified word or phrase. SQL Search performs contextaware matching. It is not casesensitive.
Operator  Description 

]  Follows operator. Returns all values that follow the operand in collation sequence. Excludes the operand value itself. This operator uses the field’s default collation. The InterSystems IRIS default is not casesensitive. The inverse is NOT]. 
For example, SELECT Age FROM MyTable WHERE Age ] 88 returns 89 and greater, but also returns 9 because 9 is after 88 in the collation sequence. SELECT Age FROM MyTable WHERE Age > 88 returns 89 and greater; it does not return 9. A string operand such as ‘ABC’ collates before any string that contains additional characters, such as ‘ABCA’; therefore, to exclude the operand string from a ] operator or a > operator you must specify the entire string. Name ] ‘Smith,John’ excludes ‘Smith,John’ but not ‘Smith,John P.’
Logical Operators
SQL logical operators are used in condition expressions that are evaluated as being True or False. These conditional expressions are used in the SELECT statement WHERE and HAVING clauses, in the CASE statement WHEN clauses, in the JOIN statement ON clause, and the CREATE TRIGGER statement WHEN clause.
NOT Unary Operator
You can use the NOT unary logical operator to specify the logical inverse of a condition, as shown in the following examples:
SELECT Name,Age FROM Sample.Person
WHERE NOT Age>21
ORDER BY Age
SELECT Name,Age FROM Sample.Person
WHERE NOT Name %STARTSWITH('A')
ORDER BY Name
You can place the NOT operator before the condition (as shown above). Or you can place NOT immediately before a singlecharacter operator; for example, NOT<, NOT[, and so forth. Note that there must be no space between NOT and the singlecharacter operator it inverts.
AND and OR Operators
You can use the AND and OR logical operators between two operands in a series of two or more conditions. These logical operators can be specified by keyword or symbol:
Operator  Description 

AND  & 
OR  ! 
Spaces are not required (though recommended for readability) between a symbol operator and its operand. Spaces are required before and after a keyword operator.
These logical operators can be used with the NOT unary logical operator, such as the following: WHERE Age<65 & NOT Age=21.
The following two examples use logical operators to schedule an assessment based on age. People between the ages of 20 and 40 are assessed every three years, people from 40 to 64 are assessed every two years, and those 65 and over are assessed every year. The examples give identical results; the first example uses keywords, the second uses symbols:
SELECT Name,Age FROM Sample.Person
WHERE Age>20
AND Age<40 AND (Age # 3)=0
OR Age>=40 AND (Age # 2)=0
OR Age>=65
ORDER BY Age
SELECT Name,Age FROM Sample.Person
WHERE Age>20
& Age<40 & (Age # 3)=0
! Age>=40 & (Age # 2)=0
! Age>=65
ORDER BY Age
Logical operators can be grouped using parentheses. This establishes a grouping level; evaluation proceeds from the lowest grouping level to the highest. In the first of the following examples, the AND condition is applied only to the second OR condition. It returns persons of any age from MA, and persons with age less than 25 from NY:
SELECT Name,Age,Home_State FROM Sample.Person
WHERE Home_State='MA' OR Home_State='NY' AND Age < 25
ORDER BY Age
Using parentheses to group conditions gives a different result. The following example returns persons from MA or NY whose age is less than 25:
SELECT Name,Age,Home_State FROM Sample.Person
WHERE (Home_State='MA' OR Home_State='NY') AND Age < 25
ORDER BY Age

SQL execution uses shortcircuit logic. If a condition fails, the remaining AND conditions will not be tested. If a condition succeeds, the remaining OR conditions will not be tested.

However, because SQL optimizes WHERE clause execution, the order of execution of multiple conditions (at the same grouping level) cannot be predicted and should not be relied upon.
Comments
InterSystems SQL supports both singleline comments and multiline comments. Comment text can contain any characters or strings, except, of course, the character(s) that indicate the end of the comment.
Using Embedded SQL marker syntax (&sql<marker>(...)<reversemarker>) imposes a restriction on the contents of SQL comments. If you are using marker syntax, the comments within the SQL code may not contain the character sequence “)<reversemarker>”. For further details, refer to The &sql Directive in the “Using Embedded SQL” chapter of this manual.
You can use the preparse()Opens in a new tab method to return an SQL DML statement stripped of comments. The preparse() method also replaces each query argument with a ? character and returns a %List structure of these arguments. The preparse() method in the following example returns a parsed version of the query, stripped of singleline and multiline comments and whitespace:
SET myq=4
SET myq(1)="SELECT TOP ? Name /* first name */, Age "
SET myq(2)=" FROM Sample.MyTable  this is the FROM clause"
SET myq(3)=" WHERE /* various conditions "
SET myq(4)="apply */ Name='Fred' AND Age > 21  end of query"
DO ##class(%SQL.Statement).preparse(.myq,.stripped,.args)
WRITE stripped,!
WRITE $LISTTOSTRING(args)
Single Line Comments
A singleline comment is specified by a twohyphen prefix. A comment can be on a separate line, or can appear on the same line as SQL code. When a comment follows SQL code on the same line, at least one blank space must separate the code from the doublehyphen comment operator. A comment can contain any characters, including hyphens, asterisks, and slashes. The comment continues to the end of the line.
The following example contains multiple singleline comments:
 This is a simple SQL query
 containing  (double hyphen) comments
SELECT TOP 10 Name,Age,  Two columns selected
Home_State  A third column
FROM Sample.Person  Table name
 Other clauses follow
WHERE Age > 20 AND  Comment within a clause
Age < 40
ORDER BY Age,  Comment within a clause
Home_State
 End of query
Multiple Line Comments
A multipleline comment is specified by a /* opening delimiter and a */ closing delimiter. A comment can appear on one or more separate lines, or can begin or end on the same line as SQL code. A comment delimiter should be separated from SQL code by at least one blank space. A comment can contain any characters, including hyphens, asterisks and slashes, with the obvious exception of the */ character pair.
The syntax /*#OPTIONS */, with no space between the /* and the #, specifies a comment option. A comment option is not a comment; it specifies a code option that the query optimizer uses during the compile of the SQL query. A comment option is specified using JSON syntax, commonly a key:value pair such as the following: /*#OPTIONS {"optionName":value} */.
The following example contains several multipleline comments:
/* This is
a simple
SQL query. */
SELECT TOP 10 Name,Age /* Two fields selected */
FROM Sample.Person /* Other clauses
could appear here */ ORDER BY Age
/* End of query */
When commenting out Embedded SQL code, always begin the comment before the &sql directive or within the parentheses. The following example correctly comments out two the Embedded SQL code blocks:
SET a="default name",b="default age"
WRITE "(not) Invoking Embedded SQL",!
/*&sql(SELECT Name INTO :a FROM Sample.Person) */
WRITE "The name is ",a,!
WRITE "Invoking Embedded SQL (as a noop)",!
&sql(/* SELECT Age INTO :b FROM Sample.Person */)
WRITE "The age is ",b
SQL Code Retained as Comments
Embedded SQL statements can be retained as comments in the .INT code version of routines. This is done systemwide by setting the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method, as follows: SET status=$SYSTEM.SQL.Util.SetOption("RetainSQL",1,.oldval). To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays the Retain SQL as Comments setting. The default is 1 (“Yes”).
Set this option to “Yes” to retain SQL statements as comments in the .INT code version of a routine. Setting this option to “Yes” also lists all non% variables used by the SQL statements in the comment text. These listed variables should also be listed in the ObjectScript procedure’s PUBLIC variable list and reinitialized using the NEW command. For further details, refer to Host Variables in the “Embedded SQL” chapter of this manual.