APPX Software Library

Chapter 4: SQL Language Elements

4.6 Expressions

Description

An expression is a symbol or string of symbols used to represent or calculate a single value in an SQL statement. When you specify an expression in a statement, SQL retrieves or calculates the value represented by the expression and uses that value when it executes the statement.

Expressions are also called scalar expressions or value expressions.

Syntax

expr ::
    [ { table_name | alias } . ] column-name
|   character-literal
|   numeric-literal
|   date-time-literal
|   aggregate-function
|   scalar-function
|   concatenated-char-expr
|   numeric-arith-expr
|   date-arith-expr
|   conditional-expr
|   ( expr )

Arguments

[ { table_name | alias } . ] column-name

A column in a table.

You can qualify column names with the name of the table they belong to:

SELECT CUSTOMER.CUSTOMER_ID FROM CUSTOMERS

You must qualify a column name if it occurs in more than one table specified in the FROM clause:

SELECT CUSTOMER.CUSTOMER_ID
    FROM CUSTOMERS, ORDERS

Qualified column names are always allowed even when they are not required.

You can also qualify column names with an alias. Aliases are also called correlation names.

The FROM clause of a query expression can specify an optional alias after the table name (see "Query Expressions" for details on query expressions). If you specify an alias, you must use it—not the table name—to qualify column names that refer to the table. Query expressions that join a table with itself must use aliases to distinguish between references to column names.

The following example shows a query expression that joins the table customer with itself. It uses the aliases x and y and returns information on customers in the same city as customer SMITH:

SELECT y.cust_no, y.name
     FROM  customer x, customer y
     WHERE  x.name = 'SMITH'
        AND y.city = x.city ;
character-literal | numeric-literal | date-time-literal

Literals that specify a constant value. See "Literals" for details on specifying literals.

aggregate-function | scalar function

An SQL function. See "Functions".

concatenated-char-expr

An expression that concatenates multiple character expressions into a single character string. See “Concatenated Character Expressions” on page 4-32.

numeric-arith-expr

An expression that computes a value from numeric values. See "Numeric Arithmetic Expressions" on page 4-33.

date-arith-expr

An expression that computes a value from date-time values. See "Date Arithmetic Expressions".

conditional-expr

An expression that evaluates a search condition or expression and returns one of multiple possible results depending on that evaluation. See "Conditional Expressions".

( expr )

An expression enclosed in parentheses. SQL evaluates expressions in parentheses first.

4.6.1 Concatenated Character Expressions

Description

The || concatenation operator (two vertical bars) concatenates the two character expressions it separates.

The concatenation operator is similar to the CONCAT scalar function (see page 4-56). However, the concatenation operator allows easy concatenation of more than two character expressions, while the CONCAT scalar function requires nesting.

Syntax

concatenated-char-expr ::
{ character-literal | character-expr } || { character-literal | character-expr }
[ { character-literal | character-expr } || { character-literal | character-expr } ] [ … ]

Arguments

character-literal

A character literal. See "Character String Literals" on page 4-36 for details on specifying character literals.

character-expr

Any expression that evaluates to a character string (see "Data Types" for details of character data types), including column names and scalar functions that return a character string.

Examples

ISQL> SELECT 'Today''s date is ' || TO_CHAR(SYSDATE) FROM SYSCALCTABLE;
TODAY'S DATE IS 08/17/1998
--------------------------
Today's date is 08/17/1998
1 record selected

4.6.2 Numeric Arithmetic Expressions

Description

Numeric arithmetic expressions compute a value using addition, subtraction, multiplication, and division operations on numeric literals and expressions that evaluate to any numeric data type.

Syntax

numeric-arith-expr ::
[ + | - ] { numeric-literal | numeric-expr } [ { + | - | * | / } numeric-arith-expr ]

Arguments

[ + | - ]

Unary plus or minus operator

numeric-literal

A numeric literal. See "Numeric Literals" for details on specifying numeric literals.

numeric-expr

Any expression that evaluates to a numeric data type (see "Data Types" for details of numeric data types), including:

  • Column names
  • Subqueries that return a single value
  • Aggregate functions
  • CAST or CONVERT operations to numeric data types
  • Other scalar functions that return a numeric data type
{ + | - | * | \ }

Addition, subtraction, multiplication, or subtraction operator. SQL evaluates numeric arithmetic expressions in the following order:

  • Unary plus or minus
  • Expressions in parentheses
  • Multiplication and division, from left to right
  • Addition and subtraction, from left to right

4.6.3 Date Arithmetic Expressions

Description

Date arithmetic expressions compute the difference between date-time expressions in terms of days or milliseconds. SQL supports these forms of date arithmetic:

  • Addition and subtraction of integers to and from date-time expressions
  • Subtraction of a date-time expression from another

Syntax

date_arith_expr ::
     date_time_expr { + | - } int_expr
|    date_time_expr - date_time_expr

Arguments

date_time_expr

An expression that returns a value of type DATE or TIME or TIMESTAMP. A single date-time expression can not mix data types. All elements of the expression must be the same data type.

Date-time expressions can contain date-time literals, but they must be converted to DATE or TIME using the CAST, CONVERT, or TO_DATE functions (see the following examples, and "CAST" and "CONVERT function (extension)").

int_expr

An expression that returns an integer value. SQL interprets the integer differently depending on the data type of the date-time expression:

  • For DATE expressions, integers represent days
  • For TIME expressions, integers represent milliseconds
  • For TIMESTAMP expressions, integers represent milliseconds

Examples

The following example manipulates DATE values using date arithmetic. SQL interprets integers as days and returns date differences in units of days:

SELECT C1, C2, C1-C2 FROM DTEST
c1               c2         c1-c2
1956-05-07       1952-09-29 1316

select sysdate,
   sysdate - 3 ,
   sysdate - cast ('9/29/52' as date)
from dtest;

sysdate          sysdate-3  sysdate-convert(date,9/29/52)

1995-03-24       1995-03-21 15516

The following example manipulates TIME values using date arithmetic. SQL interprets integers as milliseconds and returns time differences in milliseconds:

select systime,
       systime - 3000,
       systime - cast ('15:28:01' as time)
from dtest;
systime          systime-3000  systime-convert(time,15:28:01)

15:28:09         15:28:06      8000

4.6.4 Conditional Expressions

Conditional expressions are a subset of scalar functions that generate different results depending on the value of their arguments. They provide some of the flexibility of traditional programming constructs to allow expressions to return alternate results depending on the value of their arguments.

The following scalar functions provide support for conditional expressions. See the discussion for each function in "Scalar Functions" for details.

CASE

CASE is the most general conditional expression. It specifies a series of search conditions and associated expressions. SQL returns the value specified by the first expression whose associated search condition evaluates as true. If none of the expressions evaluate as true, the CASE expression returns a null value (or the value of some other default expression if the CASE expression includes the ELSE clause).

All the other conditional expressions can also be expressed as CASE expressions.

DECODE

DECODE provides a subset of the functionality of CASE that is compatible with Oracle SQL syntax. DECODE is not SQL-92 compatible.

NULLIF

NULLIF substitutes a null value for an expression if it is equal to a second expression.

COALESCE

COALESCE specifies a series of expressions. SQL returns the first expression whose value is not null. If all the expressions evaluate as null, COALESCE returns a null value.

IFNULL

IFNULL substitutes a specified value if an expression evaluates as null. If the expression is not null, IFNULL returns the value of the expression.