LEFT OUTER JOIN

Two-table outer join:

SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptID = dept.deptID

Three-table outer join:

SELECT * FROM (emp LEFT OUTER JOIN dept ON emp.deptID = dept.deptID) LEFT OUTER JOIN addr ON emp.empID = addr.empID

Embedded in vendor strings:

SELECT t1.deptno, ename FROM {oj emp t2 LEFT OUTER JOIN dept t1 ON t2.deptno = t1.deptno}

FairCom supports two-table outer joins.

In addition to simple two-table outer joins, FairCom supports n- way nested outer joins.

The outer join may or may not be embedded in a vendor string. If a vendor string is used, the ODBC driver will strip it off and parse the actual outer join text.

UNION

SELECT name, status FROM tech_staff UNION SELECT name, status FROM adm_staff

UNION eliminates duplicate rows.

UNION ALL

SELECT name, status FROM tech_staff UNION ALL SELECT name, status FROM adm_staff

UNION ALL preserves duplicate rows.

date-literal

SELECT * FROM emp WHERE hire_date < ‘1992-02-02’

SELECT * FROM emp WHERE hire_date < {d ‘1992-02-02’}

FairCom supports the following date literal format: ‘yyyy-mm-dd’.

Dates may be in the range of year 0 to 9999.

Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. FairCom treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_DATE. This becomes important when conversions are attempted. For example, CONVERT({d ‘1992-02-02’}, SQL_TIMESTAMP) is valid, whereas CONVERT(‘1992-02-02’, SQL_TIMESTAMP) returns an invalid SQL_TIMESTAMP value.

time-literal

SELECT * FROM bday WHERE btime = ‘10:04:29’

SELECT * FROM bday WHERE btime = {t ‘10:04:29’}

FairCom supports the following time literal form: ‘hh:mm:ss’.

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. FairCom treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

timestamp-lit

SELECT * FROM bday WHERE btime = ‘1965-08-25 05:25:00’

SELECT * FROM bday WHERE btime={ts ‘1965-08-25 05:25:00’}

FairCom supports the following timestamp literal format: ‘yyyy-mm-dd hh:mm:ss’.

Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. FairCom treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP.

date arithmetic

SELECT * FROM inv WHERE inv_date > ‘1993-01-01’ AND inv_date < {d ‘1993-01-01’} + 30

SELECT * FROM pay WHERE pay_date - inv_date > 30

FairCom supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a CONVERT on the date.)

FairCom also supports subtracting one date from another to yield a number of days.

extended predicates

{pred contains, col1, ‘text’}

Uses extended vendor string syn