Language Elements

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 non-NULL value.

The COALESCE function selects the first non-NULL value from supplied data. It treats empty string values as non-NULL.

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 FuncCatNull,
       'fred'||NULL AS OpCatNull 

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, 
       LENGTH('') AS EmpStrLen 

As shown in this example, the SQL LENGTH function returns the SQL lengths.

You can convert an SQL zero-length string to a NULL by using the ASCII function, as shown in the following example:

SELECT LENGTH('') AS EmpStrLen,
       LENGTH({fn ASCII('')}) As NullLen

However, certain Caché extensions to standard SQL treat the length of NULL and the empty string differently. The $LENGTH function returns the Caché 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 NullLenZero, 
$LENGTH('') AS EmpStrLenZero, 
$LENGTH('a') AS StrLenOne, 
$LENGTH(CHAR(0)) AS CharLenZero  

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 BlankLenOne,
CHAR_LENGTH(%STRING('')) AS EmpStrAndBlankLenTwo

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 zero-length 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 qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset=tStatement.%Execute()
  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
  }
  WRITE "End of data"

If you specify an input host variable that is not defined, embedded SQL treats its value as NULL.

When passing a NULL or empty string value out from embedded SQL to ObjectScript, the NULL is translated to a string of length 0, and the empty string is translated to a string of length 1. This is shown in the following example:

   &sql(SELECT 
        NULL,
        ''
        INTO :a,:b)
  WRITE !,"The length of NULL is: ",$LENGTH(a)         // length 0
  WRITE !,"The length of empty string is: ",$LENGTH(b) // length 1

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)

Operator Precedence

The SQL-92 standard is imprecise with regard to operator precedence; assumptions on this matter differ amongst SQL implementations.

• Caché SQL, by default, does not provide precedence of arithmetic operators. By default, Caché SQL executes arithmetic expressions in strict left-to-right 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.

• Caché SQL can be configured to support ANSI precedence of arithmetic operators. This is a system-wide 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.

  You can configure precedence using the Management Portal. In the General SQL Settings, you select the Apply ANSI Operator Precedence check box, then press the Save button. Changing this SQL option takes effect immediately system-wide. Changing this option causes all cached queries to be purged system-wide.

  You can also configure precedence using the $SYSTEM.SQL.SetANSIPrecedence()Opens in a new tab method.

  Changing SQL precedence has no effect on ObjectScript. ObjectScript always follows strict left-to-right 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

Caché SQL supports the following arithmetic functions:

Caché SQL supports the following trigonometric functions.

Contains and Follows Operators

Caché SQL also supports the Contains and Follows comparison operators:

• The Contains operator determines if a value contains a specified character or string of characters. It is case-sensitive.

• The %STARTWITH predicate condition determines if a value starts with a specified character or string of characters. It is not case-sensitive.

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.’

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 single-character operator; for example, NOT<, NOT[, and so forth. Note that there must be no space between NOT and the single-character 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:

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 short-circuit 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.

Single Line Comments

A single-line comment is specified by a two-hyphen 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 double-hyphen 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 single-line 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 multiple-line 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 following example contains several multiple-line 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 no-op)",!
   &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 by setting a configuration option as follows:

• Invoke the $SYSTEM.SQL.SetRetainSQL()Opens in a new tab method. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab, which displays the Retain SQL as Comments setting.

• Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings (System, Configuration, General SQL Settings). On this screen you can view the current setting of Retains SQL Statements as Comments in .INT Code. The default is “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 re-initialized using the NEW command. For further details, refer to Host Variables in the “Embedded SQL” chapter of this manual.