User's Guide

Part VI. SQL Anywhere Reference

Chapter 40. Watcom-SQL Language Reference

Syntax

     expression:
          constant
          | [correlation-name .] column-name
          | variable-name
          | function-name ( expression, ... )
          | - expression
          | expression+ expression
          | expression - expression
          | expression * expression
          | expression / expression
          | expression + expression
          | expression || expression
          | ( expression )
          | ( subquery )
          | CAST ( expression AS data-type )
          | if-expression
          | case-expression

     constant:
          integer
          | number
          | 'string'
          | special-constant
          | host-variable

     special-constant:
          CURRENT DATE
          | CURRENT TIME
          | CURRENT TIMESTAMP
          | NULL
          | SQLCODE
          | SQLSTATE
          | USER

     if-expression:
          IF condition THEN expression [ ELSE expression ] ENDIF

     case-expression:
          CASE conditionWHENexpressionTHEN,... [ ELSE expression ] END
          | CASE WHENsearch-condtionTHENexpression,... [ ELSE expression ] END

Usage

Anywhere.

Authorization

Must be connected to the database.

Side effects

None.

See also

• Search conditions
• "SQL Anywhere Data Types"

Description

Expressions are formed from several different kinds of element, discussed in the following sections.

Constants in expressions

Constants are numbers or strings. String constants are enclosed in apostrophes ('single quotes'). An apostrophe is represented inside the string by two apostrophes in a row.

There are several special constants:

• CURRENT DATE The current year, month and day represented in the DATE data type.
• CURRENT TIME The current hour, minute, second and fraction of a second represented in the TIME data type. Although the fraction of a second is stored to 6 decimal places, the current time is limited by the accuracy of the system clock.

  Under DOS and Windows, the clock is only accurate to approximately 1/18th of a second rounded to two decimal places. Under QNX, the clock is accurate to the nearest microsecond.

• CURRENT TIMESTAMP Combines CURRENT DATE and CURRENT TIME to form a TIMESTAMP value containing year, month, day, hour, minute, second and fraction of a second. Like CURRENT TIME, the accuracy of the fraction of a second is limited by the system clock.

  In embedded SQL, a host variable can also be used in an expression wherever a constant is allowed.

• NULL The NULL value (see "NULL value").
• SQLCODE Current SQLCODE value (see the chapter "SQL Anywhere Database Error Messages").
• SQLSTATE Current SQLSTATE value (see the chapter "SQL Anywhere Database Error Messages").
• CURRENT USER A string containing the user ID of the current connection.
• CURRENT PUBLISHER A string containing publisher user ID of the database for SQL Remote replications.
• LAST USER For INSERTS, this constant has the same effect as CURRENT USER. For UPDATES, if a column with a default value of LAST USER is not explicitly altered, it is changed to the name of the current user. In this way, the LAST USER default indicates the user ID of the user who last modified the row.

  When combined with the CURRENT TIMESTAMP, a default value of LAST USER can be used to record (in separate columns) both the user and the date and time a row was last changed..

In Embedded SQL, a host variable can also be used in an expression wherever a constant is allowed.

Column names in expressions

A column name is an identifier preceded by an optional correlation name. (A correlation name is usually a table name. For more information on correlation names, see "FROM clause".) If a column name has characters other than letters, digits and underscore, it must be surrounded by quotation marks (""). For example, the following are valid column names:

     employee.name

     address

     "date hired"

     "salary"."date paid"

  See "Watcom-SQL language elements" for a complete description of identifiers.

Watcom-SQL variables

SQL Anywhere supports three levels of variables:

• Local variables are defined inside a compound statement in a procedure or batch using the DECLARE statement. They exist only inside the compound statement.
• Connection-level variables are defined with a CREATE VARIABLE statement. They belong to the current connection, and disappear when you disconnect from the database or when you use the DROP VARIABLE statement.
• Global variables are SQL Anywhere-supplied variables that have system-supplied values.

Local variables

Local variables are declared using the DECLARE statement, which can be used only within a compound statement (that is, bracketed by the BEGIN and END keywords). The variable is initially set as NULL. The value of the variable can be set using the SET statement, or can be assigned using a SELECT statement with an INTO clause.

Local variables can be passed as arguments to procedures, as long as the procedure is called from within the compound statement.

The following batch of SQL statements illustrates the use of local variables.

     BEGIN
         DECLARE local_var INT ;
         SET local_var = 10 ;
         MESSAGE 'local_var = ', local_var ;
     END

Running this batch from ISQL gives the message

local_var = 10

The variable local_var does not exist outside the compound statement in which it is declared. The following batch is invalid, and gives a

column not found

     -- This batch is invalid.
     BEGIN
         DECLARE local_var INT ;
         SET local_var = 10 ;
         MESSAGE 'local_var = ', local_var ;
     END;
     MESSAGE 'local_var = ', local_var ;

The following example illustrates the use of SELECT with an INTO clause to set the value of a local variable:

     BEGIN
         DECLARE local_var INT ;
         SELECT 10 INTO local_var ;
         MESSAGE 'local_var = ', local_var ;
     END

Running this batch from ISQL gives the message

local_var = 10

Connection-level variables

Connection-level variables are declared with the CREATE VARIABLE statement. The CREATE VARIABLE statement can be used anywhere except inside compound statements. Connection-level variables can be passed as parameters to procedures.

When a variable is created it is initially set to NULL. The value of connection-level variables can be set in the same way as local variables, using the SET statement or using a SELECT statement with an INTO clause. The following batch of SQL statements illustrates the use of connection-level variables.

     CREATE VARIABLE con_var INT;
     SET con_var = 10;
     MESSAGE 'con_var = ', con_var;

