SQL Reference Guide
1. 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
2. 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
3. Understanding SQL Data Types
SQL Data Types
Character Data Types
C Data Types
Char Data Type
Text Data Types
Varchar Data Type
Long Varchar Data Types
Unicode 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
time_precision
time_zone_spec
Timestamp Data Types
timestamp_precision
time_zone_spec
Interval Data Types
interval_qualifier
Summary of ANSI Date/Time Data Types
Ingresdate Data Types
Coercion Between Date/Time Data Types
Abstract Data Types
Money Data Type
Numeric String Data Type
Logical Key Data Type
IP Network Address Data Types
Binary Data Types
Byte Data Types
Byte Varying Data Types
Long Byte Data Types
Risk of Hardware Dependant 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
date_value
Time Literals
time_value
Timestamp Literals
timestamp_value
Interval Literals
sign
interval_value
interval_qualifier
Boolean Literals
SQL Constants
Nulls
Nulls and Comparisons
Nulls and Aggregate Functions
Nulls and Integrity Constraints
4. 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
Random Number Functions
IP Network Address Functions
Aggregate Functions
Basic Aggregate Functions
Regression and Correlation Analysis 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
window_function
Windowing Aggregate Functions
Analytical Functions
Null Handling Functions
Universal Unique Identifier (UUID)
UUID Format
UUID Functions
Encryption Functions
Spatial Functions
Expressions in SQL
CASE Expressions
DECODE Function
IF, NULLIF, and COALESCE Functions
GREATEST, GREATER, LEAST, LESSER Functions
CAST Expressions
Sequence Expressions
NEXT VALUE FOR, CURRENT VALUE FOR
LAST_IDENTITY Function
Locking and Sequences
Predicates in SQL
Comparison Predicate
Pattern-matching Predicates
pattern
pattern
LIKE Predicate
BEGINNING, CONTAINING, and ENDING Predicates
expression
pattern
SIMILAR TO Predicate
expression
pattern
literal
BETWEEN Predicate
IN Predicate
Any-or-All Predicate
EXISTS Predicate
IS NULL Predicate
IS INGRESDATE Predicate
IS INTEGER Predicate
IS DECIMAL Predicate
IS FLOAT Predicate
IS TRUE, IS FALSE, IS UNKNOWN Predicates
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
5. 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
select_statement;
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
6. 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--Determine If Session Has a Privilege
DBMSINFO Function--Return Information About the Current Session
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)--Specify Whether to Abort on Error
Handling of Deadlocks
Example: Handling Deadlocks When Transactions Do Not Contain Cursors
Example: Handling Deadlocks with One Cursor
Example: Handling Deadlocks with Two Cursors
7. 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
8. SQL Statements
Context for SQL Statements
Statements for Ingres Star
ALTER GROUP
Syntax
Embedded Usage
Permissions
Locking
Related Statements
ALTER GROUP Examples
ALTER LOCATION
Syntax
location name
Embedded Usage
Permissions
Locking
Related Statements
ALTER LOCATION Examples
ALTER PROFILE
Syntax
priv
audit_opt
priv
Embedded Usage
Permissions
Locking
Related Statements
ALTER PROFILE Examples
ALTER ROLE
Syntax
role_id
priv
role_password
audit_opt
Embedded Usage
Permissions
Locking
Related Statements
ALTER ROLE Examples
ALTER SECURITY_AUDIT
Syntax
Embedded Usage
Permissions
Related Statements
ALTER SECURITY_AUDIT Examples
ALTER SEQUENCE
Syntax
sequence_options
Permissions
Locking and Sequences
Related Statements
ALTER SEQUENCE Examples
ALTER TABLE
Syntax
Alter Table... Alter Column Restrictions
Rules and Restrictions on Renaming Tables
Rules and Restrictions on Renaming Columns
Constraint Specifications
Named Constraints
constraint_name
Restrict and Cascade
Embedded Usage
Permissions
Locking
Related Statements
ALTER TABLE Examples
ALTER USER
Syntax
user_name
priv
default group
audit_opt
expire_date
profile_name
user_password
oldpassword
Embedded Usage
Permissions
Locking
Related Statements
Alter User Examples
BEGIN DECLARE
Syntax
Description
Permissions
Related Statements
BEGIN DECLARE Example
CALL
Syntax
command_string
subsystem
dbname
parameter
value
Call Description
Permissions
CALL Examples
CLOSE
Syntax
cursor_name
Description
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Related Statements
CLOSE Example
COMMENT ON
Syntax
table_name
remark_text
Description
Embedded Usage
Permissions
Locking
Related Statements
COMMENT ON Examples
COMMIT
Syntax
Description
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Performance
Related Statements
COMMIT Example
CONNECT
Syntax
dbname
connection_name
session_number
username
dbms_password
flag
value
Description
Connecting with Distributed Transactions
Creating Multiple Sessions
Using Session Identifiers
Using Connection Names
Permissions
Locking
Related Statements
CONNECT Examples
COPY
Syntax
table_name
column_name
format
filename
type
with_clause
Unformatted Copying
Formatted Copying
Bulk Copying
Column Formats for COPY
Character (Text) Formats
Counted Character Formats
Dummy Format
Unicode Formats
Binary Formats
COPY Format Details
Delimiters in the Data File
WITH NULL Clause for COPY
Filename Specification for COPY
Windows File Types for COPY
VMS File Types for COPY
With Clause Options for COPY
Permissions
Locking
Restrictions and Considerations
Related Statements
COPY Examples
COPY FROM | INTO PROGRAM
Syntax
table_name
column_name
format
func_name
with_clause
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
CREATE DBEVENT
Syntax
event_name
Description
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Related Statements
CREATE GROUP
Syntax
group id
user id
Embedded Usage
Permissions
Locking
Related Statements
CREATE GROUP Examples
CREATE INDEX
Syntax
index_name
table_name
column_name
Description
Index Storage Structure
Unique Indexes
Effect of the Unique_Scope Option on Updates
Index Location
Parallel Index Building
Embedded Usage
location_name
N
Permissions
Locking
Related Statements
CREATE INDEX Examples
CREATE INTEGRITY
Syntax
table_name
corr name
search condition
Locking
Performance
Embedded Usage
Permissions
Related Statements
CREATE INTEGRITY Examples
CREATE LOCATION
Syntax
location_name
area_name
usage_type
Embedded Usage
Permissions
Locking
Related Statements
CREATE LOCATION Examples
CREATE PROCEDURE
Create Procedure Syntax
proc name
set_param_name
param_name
param_mode
param_type
declare_section
statement
result_row_name
result_column_name
result_type
Description
Parameter Modes
Nullability and Default Values for Parameters
SET OF Parameters
Embedded Usage
Permissions
Related Statements
CREATE PROCEDURE Examples
CREATE PROFILE
Syntax
profile_name
priv
default_group
audit_opt
expire_date
Description
Embedded Usage
Permissions
Locking
Related Statements
CREATE PROFILE Examples
CREATE ROLE
Syntax
role_id
role_password
priv
Embedded Usage
Permissions
Locking
Related Statements
CREATE ROLE Examples
CREATE RULE
Syntax
rule_name
table_condition
proc_name
parameter
value
Row and Statement Level Rules
Procedure Execution in Create Rule
Table_Condition
statement_type
table_name
old_corr_name
new_corr_name
qualification
Embedded Usage
Permissions
Locking
Related Statements
CREATE RULE Examples
CREATE SCHEMA
Syntax
schema_name
object_definition
Description
Embedded Usage
Permissions
Locking
Related Statements
CREATE SCHEMA Example
CREATE SECURITY_ALARM
Syntax
object_name
Embedded Usage
Permissions
Locking
Related Statements
CREATE SECURITY_ALARM Examples
CREATE SEQUENCE
Syntax
sequence_name
sequence_options
Permissions
Locking and Sequences
Related Statements
CREATE SEQUENCE Examples
CREATE SYNONYM
Syntax
synonym_name
Embedded Usage
Permissions
Locking
Related Statements
CREATE SYNONYM Examples
CREATE TABLE
Syntax
table_name
table_constraint
Description
Column Specification--Define Column Characteristics
column_name
column_name
datatype
Default Clause
Null Clause
With|Not Null and With|Not Default Combinations
System_Maintained Logical Keys
Sequence Defaults
Identity Columns
Using Create Table...As Select
Constraints
Unique Constraint
Check Constraint
Referential Constraint
referential actions
Primary Key Constraint
Column-Level Constraints and Table-Level Constraints
constraint_name
constraint
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
encryption type
encryption type
Page_size Option
Security_audit Option
With_Clause for Create Table...As Select
Partitioned Tables
Partitioning Schemes
rule
with_clause
Embedded Usage
Permissions
Locking
Related Statements
Create Table Examples
CREATE USER
Syntax
user_name
priv
audit_opt
profile_name
user_password
Embedded Usage
Permissions
Locking
Related Statements
Create User Examples
CREATE VIEW
Syntax
view_name
select_stmt
Description
With Check Option Clause
Embedded Usage
Permissions
Locking
Related Statements
CREATE VIEW Examples
DECLARE
Syntax
var_name
var_type
Permissions
Related Statements
DECLARE Example
DECLARE CURSOR
Syntax
cursor_name
Description
Cursor Updates
Cursor Modes
Embedded Usage
Usage in OpenAPI
Permissions
Locking
Related Statements
DECLARE CURSOR Examples
DECLARE GLOBAL TEMPORARY TABLE
Syntax
table_name
with_clause
Description
SESSION Schema Qualifier
Embedded Usage
Permissions
Temporary Table Restrictions
Related Statements
DECLARE GLOBAL TEMPORARY TABLE Examples
DECLARE STATEMENT
Syntax
Related Statements
DECLARE STATEMENT Example
DECLARE TABLE
Syntax
Description
Permissions
DECLARE TABLE Example
DELETE
Syntax
table_name
corr_name
Embedded Usage
Non-Cursor Delete
Cursor Delete
Permissions
Locking
Related Statements
DELETE Example
DESCRIBE
Syntax
statement_name
descriptor name
Description
DESCRIBE Examples
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Related Statements
DESCRIBE INPUT
Syntax
descriptor-name
DISABLE SECURITY_AUDIT
Disable Security_Audit Syntax
audit_type
Embedded Usage
Permissions
Locking
Related Statements
DISABLE SECURITY_AUDIT Example
DISCONNECT
Syntax
connection_name
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Related Statements
DISCONNECT Examples
DROP
Syntax
object_type
object_name
Description
Embedded Usage
Permissions
Locking
Related Statements
DROP Examples
DROP DBEVENT
Syntax
Embedded Usage
Permissions
Related Statements
DROP DBEVENT Example
DROP GROUP
Syntax
Embedded Usage
Permissions
Locking
Related Statements
DROP GROUP Examples
DROP INTEGRITY
Syntax
table_name
integer (,integer)
Embedded Usage
Permissions
Related Statements
DROP INTEGRITY Examples
DROP LOCATION
Syntax
Embedded Usage
Permissions
Locking
Related Statements
DROP LOCATION Example
DROP PROCEDURE
Syntax
proc_name
Embedded Usage
Permissions
Related Statements
DROP PROCEDURE Example
DROP PROFILE
Syntax
Permissions
Locking
Related Statements
DROP PROFILE Example
DROP ROLE
Syntax
role_id
Embedded Usage
Permissions
Locking
Related Statements
DROP ROLE Example
DROP RULE
Syntax
Embedded Usage
Permissions
Related Statements
DROP RULE Example
DROP SCHEMA
Syntax
Embedded Usage
Permissions
DROP SCHEMA Example
DROP SECURITY_ALARM
Syntax
Embedded Usage
Permissions
Locking
Related Statements
DROP SECURITY_ALARM Examples
DROP SEQUENCE
Syntax
sequence_name
Permissions
Locking and Sequences
Related Statements
DROP SEQUENCE Example
DROP SYNONYM
Syntax
synonym_name
Embedded Usage
Permissions
Locking
Related Statements
DROP SYNONYM Example
DROP USER
Syntax
Embedded Usage
Locking
Related Statements
DROP USER Example
ENABLE SECURITY_AUDIT
Enable Security_Audit Syntax
audit_type
Embedded Usage
Permissions
Locking
Related Statements
ENABLE SECURITY_AUDIT Example
ENDDATA
Syntax
Permissions
ENDDATA Examples
END DECLARE SECTION
Syntax
Permissions
Related Statements
ENDSELECT
Syntax
Description
Permissions
Locking
Related Statements
ENDSELECT Example
EXECUTE
Syntax
statement_name
Description
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Related Statements
EXECUTE Examples
EXECUTE IMMEDIATE
Syntax
Description
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Related Statements
EXECUTE IMMEDIATE Examples
EXECUTE PROCEDURE
Syntax
proc_name
global temporary table_name
param_spec
Description
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
Syntax
Description
Readonly Cursors and Performance
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Fetch Locking
Related Statements
FETCH Examples
FOR - ENDFOR
Syntax
Description
Permissions
FOR - ENDFOR Example
GET DATA
Syntax
col_value
length_value
dataend_value
maxlength_value
Permissions
Related Statements
GET DBEVENT
Syntax
Usage in OpenAPI
Permissions
Related Statements
GRANT (privilege)
Syntax
privilege
object_type
object_name
auth_type
auth_id
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)
Syntax
Permissions
Related Statements
GRANT (role) Example
HELP
Syntax
objectname
Wildcards in Help Statement
Permissions
Locking
Related Statements
HELP Examples
IF - THEN - ELSE
Syntax
Description
If Statement
If...Then Statement
If...Then...Elseif Statement
Nesting IF Statements
Permissions
If-Then-Else Example
INCLUDE
Syntax
Description
Permissions
Related Statements
INCLUDE Examples
INQUIRE_SQL
Syntax
variable
object
Description
Obtain Logical Key Value with Inquire_sql
Permissions
Related Statements
INQUIRE_SQL Examples
INSERT
Syntax
Description
Embedded Usage
Permissions
Repeated Queries
Error Handling
Locking
Related Statements
INSERT Examples
MESSAGE
Syntax
message_text
Permissions
Related Statements
MESSAGE Examples
MODIFY
Syntax
modify-action
with_clause
Description
Syntax for Modify Operations
Storage Structure Specification
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
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
Syntax
Description
Permissions
Locking
Related Statements
OPEN Examples
PREPARE
Syntax
Description
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Related Statements
PREPARE Example
PREPARE TO COMMIT
Syntax
value
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Related Statements
PREPARE TO COMMIT Example
PUT DATA
Syntax
col value
length_value
dataend_value
Permissions
Related Statements
RAISE DBEVENT
Syntax
event_name
Description
Embedded Usage
Permissions
Related Statements
RAISE ERROR
Syntax
errornumber
errortext
Permissions
Related Statements
RAISE ERROR Example
REGISTER DBEVENT
Syntax
Embedded Usage
Permissions
Related Statements
REGISTER TABLE
Syntax
table_name
Description
Security Log File Format
Embedded Usage
Permissions
Locking
Related Statements
REGISTER TABLE Example
REMOVE DBEVENT
Syntax
Remove Dbevent Description
Permissions
Related Statements
REMOVE TABLE
Syntax
Description
Embedded Usage
Permissions
Locking
Related Statements
REMOVE TABLE Example
RENAME TABLE
Syntax
RENAME TABLE Example
REPEAT - ENDREPEAT
Syntax
Description
Permissions
REPEAT - ENDREPEAT Example
RETURN
Syntax
Permissions
RETURN Example
RETURN ROW
Syntax
Permissions
Related Statements
RETURN ROW Example
REVOKE
Syntax
privilege
object_type
object_name
auth_type
auth_id
Revoking Grant Option
Restrict versus Cascade
Embedded Usage
Permissions
Locking
Related Statements
REVOKE Examples
ROLLBACK
Syntax
Description
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, .NET
Permissions
Locking
Performance
Related Statements
SAVE
Syntax
month
day
Embedded Usage
Permissions
Locking
SAVE Example
SAVEPOINT
Syntax
savepoint_name
Embedded Usage
Usage in OpenAPI, JDBC
Permissions
Related Statements
SAVEPOINT Example
SELECT (interactive)
Select (interactive) Syntax
Description
Select Statement Clauses
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
column list
HAVING Clause
ORDER BY Clause
FETCH FIRST Clause and OFFSET Clause
UNION Clause
WITH Clause for SELECT
Query Evaluation
Syntax for Specifying Tables and Views
Joins
Join Relationships
Subselects
ANSI/ISO Join Syntax
source
join_type
Permissions
Select Locking
SELECT (interactive) Examples
SELECT (embedded)
Select (embedded) Syntax
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
Syntax
Embedded Usage
Usage in OpenAPI, ODBC, JDBC, and .NET
Permissions
Autocommit
[No]Batch_Copy_Optim
[No]Cache_dynamic
[No]Cardinality_check
Connection
Cpufactor
Date_alias
Date_format
Decimal
[No]Firstval
[No]Flatten
[No]Hash
[No]Hashagg
[No]Hashjoin
[No]Io_trace
Joinop [No]Timeout
Joinop Timeoutabort
Joinop [No]Greedy
[No]Journaling
Lockmode
n
n
[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
Random_seed
Result_Structure
Role
[No]Rules
Session Access Mode
Session Add Privileges
Session Authorization
username
Session [No]Cache_Dynamic
Session Collation
Session Drop Privileges
Session Isolation Level
Session With [No]Description
Session With On_error
Session With On_logfull
Session With On_user_error
Session With Priority
priority
Session With [No]Privileges
[No]Statistics table_name
String_truncation
[No]Trace Output
[No]Trace Point
Transaction Access Mode
Transaction Isolation Level
[No]Unicode_substitution
Update_Rowcount
Work Locations
Related Statements
SET Examples
SET_SQL
Syntax
Permissions
Related Statements
UPDATE
Syntax
Description
FROM Clause
Embedded Usage
Permissions
Cursor Updates
Locking
Related Statements
UPDATE Examples
WHENEVER
Syntax
action
Embedded Usage
Permissions
Locking
Related Statements
WHENEVER Examples
WHILE - ENDWHILE
Syntax
Description
While - Endwhile Permissions
WHILE - ENDWHILE Example
WITH (common_table_expression)
Guidelines for Using CTEs
WITH (common_table_expression) Syntax
expr_name
column_name
CTE_query_def
WITH (common_table_expression) Embedded Usage
9. 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
expression
operators
value
label
Terminal Monitor Messages and Prompts
Terminal Monitor Character Input and Output
The HELP Statement
Aborting the Editor (VMS only)
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 Chapter
Substitute Statements
Abort Statement
Examples: Abort
Begin Transaction Statement
Examples: Begin Transaction
Create Permit Statement
optlist
optlist
column_name
user_name
Example: Create Permit
Drop Permit Statement
Embedded Usage: Drop Permit
Locking
Drop Permit Example
End Transaction Statement
Example: End Transaction
Inquire_ingres Statement
Relocate Statement
location_name
location_name
Example: Relocate
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)
Ingres 10.2
-check_eos (C only)