BBj Support for SQL-99 and SQL-2003 Features

The table below shows the features defined in SQL-99 (ANSI/ISO/IEC 9075-2-1999: for Information Technology – Database Languages – SQL – Part 2: Foundation (SQL/Foundation)) and SQL-2003 (ISO/IEC 9075-2: Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)) and designates whether they are implemented in the BBj SQL engine.

Key

Yes	Feature is supported.
Yes*	Feature is supported (e.g. through JDBC) but may differ with the SQL standard. See comments for more information.
Partial	Feature is partially supported.
No	Feature is not supported.
N/A	Not applicable.

Feature	SQL-99 Core	SQL-2003Mandatory	Note
Numeric data types	Yes	Yes
INTEGER and SMALLINT data types (including all spellings)	Yes	Yes
REAL, DOUBLE PRECISON, and FLOAT data types	Yes	Yes
DECIMAL and NUMERIC data types	Yes	Yes
Arithmetic operators	Yes	Yes
Numeric comparison	Yes	Yes
Implicit casting among the numeric data types	Yes	Yes
Character data types	Yes	Yes
CHARACTER data type (including all its spellings)	Yes	Yes
CHARACTER VARYING data type (including all its spellings)	Yes	Yes
Character literals	Yes	Yes
CHARACTER_LENGTH function	Yes*	Yes*	Called CHARACTER_LENGTH and LENGTH. {fn LENGTH(...)} is according to JDBC specification.
OCTET_LENGTH function	Yes	Yes
SUBSTRING function	Yes	Yes
Character concatenation	Yes	Yes
UPPER and LOWER functions	Yes	Yes
TRIM function	Yes	Yes
Implicit casting among the character data types	Yes	Yes
POSITION function	Yes*	Yes*	Called LOCATE. {fn LOCATE(...)} is according to JDBC specification.
Character comparison	Yes	Yes
Identifiers	Yes	Yes
Delimited identifiers	Yes	Yes
Lower case identifiers	Yes	Yes
Trailing underscore	Yes	Yes
Basic query specification	Yes	Yes
SELECT DISTINCT	Yes	Yes
GROUP BY clause	Yes	Yes
GROUP BY can contain columns not in select-list	Yes	Yes
Select list items can be renamed	Yes	Yes
HAVING clause	Yes	Yes
LIMIT clause	Yes	Yes
Qualified * in select list	Yes	Yes
Correlation names in the FROM clause	Yes	Yes
Rename columns in the FROM clause	No	No
Basic predicates and search conditions	Yes	Yes
Comparison predicate	Yes	Yes
BETWEEN predicate	Yes	Yes
IN predicate with list of values	Yes	Yes
LIKE predicate	Yes	Yes
LIKE predicate: ESCAPE clause	Yes	Yes
NULL predicate	Yes	Yes
Quantified comparison predicate	Yes	Yes
EXISTS predicate	Yes	Yes
Subqueries in comparison predicate	Yes	Yes
Subqueries in IN predicate	Yes	Yes
Subqueries in quantified comparison predicate	Yes	Yes
Correlated subqueries	Yes	Yes
Search condition	Yes	Yes
Basic query expressions	Yes	Yes
UNION DISTINCT table operator	Yes	Yes
UNION ALL table operator	Yes	Yes
EXCEPT DISTINCT table operator	No	No
Columns combined via table operators need not have exactly the same data type	Yes	Yes
Table operators in subqueries	Yes	Yes
Basic Privileges	Partial	Partial
SELECT privilege at the table level	Yes	Yes
DELETE privilege	Yes	Yes
INSERT privilege at the table level	Yes	Yes
UPDATE privilege at the table level	Yes	Yes
UPDATE privilege at the column level	No	No
REFERENCES privilege at the table level	No	No
REFERENCES privilege at the column level	No	No
WITH GRANT OPTION	Yes	Yes
USAGE privilege	No	No
EXECUTE privilege	Yes	Yes
Set functions	Yes	Yes
AVG	Yes	Yes
COUNT	Yes	Yes
MAX	Yes	Yes
MIN	Yes	Yes
SUM	Yes	Yes
ALL quantifier	Yes	Yes
DISTINCT qualifier	Yes	Yes
Basic data manipulation	Yes	Yes
INSERT statement	Yes	Yes
Searched UPDATE statement	Yes	Partial	correlation name not supported
Searched DELETE statement	Yes	Partial	correlation name not supported
Single row select statement	Yes	Yes
Basic cursor support	Yes*	Yes*	Through JDBC
Declare cursor	No	No
ORDER BY columns need not be in select list	Yes	Yes
Value expressions in ORDER BY clause	Yes	Yes
OPEN statement	No	No
Positioned UPDATE statement	Yes	Partial	correlation name not supported
Positioned DELETE statement	Yes	Partial	correlation name not supported
CLOSE statement	No	No
FETCH statement	No	No
WITH HOLD cursors	No	No
Null value support (nulls in lieu of values)	Yes	Yes
Basic integrity constraints	Yes*	Yes*	Depends on the file type
NOT NULL constraints	Yes*	Yes*	Supported in ESQL only.
UNIQUE constraints of NOT NULL columns	Yes*	Yes*	Supported in ESQL only.
PRIMARY KEY constraints	Yes	Yes
Basic FOREIGN KEY constraint with the NO ACTION default	Yes*	Yes*	Supported in ESQL only.
CHECK constraints	Yes*	Yes*	Supported in ESQL only.
Column defaults	Yes*	Yes*	Supported in ESQL only.
NOT NULL inferred on PRIMARY KEY	Yes	Yes
Names in a foreign key can be specified in any order	Yes	Yes
Transaction support	Yes*	Yes*	Transactions are only supported in ESQL and Journaled files.
COMMIT statement	Yes*	Yes*	Through JDBC Connection.commit. Only supported in ESQL and Journaled files.
ROLLBACK statement	Yes*	Yes*	Through JDBC Connection.rollback. Only supported in ESQL and Journaled files.
Basic SET TRANSACTION statement	Yes*	Yes*	See below.
SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause	Yes*	Yes*	Connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE) is according to JDBC specification. Only supported in ESQL and Journaled files.
SET TRANSACTION statement: READ ONLY and READ WRITE clauses	Yes*	Yes*	No SQL syntax. Connection.setReadWrite() is according to JDBC specification. Only supported in ESQL and Journaled files.
Updatable queries with subqueries	No	No
SQL comments using leading double minus	No	No
SQLSTATE support	Yes	Yes	For most values.
Module language	N/A	No
Basic information schema	No	N/A	Note: JDBC DatabaseMetadata is ok.
COLUMNS view	No	N/A
TABLES view	No	N/A
VIEWS view	No	N/A
TABLE_CONSTRAINTS view	No	N/A
REFERENTIAL_CONSTRAINTS	No	N/A
CHECK_CONSTRAINTS	No	N/A
Basic schema manipulation	Yes	Yes
CREATE TABLE statement to create persistent base tables	Yes	Yes
CREATE VIEW statement	Yes	Yes
GRANT statement	Yes	Yes
ALTER TABLE statement: ADD COLUMN clause	Yes	Yes
DROP TABLE statement: RESTRICT clause	Yes Implicit	Yes Implicit
DROP VIEW statement: RESTRICT clause	Yes Implicit	Yes Implicit
REVOKE statement: RESTRICT clause	No	No
Basic joined tables	Yes	Yes
Inner join (but not necessarily the INNER keyword)	Yes	Yes
INNER keyword	Yes	Yes
LEFT OUTER JOIN	Yes	Yes
RIGHT OUTER JOIN	Yes	Yes
Outer joins can be nested	Yes	Yes
The inner table in a left or right outer join can also be used in an inner join	Yes	Yes
All comparison operators are supported (rather than just =)	Yes	Yes
Basic date and time	Yes*	Yes*	See below.
DATE data type (including DATE literal)	Yes*	Yes*	DATE literal is implemented as built-in function. {d 'yyyy-mm-dd'} is according to JDBC specification.
TIME data type (including TIME literal) with fractional seconds precision of 0	Yes*	Yes*	TIME literal is implemented as built-in function. No precision in datatype. {t' hh:mm:ss'} is according to JDBC specification.
TIMESTAMP data type (including TIMESTAMP literal) with fractional seconds precision of 0 and 6	Yes*	Yes*	TIMESTAMP literal is implemented as built-in function. No precision spec in datatype. No timezone spec in datatype. {ts 'yyyy-mm-dd hh:mm:ss.f...'} is according to JDBC specification.
Comparison predicate on DATE, TIME, and TIMESTAMP data types	Yes	Yes
Explicit CAST between datetime types and character types	Yes	Yes
CURRENT_DATE	Yes*	Yes*	No time zone in datetime value expression
LOCALTIME	Yes*	Yes*	{fn CURTIME()} is according to JDBC specification.
LOCALTIMESTAMP	No	No
UNION and EXCEPT in views	Partial	Partial	UNION is supported.
Grouped operations	Yes	Yes
WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views	Yes	Yes
Multiple tables supported in queries with grouped views	Yes	Yes
Set functions supported in queries with grouped views	Yes	Yes
Subqueries with GROUP BY and HAVING clauses and grouped views	Yes	Yes
Single row SELECT with GROUP BY and HAVING clauses and grouped views	Yes	Yes
Multiple module support	No	No
CAST function	Yes	Yes
Explicit defaults	Yes	Yes
CASE expression	Yes	Yes
Simple CASE	Yes	Yes
Searched CASE	Yes	Yes
NULLIF function	Yes	Yes
COALESCE function	Yes	Yes
Schema definition statement	Yes	Yes
Create schema	No	No
CREATE TABLE for persistent base tables	Yes	Yes
CREATE VIEW	Yes	Yes
CREATE VIEW: WITH CHECK OPTION	No	No
GRANT statement	Yes	Yes
Scalar subquery values	Yes	Yes
Expanded NULL predicate	Yes	Yes
Features and conformance views	No	N/A
SQL_FEATURES view	No	N/A
SQL_SIZING view	No	N/A
SQL_LANGUAGES view	No	N/A
Basic flagging	No	No
Distinct data types	No	No
USER_DEFINED_TYPES view	No	N/A
Basic SQL invoked routines	Yes	Yes	Language BBj.
User-defined functions with no overloading	Yes	Yes
User-defined stored procedures with no overloading	Yes	Yes
Function invocation	Yes	Yes
CALL statement	Yes	Yes
RETURN statement	No	No
ROUTINES view	No	N/A
PARAMETERS view	No	N/A
IN predicate with one list element	N/A	Yes
EmbeddedAda	N/A	No
Embedded C	N/A	No
Embedded COBOL	N/A	No
Embedded Fortran	N/A	No
Embedded MUMPS	N/A	No
Embedded Pascal	N/A	No
Embedded PL/I	N/A	No
Direct SQL	N/A	No
Basic dynamic SQL	N/A	No
Extended dynamic SQL	N/A	No
<describe input statement>	N/A	No
Untyped SQL-invoked function arguments	N/A	No
Dynamic specification of cursor attributes	N/A	No
Extensions to embedded SQL exception declarations	N/A	No
Enhanced execution rights	N/A	No
Module language Ada	N/A	No
Module language C	N/A	No
Module language COBOL	N/A	No
Module language Fortran	N/A	No
Module language MUMPS	N/A	No
Module language Pascal	N/A	No
Module language PL/I	N/A	No
Routine language Ada	N/A	No
Routine language C	N/A	No
Routine language COBOL	N/A	No
Routine language Fortran	N/A	No
Routine language MUMPS	N/A	No
Routine language Pascal	N/A	No
Routine language PL/I	N/A	No
Routine language SQL	N/A	No
CASCADE drop behavior	No	No
ALTER TABLE statement: DROP COLUMN clause	Yes*	Yes*	Only supported in ESQL.
Extended REVOKE statement	No	No
REVOKE statement performed by other than the owner of a schema object	Yes	Yes
REVOKE statement: GRANT OPTION FOR clause	No	No
REVOKE statement to revoke a privilege that the grantee has WITH GRANT OPTION	No	No
Intervals and datetime arithmetic	No	No
OVERLAPS predicate	N/A	No
Isolation levels other than SERIALIZABLE	Yes*	Yes*	Only supported in ESQL and Journaled files.
READ UNCOMMITTED isolation level	Yes*	Yes*	Only supported in ESQL and Journaled files.
READ COMMITTED isolation level	Yes*	Yes*	Only supported in ESQL and Journaled files.
REPEATABLE READ isolation level	Yes*	Yes*	Only supported in ESQL and Journaled files.
Basic diagnostics management	No	No
GET DIAGNOSTICS statement	No	No
SET TRANSACTION statement: DIAGNOSTICS SIZE clause	No	No
Multiple schemas per user	No	No
Referential delete actions	No	No
INSERT statement: DEFAULT VALUES clause	No	No
Privilege tables	No	No
TABLE_PRIVILEGES view	No	No
COLUMN_PRIVILEGES view	No	No
USAGE_PRIVILEGES view	No	No
Domain support	No	No
Extended CASE expression	N/A	N/A
Compound character literals	No	No
LIKE enhancements	Partial	Partial
UNIQUE predicate	No	No
CORRESPONDING in query expressions	No	No
INTERSECT table operator	No	No
INTERSECT DISTINCT table operator	No	No
INTERSECT ALL table operator	No	No
EXCEPT ALL table operator	No	No
MERGE statement	N/A	No
User authorization	No	No
Usage tables	No	No
Subprogram support	No	No
Extended schema manipulation	Partial	Partial
ALTER TABLE statement: ALTER COLUMN clause	Partial	Partial
ALTER TABLE statement: ADD CONSTRAINT clause	Partial	Partial
ALTER TABLE statement: DROP CONSTRAINT clause	Yes	Yes
Long identifiers	Yes	Yes
Unicode escapes in identifiers	N/A	No
Unicode escapes in literals	N/A	No
Extended joined table	No	No
NATURAL JOIN	No	No
FULL OUTER JOIN	Yes	Yes
CROSS JOIN	No	No
Named column joins for LOBs, arrays, and multisets	N/A	No
Time zone specification	No	No
National character	No	No
Read-only scrollable cursors	Yes	Yes
FETCH with explicit NEXT	No	No
FETCH FIRST	No	No
FETCH LAST	No	No
FETCH PRIOR	No	No
FETCH ABSOLUTE	No	No
FETCH RELATIVE	No	No
Extended set function support	No	No
Mixed column references in set functions	N/A	No
Character set definition	No	No
Named character sets	No	No
Constraint management	Yes	Yes
Enhanced documentation tables	No	No
SQL_SIZING_PROFILES view	No	No
SQL_IMPLEMENTATION_INFO view	No	No
SQL_PACKAGES view	No	No
Assertions	No	No
Temporary tables	Partial	Partial
Enhanced seconds precision	No	No
Full value expressions	No	No
Truth value tests	No	No
Derived tables	No	No
Indicator data types	No	No
Row and table constructors	Yes	Yes
Catalog name qualifiers	No	No
Simple tables	No	No
Subqueries in CHECK	N/A	N/A
Retrospective check constraints	N/A	N/A
Collation and translation	No	No
Enhanced collation support	N/A	No
SQL-session and client module collations	N/A	No
Translation support	N/A	No
Additional translation documentation	N/A	No
Referential update actions	Partial	Partial
ALTER domain	No	No
Deferrable constraints	No	No
INSERT column privileges	No	No
Referential MATCH types	No	No
View CHECK enhancements	No	No
Session management	No	No
Connection management	No	No
Self-referencing operations	Yes	Yes
Insensitive cursors	Yes*	Yes*	Through JDBC.
Full set function	Partial	Partial	DISTINCT in more than one aggregate function will not work, but SELECT DISTINCT with DISTINCT in one aggregate function will work.
Extended flagging	No	N/A
Extended flagging -- Part 1, Subclause 8.5, "SQL flagger": With "level of flagging" specified to be Core SQL Flagging and "extent of checking" specified to be Catalog Lookup	No	No
Local table references	No	No
Full cursor update	Partial	Partial
Updatable scrollable cursors	Partial	Partial
Updatable ordered cursors	No	No
Basic structured types	No	No
Enhanced structured types	No	No
Final structured types	N/A	No
Self-referencing structured types	N/A	No
Create method by specific method name	N/A	No
Permutable UDT options list	N/A	No
Basic reference types	No	No
Enhanced reference types	No	No
Create table of type	No	No
SQL paths in function and type name resolution	No	No
Subtables	No	No
Basic array support	No	No
Arrays of built-in data types	No	No
Arrays of distinct types	No	No
Array expressions	No	No
Arrays of user-defined types	No	No
Arrays of reference types	No	No
Array constructors by query	N/A	No
Optional array bounds	N/A	No
Array element assignment	N/A	No
ONLY in query expressions	No	No
Type predicate	No	No
Subtype treatment	No	No
Subtype treatment for references	N/A	No
SQL-invoked routines on arrays	No	No
Array parameters	No	No
Array as result type of functions	No	No
SQL-invoked routines on multisets	N/A	No
User-defined cast functions	No	No
Structured type locators	No	No
Array locators	No	No
Multiset locators	N/A	No
Transform functions	No	No
Alter transform statement	N/A	No
User-defined orderings	No	No
Specific type method	No	No
Basic multiset support	N/A	No
Multisets of user-defined types	N/A	No
Multisets of reference types	N/A	No
Advanced multiset support	N/A	No
Nested collection types	N/A	No
Unique constraint on entire row	N/A	No
Timestamp in Information Schema	No	No
BOOLEAN data type	No	No
Basic LOB data type support	Yes	Yes
BLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s BINARY, BLOB, LARGE, and OBJECT -- Subclause 5.3, "<literal>": <binary string literal> -- Subclause 6.1, "<data type>": The BINARY LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type BINARY LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for BINARY LARGE OBJECT for all supported languages	Yes	Yes
CLOB data type -- Subclause 5.2, "<token> and <separator>": The <reserved word>s CHARACTER, CLOB, LARGE, and OBJECT -- Subclause 6.1, "<data type>": The CHARACTER LARGE OBJECT data type -- Subclause 6.28, "<string value expression>": For values of type CHARACTER LARGE OBJECT -- Subclause 13.6, "Data type correspondences": Type correspondences for CHARACTER LARGE OBJECT for all supported languages -- The implicit casting among the fixed-length and variable-length character string types supported by subfeature E021-10 is extended to support the character large object type	Yes	Yes
POSITION, LENGTH, LOWER, TRIM, UPPER, and SUBSTRING functions for LOB data types -- Subclause 6.27, "<numeric value function>": The <position expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <char length expression> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.27, "<numeric value function>": The <octet length expression> for expressions of type BINARY LARGE OBJECT and CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <fold> function for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <trim function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob trim function> -- Subclause 6.29, "<string value function>": The <character substring function> for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.29, "<string value function>": The <blob substring function>	Yes*	Yes*	May get unexpected results using binary data.
Concatenation of LOB data types -- Subclause 6.28, "<string value expression>": The <concatenation> expression for expressions of type CHARACTER LARGE OBJECT -- Subclause 6.28, "<string value expression>": The <blob concatenation> expression	Yes	Yes
LOB locator: non-holdable -- Subclause 13.3, "<externally-invoked procedure>": <locator indication> -- Subclause 14.14, "<free locator statement>"	No	No
Extended LOB data type support	Partial	Partial	CAST and string value functions implemented. No comparison or ordering.
Row types	No	No
MAX and MIN for row types	N/A	No
Explicit aliases for all-fields reference	N/A	No
UCS support	N/A	No
BIGINT data type	N/A	Yes
Updatable joins, unions, and columns	No	No
WITH (excluding RECURSIVE) in query expression	No	No
WITH (excluding RECURSIVE) in subquery	N/A	No
Recursive query	No	No
Recursive query in subquery	N/A	No
SIMILAR predicate	No	No
DISTINCT predicate	No	No
DISTINCT predicate with negation	N/A	No
LIKE clause in table definition	No	No
AS subquery clause in table definition	N/A	No
Extended LIKE clause in table definition	N/A	No
Identity columns	N/A	Yes*	Only supported in ESQL files.
Generated columns	N/A	Yes*	Only supported in ESQL.
Sequence generator support	N/A	Yes
Referential action RESTRICT	N/A	N/A
Comparable data types for referential constraints	N/A	N/A
Basic trigger capability	Yes*	Yes*
Triggers activated on UPDATE, INSERT, or DELETE of one base table.	Yes	Yes
BEFORE triggers	Yes*	Yes*
AFTER triggers	Yes	Yes
FOR EACH ROW triggers	No	No
Ability to specify a search condition that shall be True before the trigger is invoked.	No	No
Support for run-time rules for the interaction of triggers and constraints.	N/A	N/A
TRIGGER privilege	No	No
Multiple triggers for the same event are executed in the order in which they were created in the catalog.	Yes	Yes
Enhanced trigger capability	N/A	N/A
Sensitive cursors	No	No
START TRANSACTION statement	No	No
SET TRANSACTION statement: LOCAL option	No	No
Chained transactions	No	No
Savepoints	Yes	Yes	Only supported in ESQL files.
Enhanced savepoint management	N/A	No
SELECT privilege with column granularity	Yes	Yes
Functional dependencies	No	No
OVERLAY function	No	No
Overloading of SQL-invoked functions and procedures	N/A	N/A
Explicit security for external routines	N/A	N/A
Explicit security for SQL routines	N/A	No
Qualified SQL parameter references	N/A	No
Table functions	N/A	N/A
Basic roles	No	No
Extended roles	No	No
Bracketed SQL comments (/*...*/ comments)	No	No
Extended grouping capabilities	N/A	N/A
Nested and concatenated GROUPING SETS	N/A	N/A
Multiargument GROUPING function	N/A	N/A
GROUP BY DISINCT	N/A	N/A
ABS and MOD functions	Yes	Yes
Symmetric BETWEEN predicate	No	No
Result sets return value	No	No
LATERAL derived table	No	No
Enhanced EXISTS predicate	No	No
Transaction counts	No	No
Updatable table references	No	N/A
Optional key words for default syntax	No	No
Holdable locators	No	No
Array-returning external SQL-invoked functions	No	No
Multiset-returning external SQL-invoked functions	N/A	No
Regular expression substring function	No	No
UNIQUE constraints of possibly null columns	No	No
Local cursor references	No	No
Elementary OLAP operations	N/A	No
Advanced OLAP operations	N/A	No
Sampling	N/A	No
Enhanced numeric functions	N/A	No
Multiple column assignment	N/A	No
SQL-schema statements in SQL routines	No	No
SQL-dynamic statements in SQL routines	No	No
SQL-schema statements in external routines	No	No
SQL-dynamic statements in external routines	No	No
Cyclically dependent routines	No	No