Feature
|
SQL-99 Core
|
SQL-2003 Mandatory
|
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
|
|
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
|
|
|
|
|
|
Feature Name
|
SQL-99 Core
|
SQL-2003 Mandatory
|
Note
|
Embedded Ada
|
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
|
|
|
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
|
|
|
|
Retrospective check constraints
|
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
|
|
|
|
Comparable data types for referential constraints
|
|
|
|
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.
|
|
|
|
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
|
|
|
|
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
|
|
|
|
Explicit security for external routines
|
|
|
|
Explicit security for SQL routines
|
N/A
|
No
|
|
Qualified SQL parameter references
|
N/A
|
No
|
|
Table functions
|
N/A
|
|
|
Basic roles
|
No
|
No
|
|
Extended roles
|
No
|
No
|
|
Bracketed SQL comments (/*...*/ comments)
|
No
|
No
|
|
Extended grouping capabilities
|
|
|
|
Nested and concatenated GROUPING SETS
|
N/A
|
|
|
Multiargument GROUPING function
|
N/A
|
|
|
GROUP BY DISINCT
|
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
|
|
Copyright BASIS International Ltd. BBj®, Visual PRO/5®, PRO/5®, and BBx® are registered trademarks.