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.
Concatenated Character Expressions
Description
The || concatenation operator (two vertical bars) || or + (plus) concatenates the two character expressions it separates.
The concatenation operator is similar to the CONCAT scalar function. 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. Refer to Character String Literals for details on specifying character literals.
character-expr
Any expression that evaluates to a character string (refer to 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
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
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. Refer to Numeric Literals for details on specifying numeric literals.
numeric-expr
Any expression that evaluates to a numeric data type (Refer to 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 division operator. FairCom DB 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
Date Arithmetic Expressions
Description
Date arithmetic expressions compute the difference between date-time expressions in terms of days or milliseconds. FairCom DB 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 cannot 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 as well as CAST function (SQL-92 compatible) and CONVERT function (extension)).
int_expr
An expression that returns an integer value. FairCom DB 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. FairCom DB 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. FairCom DB 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
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. Refer to Scalar Functions for a description of each function.
CASE
CASE is the most general conditional expression. It specifies a series of search conditions and associated expressions. FairCom DB 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. FairCom DB 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.
Scalar Sub-query Expressions
Description
A scalar sub-query expression, denoted by grammar symbol scalar_subquery_expr, is a query_expression that returns only one value, that is, a value for one row and one column. See Query Expressions for more information on query_expression.
Scalar sub-query expressions can be specified in select lists, search conditions and arguments of scalar functions.
Examples
- Scalar sub-query in a select list
SELECT e.ename, ( SELECT d.dname
FROM dept d
WHERE d.deptno = e.deptno)
FROM emp e;
- Scalar sub-query in a search condition
SELECT ename
FROM emp
WHERE (SELECT MAX(deptno)
FROM dept) = deptno;
- Scalar sub-query as an argument to a scalar function.
SELECT e.ename, LEN ((SELECT d.dname
from dept d
WHERE d.deptno = e.deptno))
FROM emp e;
Escape field name matching reserved words in c-tree expressions
In V11.5 and later, it is possible to escape the field names by wrapping them in square brackets, thus identifying them directly as fields so they do not conflict with function names.
This is useful if you have a field name that is the same as a function name. For example, if you have a field named "year" and you want to set a filter on year being 2000, so the filter string would be "year = 2000". The parser would fail because it identified "year" as a function with the wrong syntax.
The new syntax would be "[year] = 2000", which the parser unambiguously interprets as a field named "year".
The parser allows the content of "[]" to be composed by any character excluding "]", space, tabs, newline, and linefeed.