SQL Expression

Constructs a scalar expression in an SQL statement.

Syntax

expression

Syntax Elements

expression =
term || expression [+ || -] term

term =
factor || term [* || /] factor

factor =
{+ || -}primary

primary =
     column-name
||   dynamic-parameter
||   literal
||   set-function-reference
||   USER
||   (expression)
||   scalar-function-extension

See Syntax of Primary.

Syntax of Primary

column-name =
{[table-name || correlation-name].}column-identifier

table-name =
{owner-name.}table-identifier

owner-name =
The SQL user-id of the creator of the table

table-identifier =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

correlation-name =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

column-identifier =
Any user-defined name (except a Reserved Word) starting with a letter and comprising up to 49 letters, underscores and/or numbers. Letters are case sensitive.

dynamic-parameter =
?

literal =
character-string-literal || numeric-literal || date-literal
See Literals in the Examples section.

set-function-reference =
COUNT(*) || distinct-function || all-function

distinct-function =
Not currently supported by Reality.

all-function =
[AVG || MAX || MIN || SUM] (expression)

scalar-function-extension =
--(vendor(Microsoft),product(ODBC) fn scalar-function *)--
|| {fn scalar-function}

scalar-function =
function-name(arguments)

Refer to ODBC 2.0 Programmer's Reference Guide Appendix F for the list of scalar function-names and the arguments they require.

SQL for Reality currently supports only those scalar functions classified in the ODBC 2.0 specification as STRING, TIMEDATE and CONVERT.

Examples

Numeric

The following examples of SQL expressions yield a numeric value.

WEIGHT * 454

SALARY + COMMISSION + BONUS

{fn LENGTH ( SNAME ) } - 1

50 - ( AVG ( QTY ) / 100 )

( SELECT S.STATUS FROM S WHERE S.SNO = 'S3' )

Character String

The following examples are syntactically identical to numeric expressions, but yield a data type string value instead of a numeric value.

{fn CONCAT ( INITIALS, LASTNAME ) }

MIN ( COLOUR )

{fn SUBSTRING ( LASTNAME,1,3 )}

Literals

The following are examples of literals that can be used as a primary.

Character String literals

'56 Boundary Way'       'SQL/ODBC'               'ABCDExyz1234'          'Seychelles'

Numeric Literals

4		      -70			   365.123		0.876

Date Literals

{d '1967-01-15'}        {d '1999-07-27'}

Time Literals

{t '11:23:00'}          {t '14:01:30'}

Timestamp Literals

{ts '1999-07-27 11:23:00'}    {ts '1967-01-15 14:01:30'}

SQL Statement Examples using SQL Expressions

Example 1

SELECT SURNAME, DEPTNAME, POSITION
FROM STAFF
WHERE (ANNUAL_SALARY + BONUS + COMMISSION) > 50000
ORDER BY DEPTNAME

Example 2

SELECT SURNAME, POSITION, JOINDATE
FROM STAFF
WHERE DEPTNAME = 'SALES'

Example 3

SELECT SURNAME, {fn CONCAT(DEPTNAME, POSITION)}
FROM STAFF
WHERE JOINDATE < {d '1967-01-15'}

Example 4

SELECT {fn UCASE (SURNAME)}
FROM STAFF 

Example 5

SELECT TIMESTAMPDIFF(SQL_TSI_DAY, HIREDATE, {fn CURDATE()}) AS DAYS_EMPLOYED
FROM EMP WHERE EMPNO = 8876

Example 6

SELECT {fn CURDATE()}, {fn CURTIME()}, AVG(SAL) FROM EMP