Collection Contents Index Statement elements Search conditions pdf/chap6.pdf

Reference Manual
   CHAPTER 6. SQL Language Elements     

Expressions

Syntax 

expression:
case-expression
| constant
| [correlation-name .] column-name [ java-ref ]
| - expression
| expression operator expression
| ( expression )
| function-name ( expression, ... )
| if-expression
| [ java-package-name.] java-class-name java-ref
| ( subquery )
| variable-name [ java-ref ]

Parameters 

case-expression:
{ CASE search-condition
... WHEN expression
... THEN,...
... [ ELSE expression ]
END
| CASE
... WHEN search-condition
... THEN expression,...
... [ ELSE expression ]
END
}

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

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

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

java-ref:
 .field-name [ java-ref ]
| >> field-name [ java-ref ]
| .method-name ( [expression,...] ) [ java-ref ]
| >> method-name ( [expression,...] ) [ java-ref ]

operator:
{ + | - | * | / | || | % }

Usage 

Anywhere.

Authorization 

Must be connected to the database.

Side effects 

None.

See also 

Search conditions

SQL Data Types

SQL Functions

SQL variables

Description 

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

For Info     For information on functions, see SQL Functions. For information on variables, see SQL variables.

Compatibility 

Top of page  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.

Special constants 

There are several special constants:

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

Top of page  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"

For Info     See Statement elements for a complete description of identifiers.

Top of page  Functions in expressions

See SQL Functions for a description of SQL functions.

Top of page  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.

Top of page  SQL Operators

This section describes arithmetic, string, and bitwise operators. 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.

Top of page  Arithmetic operators

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     Modulo finds the integer remainder after a division involving two whole numbers. For example, 21 % 11 = 10 because 21 divided by 11 equals 1 with a remainder of 10.

Top of page  String operators

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.

Standards and compatibility 

Top of page  Bitwise operators

The following operators can be used on bit data types, in both Adaptive Server Anywhere and Adaptive Server Enterprise.

Operator

Description

&

and

|

or

^

exclusive or

~

not

Top of page  Join operators

The Transact-SQL outer join operators *= and =* are supported in Adaptive Server Anywhere, in addition to the SQL/92 join syntax that uses a table expression in the FROM clause.

Compatibility 

Top of page  Operator precedence

When you use more than one operator in an expression, it is recommended that you make the order of operation explicit using parentheses rather than relying on an identical operator precedence between Adaptive Server Enterprise and Adaptive Server Anywhere.

Top of page  IF expressions

The syntax of the IF expression is as follows:

IF condition
THEN expression1
[ ELSE expression2 ]
ENDIF

This expression returns the following:

For Info     For more information about TRUE, FALSE and UNKNOWN conditions, see NULL value, and Search conditions.

IF statement is different from IF expression    
Do not confuse the syntax of the IF expression with that of the IF statement.

For information on the IF statement, see IF statement.

Top of page  CASE expressions

The CASE expression provides conditional SQL expressions. Case expressions can be used anywhere an expression can be used.

The syntax of the CASE expression is as follows:

CASE expression 
WHEN expression 
THEN expression,... 
[ ELSE expression ]
END

If 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 returned, if it exists.

For example, the following code uses a case expression as the second clause in a SELECT statement.

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

An alternative syntax is as follows:

CASE 
WHEN search-condition 
THEN expression,... 
[ ELSE expression ]
END

If 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 returned, 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

NULLIF function for abbreviated CASE expressions 

The NULLIF function provides a way to write some CASE statements in short form. The syntax for NULLIF is as follows:

NULLIF ( expression-1, expression-2 )

NULLIF compares the values of the two expressions. If the first expression equals the second expression, NULLIF returns NULL. If the first expression does not equal the second expression, NULLIF returns the first expression.

Top of page  Java expressions

The following kinds of Java expressions can be used as SQL expressions:

For Info     For more information on class and instance fields and methods, see A 15 minute Java seminar.

Referencing fields and methods 

