ODBC - c-tree Plus Edition

CREATE TABLE

CREATE TABLE sal (emp_id integer, name char(50), salary float, hire_date date)

CREATE TABLE emp (emp_id integer NOT NULL, PRIMARY KEY (emp_id) )

Column constraint definitions supported: NOT NULL.

Table constraint definitions supported: UNIQUE and PRIMARY KEY DEFAULT. Default-value is not sup

DELETE

DELETE FROM sal WHERE name = ‘John Smith’

DROP TABLE

DROP TABLE sal

[CASCADE | RESTRICT] is not supported.

INSERT

INSERT INTO sal VALUES (34086, ‘Fred Black’, 45000.00, ‘1992-05-25’)

SELECT

SELECT * FROM sal

SELECT emp.emp_id, sal.salary FROM emp, sal WHERE emp.emp_id = sal.emp_id

UPDATE

UPDATE sal SET salary = 35000.00 WHERE emp_id = 25089

CREATE INDEX

CREATE INDEX empidx ON emp (emp_id, emp_name)

CREATE UNIQUE INDEX empidx ON emp (emp_id, emp_name)

To designate a key as a primary key, FairCom supports the UNIQUE option.

CREATE VIEW

CREATE VIEW vw_sal (v_col1, v_col2) AS SELECT emp_id, name FROM sal

The column list is optional.

DROP INDEX

DROP INDEX emp.empuniq

DROP VIEW

DROP VIEW vw_sal

[CASCADE | RESTRICT] is not supported.

SELECT

SELECT COUNT(emp_id), dept FROM mgrs GROUP BY dept HAVdept > 15

In addition to supporting an order by on a column-list, as specified in the ODBC Programmer’s Reference, FairCom has extended the syntax to support an order by on an expression-list or on any expression in a group by expression-list. For example: SELECT * FROM emp ORDER BY a+b,c+d,e This causes the result table to be ordered by three expressions: a+b, c+d, and e. If the expression is a positive integer literal, then that literal will be interpreted as the number of the column in the result set and ordering will be done on that column. No ordering is allowed on set functions or an expression that contains a set function.

subqueries

The following types of subqueries are supported: comparison, exists, quantified, in, and correlated. Order by clauses are not allowed in a subclause.

approximate-numeric-literal

SELECT * FROM results WHERE quotient = -4.5E-2

between- predicate

SELECT c1 FROM emp
WHERE emp_id
BETWEEN 10000 AND 20000

The syntax expr1 BETWEEN expr2 AND expr3returns TRUE if expr1 >= expr2 and expr1 <= expr3. expr2 and expr3 may be dynamic parameters (e.g., SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?).

correlation-name

SELECT * FROM emp t1, addr t2 WHERE t1.emp_id = t2.emp_id

FairCom supports both table and column correlation names.

exact-numeric-literal

INSERT INTO cars (car_no, price) VALUES (49042, 49999.99)

SELECT * FROM numtbl WHERE c1 = -208.6543189

in-predicate

SELECT * from colors WHERE color IN (‘red’, ‘blue’, ‘green’)

set-function

SELECT COUNT(a+b) FROM q

SELECT MIN(salary) FROM emp

MIN(expr), MAX(expr), AVG(expr), SUM(expr), COUNT(*), and COUNT(expr) are supported. COUNT(expr) counts all non-NULL values for an expression across a predicate. The following example counts all the rows in q where a+b does not equal NULL:

SELECT COUNT (a+b) FROM q

inner join syn

SELECT *
FROM mytableA, mytableB WHERE myColA = myColB

SELECT *
FROM mytableA, mytableB INNER JOIN myColA = myColB

These two statements are considered identical.

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