InterSystems TSQL Language Elements
This chapter describe the following TSQL language elements for InterSystems IRIS® data platform:
A string literal must be delimited by quote characters. The preferred delimiter characters are single quote characters. You can also use double quote characters as string delimiters if you specify SET DELIMITED_IDENTIFER OFF. Otherwise, double quote characters are parsed as delimiting an identifier.
If you delimit a string literal with single quote characters, you can include literal double quote characters within the string. To include a literal single quote character within the string, double it by typing two single quotes.
A string containing literal single quotes, such as 'this is an ''embedded'' string', is compiled by InterSystems IRIS to single quotes within double quotes: "this is an 'embedded' string".
When migrating Transact-SQL code to InterSystems TSQL, it may be necessary to redefine the empty string. You can do this by setting the following InterSystems IRIS system global:
All of these specified values are keyword literals, except nspace, which is a namespace name specified as a quoted string.
Changing the empty string definition should be done with extreme caution. It can result in data containing different representations for an empty string. It can also cause existing programs to fail when executed in this namespace. After defining the empty string, you must purge all cached queries and recompile all classes and routines for that namespace that use the former empty string definition.
The following ObjectScript example changes the empty string definition for the SAMPLES namespace. It first sets the empty string value to a single blank space. It then sets the empty string value to the non-printing character represented by the ASCII code 0. (This example then immediately resets the empty string value to the InterSystems IRIS default):
SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")=" " WRITE !,"Empty string set to:" ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string") SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")=$CHAR(0) WRITE !,"Empty string set to:" ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string") SET ^%SYS("sql","sys","namespace","SAMPLES","empty string")="" WRITE !,"Empty string reset to:" ZZDUMP ^%SYS("sql","sys","namespace","SAMPLES","empty string") WRITE !,!,"End of sample program"
In TSQL a NULL supplied to a boolean operation returns as FALSE, as shown in the following example:
DECLARE @var BINARY(1) SELECT @var=NULL IF @var PRINT "true" ELSE PRINT "false"
In Sybase dialect, NULL is equal to NULL. A NULL=NULL comparison returns TRUE, and a NULL != NULL comparison returns FALSE.
In MSSQL dialect, a comparison of NULL with any value returns FALSE. Thus NULL=NULL and NULL != NULL comparisons both return FALSE.
DECLARE @var BINARY(1) SELECT @var=NULL IF @var=NULL PRINT "true" ELSE PRINT "false"
In Sybase dialect, NULL is not equal to any value. Therefore, Not Equals (!=) comparison involving NULL and any boolean, numeric, or string value (including the empty string ("")) returns TRUE. All Equals (=), Greater Than (>) or Less Than (<) comparisons return FALSE.
In MSSQL dialect, NULL cannot be compared to a value. Thus all Equals (=), Not Equals (!=), Greater Than (>) or Less Than (<) comparisons return FALSE.
In a TSQL string concatenation operation, NULL is equivalent to an empty string. In a TSQL arithmetic operation, NULL is equivalent to 0.
InterSystems TSQL automatically converts hexadecimal numeric literals in TSQL source code to the corresponding decimal (base-10) numeric literals.
InterSystems TSQL cannot use as identifiers the SQL Server reserved words. InterSystems TSQL can use InterSystems SQL reserved words (that are not also SQL Server reserved words) if the QUOTED_IDENTIFIER SQL configuration setting is set to Yes.
Comments, Blank Lines, and Semicolons
InterSystems TSQL supports both single-line and multi-line comments.
A single line comment continues to the rest of the line. When used in the TSQL shell, a comment does not encompass the end-of-line qualifier, such as /x or /c. InterSystems TSQL supports both –– and // as single-line comment delimiters.
A multi-line comment begins with /* and ends with */. A comment can include nested /* ... */ comments.
PRINT 'these are comments' -- this is a single-line comment // this is a single-line comment /* This is a multi-line comment The command PRINT 'do not print' is part of the comment and is not executed */
InterSystems TSQL provides the means to include executable statements within InterSystems IRIS TSQL code which are parsed as nonexecutable comments in Transact-SQL. A statement prefixed with two hyphens and a vertical bar is parsed by InterSystems IRIS as an executable statement. Sybase Adaptive Server and Microsoft SQL Server consider this to be a Transact-SQL comment.
PRINT 'any context' -- PRINT 'commented out' --| PRINT 'InterSystems only'
You can specify a blank line by using either two hyphens or a semicolon.
A semicolon either before or after a TSQL statement is ignored. They are supported for compatibility with Transact-SQL code, such as stored procedures, that ends statements with a semicolon.
PRINT 'no semicolon' -- PRINT 'trailing semicolon'; ; ;PRINT 'leading semicolon'
A local variable name must be a valid identifier. An at sign (@) prefix to an identifier specifies that it is the name of a local variable.
Case-sensitivity differs for TSQL dialects:
Sybase: local variable names are case-sensitive.
MSSQL: local variable names are not case-sensitive.
InterSystems IRIS local variable names are case-sensitive.
An identifier is a name for a TSQL object, such as a table, column, view, key, index, trigger, or stored procedure. Naming conventions for identifiers are as follows:
The first character of an identifier must be a letter, an underscore (_) or a percent (%) character.
Subsequent characters of an identifier may be letters, numbers, underscores (_), dollar signs ($), or pound signs (#).
Identifiers can be of any length, but must be unique within their first 30 characters.
Identifiers are not case-sensitive.
An identifier cannot be an SQL reserved word.
A pound sign (#) prefix to an identifier specifies that it is the name of a temporary table.
An at sign (@) prefix to an identifier specifies that it is the name of a variable.
Some identifiers are qualified with a schema name. For example, schema.tablename or schema.storedprocedure. If the schema name is omitted, the identifier is unqualified. TSQL resolves unqualified identifiers by using either the system-wide default schema (for DDL) or the schemaPath property (for DML), which provides a search path of schemas to check for the specified table name or stored procedure name.
Delimited and Quoted Identifiers
A delimited identifier is not restricted by the naming conventions of ordinary identifiers. For example, a delimited identifier can be the same word as an SQL reserved word; a delimited identifier can contain space characters.
By default, both square brackets and double quotation marks can be used to delimit an identifier. These delimiters are interchangeable; you can define a delimited identifier by enclosing it with square brackets, and invoke the same delimited identifier by specifying it enclosed with double quotation marks.
You can specify a quoted identifier if the QUOTED_IDENTIFIER SQL configuration setting is set to Yes. You specify a quoted identifier by enclosing it in double quotation marks. When QUOTED_IDENTIFIER is on, double quotes are parsed as delimiting an identifier. When QUOTED_IDENTIFIER is off, double quotes are parsed as alternative delimiters for string literals. The preferable delimiters for string literals are single quotes. A quoted identifier can contain any characters, including blank spaces.
The following data types are supported for local variables and table columns. These data types are supported in that they are parsed as valid data types; however, no range or value validation is performed.
BINARY(n) and VARBINARY(n). The (n) size specification is mandatory.
CHAR and VARCHAR
CHAR(n), NCHAR(n), VARCHAR(n), and NVARCHAR(n)
VARCHAR(MAX), and NVARCHAR(MAX). By default, these map to %Stream.GlobalCharacterOpens in a new window.
DATETIME and SMALLDATETIME
DECIMAL, DECIMAL(p), and DECIMAL(p,s). Where p and s are integers specifying precision (total digits) and scale (decimal digits).
DOUBLE and DOUBLE PRECISION
FLOAT and FLOAT(n)
INT, BIGINT, SMALLINT, and TINYINT
MONEY and SMALLMONEY
NUMERIC, NUMERIC(p), and NUMERIC(p,s). Where p and s are integers specifying precision (total digits) and scale (decimal digits).
The Microsoft SQL Server TIMESTAMP data type is not used for date or time information. It is an integer counter of the number of times a record is inserted or updated in a table. It should not be confused with the InterSystems SQL and ODBC TIMESTAMP data type, which represents a date and time in YYYY-MM-DD HH:MM:SS.nnnnnnnnn format. In TSQL, use DATETIME and SMALLDATETIME for date and time values.
The following SQL Server data types are supported in a specific context:
NTEXT, TEXT By default, these map to %Stream.GlobalCharacterOpens in a new window.
The following are not implemented:
UNIQUEIDENTIFIER stored as a 16-byte binary string. Instead use VARCHAR(32) as the data type for a globally unique ID.
SQL92 and TSQL options
Arithmetic and Equality Operators
InterSystems TSQL supports + (addition), – (subtraction), * multiplication, / division, and % modulo arithmetic operators.
InterSystems TSQL supports the following equality and comparison operators:
= (equal to)
<> (not equal to) and != (not equal to)
< (less than), !< (not less than), <= (less than or equal to)
> (greater than), !> (not greater than), >= (greater than or equal to)
When performing equality comparisons (= or <>) between date values with different data types, all date and time values are compared using the TIMESTAMP data type. Thus two dates in different formats can be meaningfully compared. A date value declared as a STRING data type can be compared to a date value declared as a DATETIME data type.
InterSystems TSQL supports the + (plus sign) as both a concatenation operator and the addition operator. The plus sign functions as a concatenation operator with strings. You can concatenate several strings together using this operator. If all item are strings, TSQL performs concatenation; however, if one of the items is a number, TSQL performs addition, treating non-numeric strings as 0.
'world'+'wide'+'web' concatenates to 'worldwideweb'
'world'+'33'+'web' concatenates to 'world33web'
'world'+33+'web' performs addition (0+33+0=33)
In a TSQL string concatenation operation, NULL is equivalent to an empty string. In a TSQL arithmetic operation, NULL is equivalent to 0. Note that because the plus sign (+) is used for both concatenation and addition, the data type declaration of the NULL variable is critical. The following examples all return “bigdeal”:
DECLARE @var1 BINARY(1) DECLARE @var2 VARCHAR(10) SELECT @var1=NULL,@var2=NULL PRINT "big"+NULL+"deal" PRINT "big"+@var1+"deal" PRINT "big"+@var2+"deal"
The following example returns 0; it treats the + as an arithmetic operator and interprets the argument as 0 + 0 + 0 = 0:
DECLARE @var1 INT SELECT @var1=NULL PRINT "big"+@var1+"deal"
InterSystems TSQL also supports || as a concatenation operator.
InterSystems TSQL supports the BETWEEN range check operator of the form: BETWEEN num1 AND num2. BETWEEN is inclusive of the specified range limits.
InterSystems TSQL supports the IS NULL match operator. A variable is NULL if it has been declared but not assigned a value, or if it has been explicitly specified as NULL. The empty string is not NULL.
InterSystems TSQL supports the LIKE pattern match operator. LIKE performs not case-sensitive matching of letters. InterSystems TSQL also supports NOT LIKE.
NOT Logical Operator
The NOT logical operator inverts the truth value of the statement that follows it. For example, IF NOT EXISTS(...). NOT is not case-sensitive.
Bitwise Logical Operators
InterSystems TSQL supports the AND (&), OR (|), XOR (^), and NOT (~) bitwise operators for the integer data type. The decimal integers are converted to binary, the logical operation is performed, and the resulting binary is converted to a decimal integer value. The NOT (~) operator is a unary operator that inverts bits.