SQL Reference Guide
Introducing the SQL Reference Guide
In This Guide
Audience
Enterprise Access Compatibility
System-specific Text in This Guide
Terminology Used in This Guide
Syntax Conventions Used in This Guide
Introducing SQL
SQL Functionality
Types of SQL Statements
Interactive and Embedded SQL
Interactive SQL
Line-Based Terminal Monitor
Forms Based Terminal Monitor
Embedded SQL
Embedded SQL Support
How Embedded SQL Differs From Interactive SQL
SQL Naming and Statement Rules
Object Naming Rules
User Names
Regular and Delimited Identifiers
Case Sensitivity of Identifiers
Restrictions on Identifiers
Comment Delimiters
Comments in Embedded SQL
Statement Terminators
Correlation Names
Correlation Name Rules
Database Procedures
Object Management Extension
ANSI Compliance
OpenSQL
Security Levels
Understanding SQL Data Types
SQL Data Types
X100 Supported Data Types
Character Data Types
Char Data Type
C Data Type (Deprecated)
Varchar Data Type
Text Data Type (Deprecated)
Long Varchar Data Type
Nchar, Nvarchar, and Long Nvarchar Data Types
Numeric Data Types
Integer Data Types
Decimal Data Type
Floating Point Data Types
Date and Time Data Types
Date and Time Input Formats
Date Data Type
Time Data Types
Timestamp Data Types
Interval Data Types
Summary of ANSI Datetime Data Types
Ingresdate Data Types
Coercion Between Datetime Data Types
Abstract Data Types
Money Data Type
Numeric String Data Type
Logical Key Data Type
IP Network Address Data Types
Universal Unique Identifier (UUID) Data Type
Binary Data Types
Byte Data Type
Byte Varying Data Type
Long Byte Data Type
Risk of Hardware Dependent SQL Code when Using Binary Data
Boolean Data Type
Spatial Data Types
Storage Formats of Data Types
Literals
String Literals
Hexadecimal Representation
Quotes within Strings
Unicode Literals
Numeric Literals
Integer Literals
Decimal Literals
Floating Point Literals
Date and Time Literals
Date Literals
Time Literals
Timestamp Literals
Interval Literals
Byte Literals
Boolean Literals
SQL Constants
Nulls
Nulls and Comparisons
Nulls and Aggregate Functions
Nulls and Integrity Constraints
Understanding the Elements of SQL Statements
SQL Operators
Arithmetic Operators
Comparison Operators
Logical Operators
SQL Operations
String Concatenation Operations
Assignment Operations
Character String Assignments
String Truncation
Numeric Assignments
ANSI Date and Time Assignments
Ingresdate Assignments
Logical Key Assignments
Null Value Assignments
Arithmetic Operations
Default Type Conversion
Arithmetic Operations on Decimal Data Types
Date and Time Arithmetic
Ingresdate Interval Arithmetic
ANSI Date and Time Comparisons
Ingresdate Comparisons
Operator Coercion Rules
SQL Functions
Scalar Functions
Conversion Functions
Numeric Functions
String Functions
Date and Time Functions
Bitwise Functions
Hashing Functions
Random Number Functions
IP Network Address Functions
Aggregate Functions
Basic Aggregate Functions
Regression and Correlation Analysis Aggregate Functions
String Aggregate Functions
Ordering Aggregate Functions
Aggregate Functions and Decimal Data
GROUP BY Clause with Aggregate Functions
Restrictions on the Use of Aggregate Functions
Window Functions
Window Function Syntax
Windowing Aggregate Functions
Analytical Functions
Null Handling Functions
UUID ([expr]) Function
Encryption Functions
MASK_COLUMN Function
Spatial Functions
Predicates in SQL
Comparison Predicates
Quantified Predicates
Pattern-matching Predicates
LIKE Predicate
BEGINNING, CONTAINING, and ENDING Predicates
SIMILAR TO Predicate
BETWEEN Predicate
IN Predicate
EXISTS Predicate
IS NULL Predicate
IS INTEGER Predicate
IS DECIMAL Predicate
IS FLOAT Predicate
IS TRUE, IS FALSE, IS UNKNOWN Predicates
IS INGRESDATE, IS ANSIDATE, IS TIME, IS TIMESTAMP, IS INTERVAL YEAR TO MONTH, IS INTERVAL DAY TO SECOND Predicates
Expressions in SQL
CASE Expressions
Searched CASE
Simple CASE
DECODE Function
IF, NULLIF, and COALESCE Functions
GREATEST, GREATER, LEAST, LESSER Functions
CAST Expression
Sequence Expressions
NEXT VALUE FOR and CURRENT VALUE FOR
LAST_IDENTITY Function
Locking and Sequences
Search Conditions in SQL Statements
Subqueries
Subqueries in the WHERE Clause
Subqueries in the FROM Clause (Derived Tables)
Derived Table Syntax
Subqueries in the Target List, SET and VALUE Clauses (Scalar Subqueries)
Scalar Subqueries
Subquery Behavior Change due to Scalar Subquery Feature
Common Table Expressions (CTEs)
Common Table Expression Format
Components of a CTE
How CTEs Are Used
Examples of Using CTEs: The Person and Relative Tables
Referring to a Defined WITH Element
Using Nested WITH Element References
WITH Elements in CREATE VIEW
Pivot Tables
PIVOT Format
Pivot Table Examples
Sales Summary
Range Pivoting
Pivot Table Usage Notes
Working with Embedded SQL
Embedded SQL Statements
How Embedded SQL Statements Are Processed
General Syntax and Rules of an Embedded SQL Statement
Syntax Conventions Used in this Chapter
Structure of an Embedded SQL Program
Host Language Variables in Embedded SQL
Variable Declaration
INCLUDE Statement
Variable Usage
Variable Structures
Dclgen Utility--Generate Structure
Indicator Variables
Indicator Variable Declaration
Null Indicators and Data Retrieval
Using Null Indicators to Assign Nulls
Indicator Variables and Character Data Retrieval
Null Indicator Arrays and Host Structures
Data Manipulation with Cursors
Example: Cursor Processing
Cursor Declaration
Open a Cursor
Readonly Cursors
Open Cursors and Transaction Processing
Fetch Data From Cursor
Fetch Rows Inserted by Other Queries
Using Cursors to Update Data
Cursor Modes
Cursor Position for Updates
Delete Data Using Cursors
Example: Updating and Deleting with Cursors
Closing Cursors
Summary of Cursor Positioning
Dynamically Specifying Cursor Names
Cursors Versus Select Loops
Dynamic Programming
SQLDA
Structure of the SQLDA
Including the SQLDA in a Program
DESCRIBE Statement and SQLDA
Data Type Codes
USING Clause
Dynamic SQL Statements
EXECUTE IMMEDIATE Statement
PREPARE and EXECUTE Statements
DESCRIBE Statement
Execute a Dynamic Non-select Statement
Using EXECUTE IMMEDIATE to Execute a Non-select Statement
Preparing and Executing a Non-select Statement
Execute a Dynamic SELECT Statement
Unknown Result Column Data Types
How Unknown Result Column Data Types are Handled
Prepare and Describe SELECT Statements
Sqlvar Elements
SELECT Statement with EXECUTE IMMEDIATE
Retrieve Results Using Cursors
Data Handlers for Large Objects
Errors in Data Handlers
Restrictions on Data Handlers
Large Objects in Dynamic SQL
Length Considerations
Data Handlers in Dynamic SQL
Example: PUT DATA Handler
Example: GET DATA Handler
Example: Dynamic SQL Data Handler
Ingres 4GL Interface
Working with Transactions and Handling Errors
Transactions
How Transactions Work
How Consistency is Maintained During Transactions
SET AUTOCOMMIT ON--Commit Individual Statement
How to Determine if You Are in a Transaction
Statements Used to Control Transactions
How Effects of a Transaction Are Controlled
Savepoints on Multi-statement Transactions
How the Transaction Processing System Handles Interrupts
Abort Policy for Transactions and Statements
How to Direct the DBMS to Roll Back an Entire Transaction or Statement
Effects of Aborted Transactions
Two-phase Commit
Statements that Support Two-phase Commit
Distributed Transaction ID
Coordinator Applications for a Two-phase Commit
Manual Termination of a Distributed Transaction
Example: Using Two-phase Commit
Ways to Obtain Status Information
SESSION_PRIV Function
DBMSINFO Function
DBMSINFO Examples
Request Names for DBMSINFO Function
INQUIRE_SQL Function
SQL Communications Area (SQLCA)
Variables that Compose SQLCA
SQLCODE and SQLSTATE
SQLCODE Variable
SQLSTATE Variable
Error Handling
Types of Error Codes
Error Message Format
Display of Error Messages
Error Handling in Embedded Applications
Error Information from SQLCA
SQLSTATE
Error Trapping Using WHENEVER Statement
How to Define an Error-Handling Function
Event Handlers and Message Handlers
Error Checking Using Inquire Statements
SET_SQL(PROGRAMQUIT)
Handling of Deadlocks
Example: Handling Deadlocks When Transactions Do Not Contain Cursors
Example: Handling Deadlocks with One Cursor
Example: Handling Deadlocks with Two Cursors
Understanding Database Procedures, Sessions, and Events
How Database Procedures Are Created, Invoked, and Executed
Benefits of Database Procedures
Contents of Database Procedures
Permissions on Database Procedures
Methods of Executing Procedures
How Parameters Are Passed in Database Procedures
Row-Producing Procedures
Format of a Row-Producing Procedure
How a Row-Producing Procedure Is Called
Row-Producing Procedure Example
Table Procedure
Table Procedure Restrictions
Table Procedure Example
Effects of Errors in Database Procedures
iierrornumber and iirowcount Variables
RAISE ERROR Statement
Messages from Database Procedures
Message Handling Using the WHENEVER Statement
Message Handling Using User-Defined Handler Routines
Rules
Examples: Database Procedures and Rules
AFTER Rule Example: The Audit Procedure and Rule
BEFORE Rule Example: The Audit Procedure and Rule
Multiple Session Connections
Multiple Sessions
Session Identification
Session Switching
Disconnection of Sessions
Status Information in Multiple Sessions
What You Should Know When Creating Multiple Sessions
Example: Two Open Sessions
Examples: Session Switching
Database Events
Example: Database Events in Conjunction with Rules
Database Event Statements
Create a Database Event
Raise a Database Event
Register Applications to Receive a Database Event
How a Database Event Is Received
Get a Database Event
How to Process Database Events
GET DBEVENT Statement Example
WHENEVER DBEVENT Statement
User-Defined Database Event Handlers
Remove a Database Event Registration
Drop a Database Event
Privileges and Database Events
Trace Database Events
Using External Tables (X100 Only)
Introduction to External Tables
How External Tables Work
External Table Requirements
Starting and Stopping the Spark Provider
Spark Provider Log
Configuring Logging
Spark Provider Configuration
Syntax for Defining an External Table
Reading and Writing to an External Table
How to Add Extra Packages
External Table Limitations
External Table Usage Notes
Working with JSON Data
Data Types
JSON Scalars
JSON Arrays
JSON Objects
JSON Concepts
Lax and Strict Modes
JSON Sequences
JSON Member Accessors
JSON API Common Syntax
JSON Predicates
EXISTS
Comparison Operators
SIMILAR TO
STARTS WITH
IS UNKNOWN
JSON Constructors
JSON_OBJECT
JSON_OBJECTAGG
JSON_ARRAY
JSON_ARRAYAGG
How JSON Dates Are Handled
JSON Query Functions
Use of Operators with JSON Data
Arithmetic Expressions
Booleans and Boolean Operators
Comparison Operators
JSON Item Methods
SQL Statements
Context for SQL Statements
Statements for Ingres Star
ALTER GROUP
ALTER GROUP Examples
ALTER LOCATION
ALTER LOCATION Examples
ALTER PROFILE
ALTER PROFILE Examples
ALTER ROLE
ALTER ROLE Examples
ALTER SECURITY_AUDIT
ALTER SECURITY_AUDIT Examples
ALTER SEQUENCE
ALTER SEQUENCE Examples
ALTER TABLE
ALTER TABLE...ALTER COLUMN Restrictions
Rules and Restrictions on Renaming Tables
Rules and Restrictions on Renaming Columns
Constraint Specifications
Named Constraints
Restrict and Cascade
ALTER TABLE Examples
ALTER USER
ALTER USER Examples
BEGIN DECLARE
BEGIN DECLARE Example
CALL
CALL Examples
CALL X100 (X100 Only)
CLEANUP_UNUSED_FILES Command
CONDENSE_LOG Command
FORCE_TRANSACTION_DISALLOW_COMMIT Command
SETCONF Command
TERMINATE Command
TERMINATE_IF_IDLE Command
TOTAL_DB_SIZE Command
VWLOG_RELOAD Command
VWLOG_ROTATE Command
CLOSE
CLOSE Example
COMMENT ON
COMMENT ON Examples
COMMIT
COMMIT Example
CONNECT
Connecting with Distributed Transactions
Creating Multiple Sessions
Using Session Identifiers
Using Connection Names
CONNECT Examples
CONTINUE
COPY
Unformatted Copying
Formatted Copying
Bulk Copying
Column Formats for COPY
Character (Text) Formats
Counted Character Formats
Unicode Formats
Dummy Format
Binary Formats
COPY Format Details
Delimiters in the Data File
WITH NULL Clause for COPY
Filename Specification for COPY
Windows File Types for COPY
WITH Clause Options for COPY
Permissions
Locking
Restrictions and Considerations
Related Statements
COPY Examples
COPY FROM | INTO PROGRAM
Row Formats
Fixed-length Formats
Variable-length Formats
COPY Arguments
Handler Code Examples
COPY INTO PROGRAM Using Bulk Format
COPY FROM PROGRAM Using Fixed-length Formats
COPY FROM PROGRAM Using Variable-length Formats
COPY VWLOAD (X100 Only)
COPY VWLOAD Example
CREATE DBEVENT
CREATE EXTERNAL TABLE (X100 Only)
CREATE EXTERNAL TABLE Examples
CREATE GROUP
CREATE GROUP Examples
CREATE INDEX
Description
Index Storage Structure for Actian X Tables
Unique Indexes
Effect of the Unique_Scope Option on Updates
Index Location
Parallel Index Building
Embedded Usage
Permissions
Locking
Related Statements
CREATE INDEX Examples
CREATE INTEGRITY
CREATE INTEGRITY Examples
CREATE LOCATION
CREATE LOCATION Examples
CREATE PROCEDURE
Description
Parameter Modes
Nullability and Default Values for Parameters
SET OF Parameters
Embedded Usage
Permissions
Related Statements
CREATE PROCEDURE Examples
CREATE PROFILE
CREATE PROFILE Examples
CREATE ROLE
CREATE ROLE Examples
CREATE RULE
Row and Statement Level Rules
Procedure Execution in CREATE RULE
Table_Condition
CREATE RULE Examples
CREATE SCHEMA
CREATE SCHEMA Example
CREATE SECURITY_ALARM
CREATE SECURITY_ALARM Examples
CREATE SEQUENCE
CREATE SEQUENCE Examples
CREATE STATISTICS
CREATE STATISTICS Example
CREATE SYNONYM
CREATE SYNONYM Examples
CREATE TABLE
Description
Column Specification--Define Column Characteristics
Default Clause
Null Clause
Null Clause and Default Clause Combinations
SYSTEM_MAINTAINED Logical Keys
Sequence Defaults
Masked Column Default Values
Identity Columns
Using CREATE TABLE...AS SELECT
Constraints
Unique Constraint
Check Constraint
Referential Constraint
Primary Key Constraint
Column-Level Constraints and Table-Level Constraints
Constraint WITH_Clause--Define Constraint Index Options
NO INDEX Option
INDEX = BASE TABLE STRUCTURE Option
INDEX = Index_name Option
Constraints and Integrities
WITH Clause for CREATE TABLE
[PAGE_SIZE Option
SECURITY_AUDIT Option
SECURITY_AUDIT_KEY Option
MINMAX_SAMPLES Option
WITH Clause for CREATE TABLE...AS SELECT
Partitioned Tables
Partitioning Schemes
Embedded Usage
Permissions
Locking
Related Statements
CREATE TABLE Examples
CREATE USER
CREATE USER Examples
CREATE VIEW
WITH CHECK OPTION Clause
CREATE VIEW Examples
DECLARE
DECLARE Example
DECLARE CURSOR
Cursor Updates
Cursor Modes
Embedded Usage
Usage in OpenAPI
Permissions
Locking
Related Statements
DECLARE CURSOR Examples
DECLARE GLOBAL TEMPORARY TABLE
Description
SESSION Schema Qualifier
Embedded Usage
Permissions
Temporary Table Restrictions
Related Statements
DECLARE GLOBAL TEMPORARY TABLE Examples
DECLARE STATEMENT
DECLARE STATEMENT Example
DECLARE TABLE
DECLARE TABLE Example
DELETE
Embedded Usage
Non-Cursor Delete
Cursor Delete
Permissions
Locking
Related Statements
DELETE Example
DESCRIBE
DESCRIBE Examples
DESCRIBE INPUT
DISABLE SECURITY_AUDIT
DISABLE SECURITY_AUDIT Example
DISCONNECT
DISCONNECT Examples
DROP
DROP Examples
DROP DBEVENT
DROP DBEVENT Example
DROP GROUP
DROP GROUP Examples
DROP INTEGRITY
DROP INTEGRITY Examples
DROP LOCATION
DROP LOCATION Example
DROP PERMIT ON SEQUENCE
DROP PERMIT ON SEQUENCE Example
DROP PROCEDURE
DROP PROCEDURE Example
DROP PROFILE
DROP PROFILE Example
DROP ROLE
DROP ROLE Example
DROP RULE
DROP RULE Example
DROP SCHEMA
DROP SCHEMA Example
DROP SECURITY_ALARM
DROP SECURITY_ALARM Examples
DROP SEQUENCE
DROP SEQUENCE Example
DROP STATISTICS
DROP STATISTICS Examples
DROP SYNONYM
DROP SYNONYM Example
DROP USER
DROP USER Example
ENABLE SECURITY_AUDIT
ENABLE SECURITY_AUDIT Example
ENDDATA
ENDDATA Examples
END DECLARE SECTION
ENDLOOP
ENDSELECT
ENDSELECT Example
EXECUTE
EXECUTE Examples
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE Examples
EXECUTE PROCEDURE
Passing Parameters - Non-Dynamic Version
Passing Parameters - Dynamic Version
Positional Parameters Sample Syntax
Temporary Table Parameter
Limitations of Temporary Table Parameter
Execute Procedure Loops
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Performance
Related Statements
EXECUTE PROCEDURE Examples
FETCH
FETCH Examples
FOR - ENDFOR
FOR - ENDFOR Example
GET DATA
GET DBEVENT
GRANT (privilege)
Object Privileges
TABLE Privileges
TABLE Privileges for Views
DATABASE Privileges
PROCEDURE Privileges
DBEVENT Privileges
SEQUENCE Privileges
Privilege Defaults
GRANT ALL PRIVILEGES Option
Installation and Database Privileges
Other Privileges
Granting All Privileges on Views
GRANT OPTION Clause
Embedded Usage
Permissions
Locking
Related Statements
GRANT (privilege) Examples
GRANT (role)
GRANT (role) Example
HELP
HELP Examples
IF - THEN - ELSE
IF Statement
IF Statement
IF...THEN Statement
IF...THEN...ELSEIF Statement
Nesting IF Statements
IF-THEN-ELSE Example
INCLUDE
INCLUDE Examples
INQUIRE_SQL
Obtain Logical Key Value with INQUIRE_SQL
Permissions
Related Statements
INQUIRE_SQL Examples
INSERT
Description
Embedded Usage
Permissions
Repeated Queries
Error Handling
Locking
Related Statements
INSERT Examples
INSERT INTO EXTERNAL CSV (X100 Only)
INSERT INTO EXTERNAL CSV Examples
MERGE
MERGE Examples
MESSAGE
MESSAGE Examples
MODIFY
Description
Storage Structure Specification
MODIFY...TO COMBINE
MODIFY...TO RECONSTRUCT
MODIFY...TO TRUNCATED
MODIFY...TO REORGANIZE
MODIFY...TO RELOCATE
MODIFY...TO MERGE
MODIFY...TO ADD_EXTEND
MODIFY...TO [NO]READONLY
MODIFY...TO PHYS_CONSISTENT | PHYS_INCONSISTENT
MODIFY...TO LOG_CONSISTENT | LOG_INCONSISTENT
MODIFY...TO TABLE_RECOVERY_ALLOWED | TABLE_RECOVERY_DISALLOWED
MODIFY...TO UNIQUE_SCOPE=STATEMENT|ROW
MODIFY...TO PRIORITY=n
MODIFY…TO ENCRYPT
MODIFY…PARTITION…TO SPLIT
MODIFY…PARTITION…TO MERGE
MODIFY…PARTITION…TO DROP
WITH Clause Options for MODIFY
ALLOCATION
EXTEND
FILLFACTOR, MINPAGES, and MAXPAGES
LEAFFILL and NONLEAFFILL
BLOB_EXTEND
LOCATION
COMPRESSION
(NO)PERSISTENCE
UNIQUE_SCOPE
PAGE_SIZE
NOPARTITION | PARTITION=
CONCURRENT_UPDATES
NODEPENDENCY_CHECK
PASSPHRASE=
Embedded Usage
Permissions
Locking
Related Statements
MODIFY Examples
OPEN
OPEN Examples
PREPARE
PREPARE Example
PREPARE TO COMMIT
PREPARE TO COMMIT Example
PUT DATA
RAISE DBEVENT
RAISE ERROR
RAISE ERROR Example
REGISTER DBEVENT
REGISTER TABLE
Description
Security Log File Format
Embedded Usage
Permissions
Locking
Related Statements
REGISTER TABLE Example
REMOVE DBEVENT
REMOVE TABLE
REMOVE TABLE Example
RENAME TABLE
RENAME TABLE Example
REPEAT - ENDREPEAT
REPEAT - ENDREPEAT Example
RETURN
RETURN Example
RETURN ROW
RETURN ROW Example
REVOKE
Revoking Grant Option
Restrict versus Cascade
Embedded Usage
Permissions
Locking
Related Statements
REVOKE Examples
ROLLBACK
SAVE
SAVE Example
SAVEPOINT
SAVEPOINT Example
SELECT (interactive)
SELECT Statement Clauses
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
HAVING Clause
ORDER BY Clause
FETCH FIRST Clause and OFFSET Clause
UNION Clause
INTERSECT Clause
EXCEPT Clause
WITH Clause for SELECT
Query Evaluation
Syntax for Specifying Tables and Views
Joins
Join Relationships
Subselects
ANSI/ISO Join Syntax
Hybrid Join Restrictions
Permissions
SELECT Locking
SELECT (interactive) Examples
SELECT (embedded)
Non-Cursor Select
Select Loops
INTO Clause--Retrieve Values into Host Language Variables
REPEATED Queries
Cursor Select
Error Handling for Embedded SELECT
Embedded Usage
Related Statements
SELECT (embedded) Examples
SET
AUTOCOMMIT
[NO]BATCH_COPY_OPTIM
[NO]CACHE_DYNAMIC
[NO]CARDINALITY_CHECK
CONNECTION
CPUFACTOR
DATE_ALIAS
DATE_FORMAT
DECIMAL
[NO]FIRSTVAL
[NO]FLATTEN
FLOAT_FORMAT
[NO]HASH
[NO]HASHAGG
[NO]HASHJOIN
INSERTMODE DEFAULT | ROW | BULK
[NO]IO_TRACE
JOINOP [NO]TIMEOUT
JOINOP TIMEOUTABORT
JOINOP [NO]GREEDY
[NO]JOURNALING
LOCKMODE
[NO]LOCK_TRACE
[NO]LOGDBEVENTS
[NO]LOGGING
[NO]LOG_TRACE
[NO]MAXCONNECT
[NO]MAXCOST
[NO]MAXCPU
[NO]MAXIDLE
[NO]MAXIO
[NO]MAXPAGE
[NO]MAXQUERY
[NO]MAXROW
MONEY_FORMAT
MONEY_PREC
NUMERIC_OVERFLOW
[NO]OJFLATTEN
[NO]OPTIMIZEONLY
[NO]PARALLEL
[NO]PRINTDBEVENTS
[NO]PRINTQRY
[NO]PRINTRULES
[NO]QEP [CONCISE | SEGMENTED]
RANDOM_SEED
RESULT_STRUCTURE
NOREUSE
ROLE
[NO]RULES
[NO]SCHEMA
SERVER_TRACE
SESSION
SESSION ADD PRIVILEGES
SESSION DROP PRIVILEGES
SESSION AUTHORIZATION
SESSION [NO]CACHE_DYNAMIC
SESSION COLLATION
SESSION [NO]OLD_COPY_DN
SESSION ISOLATION LEVEL
SESSION WITH [NO]DESCRIPTION
SESSION WITH ON_ERROR
SESSION WITH ON_LOGFULL
SESSION WITH ON_USER_ERROR
SESSION_PRIORITY
SESSION WITH [NO]PRIVILEGES
SESSION_TRACE
[NO]STATISTICS
STRING_TRUNCATION
[NO]TRACE OUTPUT
[NO]TRACE TERMINAL
[NO]TRACE POINT
TRANSACTION
TRANSACTION ISOLATION LEVEL
[NO]UNICODE_SUBSTITUTION
[NO]UNION_FLATTENING
UPDATE_ROWCOUNT
WORK LOCATIONS
Related Statements
SET Examples
SET_SQL
UPDATE
FROM Clause
WHERE Clause
Embedded Usage
Permissions
Cursor Updates
Locking
Related Statements
UPDATE Examples
WHENEVER
Embedded Usage
Permissions
Locking
Related Statements
WHENEVER Examples
WHILE - ENDWHILE
While - Endwhile Permissions
WHILE - ENDWHILE Example
WITH (common_table_expression)
Guidelines for Using CTEs
WITH (common_table_expression) Syntax
WITH (common_table_expression) Embedded Usage
Keywords
Reserved Keywords and Identifiers
Abbreviations Used in Keyword Lists
Reserved Single Word Keywords
Reserved Multi Word Keywords
Partition Keywords
ANSI/ISO SQL Keywords
A. Terminal Monitor
Terminal Monitors
sql Command--Access Line-based Terminal Monitor
Terminal Monitor Query Buffering
Terminal Monitor Commands
Terminal Monitor Messages and Prompts
Terminal Monitor Character Input and Output
The HELP Statement
Terminal Monitor Macros
Macro Concepts
System Macros
Macro Evaluation
Macro Parameters
Parameter Prescan
Using Tab or Newline
Use of Quotes
Use of Backslashes
Special {define} Processing
Terminal Monitor Macro Examples
B. SQL Statements from Earlier Releases
In This Appendix
Substitute Statements
ABORT Statement
ABORT Examples
BEGIN TRANSACTION Statement
BEGIN TRANSACTION Examples
CREATE PERMIT Statement
CREATE PERMIT Example
DROP PERMIT Statement
DROP PERMIT Example
END TRANSACTION Statement
END TRANSACTION Example
INQUIRE_INGRES Statement
RELOCATE Statement
RELOCATE Example
SET_INGRES Statement
C. SQLSTATE Values and Generic Error Codes
SQLSTATE Values
Generic Error Codes
Generic Error Data Exception Subcodes
SQLSTATE and Equivalent Generic Errors
D. Settings for Strict ANSI Compliance
In This Chapter
How Settings Are Determined
ISO_ENTRY_SQL-92 Parameter
Case Sensitivity for Identifiers
Regular Identifiers
Delimited Identifiers
User Names
Default Cursor Mode
Query Flattening
Connection Flags
-string_truncation Connection Flag
-numeric_overflow Connection Flag
ESQL Preprocessor Flags
-wsql ESQL Preprocessor Flag
-blank_pad ESQL Preprocessor Flag
-sqlcode
-check_eos (C only)
SQL Reference Guide
-check_eos (C only)