Documentation

SQL Reference Guide

Previous Topic

Next Topic

Expressions

An expression is a symbol or string of symbols used to represent or calculate a single value in a FairCom DB SQLstatement. When you specify an expression in a statement, FairCom DB 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

| scalar-subquery-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 more details). 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 and subsequent pages for more details on all types of literals.

aggregate-function | scalar function

A FairCom DB SQL function. See Functions for details.

concatenated-char-expr

An expression that concatenates multiple character expressions into a single character string. See Concatenated Character Expressions for more details.

numeric-arith-expr

An expression that computes a value from numeric values. See Numeric Arithmetic Expressions for more details.

date-arith-expr

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

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 for more details.

scalar-subquery-expr

An expression which is a scalar sub-query. A scalar sub-query returns only one value. See Scalar Sub-query Expressions for more details.

( expr )

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

In This Section

Concatenated Character Expressions

Numeric Arithmetic Expressions

Date Arithmetic Expressions

Conditional Expressions

Scalar Sub-query Expressions

Escape field name matching reserved words in c-tree expressions

TOCIndex