When referencing a Java field or method from within Java code, you use the dot (.) operator. For example, to invoke the getConnection method of the DriverManager class you use the following:

conn = DriverManager.getConnection( temp.toString() , _props )

For Info    There are two ways of referencing Java fields or methods from within SQL statements. You can use either the dot operator or the >> operator.

For Info    The dot operator has the advantage that it looks like Java code, but has the disadvantage that in SQL the dot is also used to indicate the owner, table, and column hierarchy, so this could be confusing to read.

For Info    Using the dot operator, a name method of an object named Employee is invoked from SQL as follows:

select Employee.name ...

The same expression could refer to a name column of an Employee table.

The >> operator is unambiguous, but does not look like what Java programmers may expect.

Top of page  Compatibility of expressions

The following tables describe the compatibility of expressions and constants between Adaptive Server Enterprise and Adaptive Server Anywhere. These tables are a guide only, and a marking of Both may not mean that the expression performs in an identical manner for all purposes under all circumstances. For detailed descriptions, you should refer to the Adaptive Server Enterprise documentation and the Adaptive Server Anywhere documentation on the individual expression.

In the following table, expr represents an expression, and op represents an operator.

Expression

Supported by

constant

Both

column name

Both

variable name

Both

function (expr)

Both

- expr

Both

expr op expr

Both

( expr )

Both

( subquery )

Both

if-expression

Adaptive Server Anywhere only

Constant

Supported by

integer

Both

number

Both

'string'

Both

special-constant

Both

host-variable

Adaptive Server Anywhere

Default interpretation of delimited strings 

By default, Adaptive Server Enterprise and Adaptive Server Anywhere give different meanings to delimited strings: that is, strings enclosed in apostrophes (single quotes) and in quotation marks (double quotes).

Adaptive Server Anywhere employs the SQL/92 convention, that strings enclosed in apostrophes are constant expressions, and strings enclosed in quotation marks (double quotes) are delimited identifiers (names for database objects). Adaptive Server Enterprise employs the convention that strings enclosed in quotation marks are constants, while delimited identifiers are not allowed by default and are treated as strings.

Top of page  The quoted_identifier option

Both Adaptive Server Enterprise and Adaptive Server Anywhere provide a quoted_identifier option that allows the interpretation of delimited strings to be changed. By default, the quoted_identifier option is set to OFF in Adaptive Server Enterprise, and to ON in Adaptive Server Anywhere.

You cannot use SQL reserved words as identifiers if the quoted_identifier option is off.

For Info     For a complete list of reserved words, see Alphabetical list of keywords.

Setting the option 

While the Transact-SQL SET statement is not supported for most Adaptive Server Enterprise connection options, it is supported for the quoted_identifier option.

The following statement in either Adaptive Server Anywhere or Adaptive Server Enterprise changes the setting of the quoted_identifier option to ON:

SET quoted_identifier ON

With the quoted_identifier option set to ON, Adaptive Server Enterprise allows table, view, and column names to be delimited by quotes. Other object names cannot be delimited in Adaptive Server Enterprise.

The following statement in Adaptive Server Anywhere or Adaptive Server Enterprise changes the setting of the quoted_identifier option to OFF:

SET quoted_identifier OFF

Compatible interpretation of delimited strings 

You can choose to use either the SQL/92 or the default Transact-SQL convention in both Adaptive Server Enterprise and Adaptive Server Anywhere as long as the quoted_identifier option is set to the same value in each DBMS.

Examples 

If you choose to operate with the quoted_identifier option on (the default Adaptive Server Anywhere setting), then the following statements involving the SQL keyword user are valid for both DBMS's.

CREATE TABLE "user" (
   col1 char(5)
) ;
INSERT "user" ( col1 )
VALUES ( 'abcde' ) ;

If you choose to operate with the quoted_identifier option off (the default Adaptive Server Enterprise setting), then the following statements are valid for both DBMSs.

SELECT *
FROM employee
WHERE emp_lname = "Chin"
Top of page  

Collection Contents Index Statement elements Search conditions pdf/chap6.pdf