Running this batch from ISQL gives the message

local_var = 10

Connection-level variables exist until the connection is terminated, or until the variable is explicitly dropped using the DROP VARIABLE statement. The following statement drops the variable con_var:

     DROP VARIABLE con_var

Global variables

Global variables are SQL Anywhere-supplied variables that have values set by the SQL Anywhere engine. For example, the global variable @@version has a value that is the current version number of the database engine.

Predefined global variables are distinguished from local and connection-level variables by having two @ signs preceding their names. For example, @@error, @@rowcount are global variables. Users cannot create global variables, and cannot update the value of global variables directly.

The special constants available in SQL Anywhere, such as CURRENT DATE, CURRENT TIME, USER, SQLSTATE and so on are similar to, but not identical to, global variables. The special constants can be used as defaults for columns; global variables cannot.

The following statement retrieves a value of the version global variable.

     SELECT @@version

In procedures and triggers, global variables can be selected into a variable list. The following procedure returns the engine version number in the ver parameter.

     CREATE PROCEDURE VersionProc ( OUT ver
                     NUMERIC ( 5, 2 ) )
     BEGIN
         SELECT @@version
         INTO ver;
     END

In Embedded SQL, global variables can be selected into a host variable list.

The following table lists the global variables available in SQL Anywhere

Variable name	Meaning
@@error	Commonly used to check the error status (succeeded or failed) of the most recently executed statement. It contains 0 if the previous transaction succeeded; otherwise, it contains the last error number generated by the system. A statement such as if @@error != 0 return causes an exit if an error occurs. Every SQL statement resets @error, so the status check must immediately follow the statement whose success is in question.
@@identity	Last value inserted into an IDENTITY column by an insert or select into statement. @@identity is reset each time a row is inserted into a table. If a statement inserts multiple rows, @@identity reflects the IDENTITY value for the last row inserted. If the affected table does not contain an IDENTITY column, @@ identity is set to 0. The value of @@identity is not affected by the failure of an insert or select into statement, or the rollback of the transaction that contained it. @@identity retains the last value inserted into an IDENTITY column, even if the statement that inserted it fails to commit.
@@isolation	Current isolation level. @@isolation takes the value of the active level.
@@procid	Stored procedure ID of the currently executing procedure.
@@rowcount	Number of rows affected by the last statement. @@rowcount is set to zero by any statement which does not return rows, such as an if statement. With cursors, @@rowcount represents the cumulative number of rows returned from the cursor result set to the client, up to the last fetch request.
@@servername	Name of the current database server.
@@sqlstatus	Contains status information resulting from the last fetch statement.
@@version	Version of the current version of SQL Anywhere.

Functions in expressions

See "Watcom-SQL Functions" for a description of the functions available in SQL Anywhere.

Subqueries in expressions

A subquery is a SELECT statement enclosed in parentheses. The SELECT statement must contain one and only one select list item. Usually, the subquery is allowed to return only one row. See "Search conditions" for other uses of subqueries. A subquery can be used anywhere that a column name can be used. For example, a subquery can be used in the select list of another SELECT statement.

Watcom-SQL Operators

This section describes the arithmetic and string operators available in SQL Anywhere. For information on comparison operators, see the section "Search conditions".

The normal precedence of operations applies. Expressions in parentheses are evaluated first; then multiplication and division before addition and subtraction. String concatenation happens after addition and subtraction.

expression + expression Addition. If either expression is the NULL value, the result is the NULL value.

expression - expression Subtraction. If either expression is the NULL value, the result is the NULL value.

- expression Negation. If the expression is the NULL value, the result is the NULL value.

expression * expression Multiplication. If either expression is the NULL value, the result is the NULL value.

expression / expression Division. If either expression is the NULL value or if the second expression is 0, the result is the NULL value.

expression || expression String concatenation (two vertical bars). If either string is the NULL value, it is treated as the empty string for concatenation.

expression + expression Alternative string concatenation. When using the + concatenation operator, you must ensure the operands are explicitly set to character data types rather than relying on implicit data conversion.

( expression ) Parentheses.

IF condition THEN expression1 [ELSE expression2] ENDIF Evaluates to the value of expression1 if the specified search condition is TRUE, the value of expression2 if condition is FALSE, and the NULL value if condition is UNKNOWN. (For more information about TRUE, FALSE and UNKNOWN conditions, see "NULL value", and "Search conditions".)

CASE expression WHEN expression THEN expression,... [ ELSE expression ] END When the expression following the CASE statement is equal to the expression following the WHEN statement, then the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is return, if it exists.

For example, the following code uses a case expression as the second clause in a SELECT statement and returns a list using the Products table from the sample database:

     SELECT id,
         ( CASE name
             WHEN 'Tee Shirt' then 'Shirt'
             WHEN 'Sweatshirt' then 'Shirt'
             WHEN 'Baseball Cap' then 'Hat'
             ELSE 'Unknown'
         END ) as Type
     FROM "DBA".Product

CASE WHEN search-condition THEN expression,... [ ELSE expression ] END When the search-condition following the WHEN statement is satisfied, the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is return, if it exists.

For example, the following statement uses a case expression as the third clause of a SELECT statement to associate a string with a search-condition.

     SELECT id, name,
         ( CASE
             WHEN name='Tee Shirt' then 'Sale'
             WHEN quantity >= 50 then 'Big Sale'
             ELSE 'Regular price'
         END ) as Type
     FROM "DBA".Product