Download SQL Quick Syntax Guide

SQL Quick Syntax
Guide
Version 7.2
April 1996
Part No. 000-7880A
Published by INFORMIX® Press
Informix Software, Inc.
4100 Bohannon Drive
Menlo Park, CA 94025
The following are worldwide trademarks of Informix Software, Inc., or its subsidiaries, registered in the
United States of America as indicated by an “®,” and in numerous other countries worldwide:
INFORMIX®; INFORMIX®-OnLine Dynamic Server; C-ISAM®
The following are worldwide trademarks of the indicated owners or their subsidiaries, registered in the
United States of America as indicated by an “®,” and in numerous other countries worldwide:
X/Open Company Ltd.: UNIX®; X/Open®
Adobe Systems Incorporated: PostScript®
Some of the products or services mentioned in this document are provided by companies other than Informix.
These products or services are identified by the trademark or servicemark of the appropriate companies. If you
have a question about one of these products or services, please contact the company in question directly.
Documentation Team:
Diana Chase, Geeta Karmarkar, Tom Noronha
Copyright © 1981-1996 by Informix Software, Inc. All Rights Reserved.
No part of this work covered by the copyright hereon may be reproduced or used in any form or by any
means—graphic, electronic, or mechanical, including photocopying, recording, taping, or information storage
and retrieval systems—without permission of the publisher.
RESTRICTED RIGHTS LEGEND
Software and accompanying materials acquired with United States Federal Government funds or intended for
use within or for any United States federal agency are provided with “Restricted Rights” as defined in DFARS
252.227-7013(c)(1)(ii) or FAR 52.227-19.
ii
SQL Quick Syntax Guide
Introduction
The following statements and segments for Version 7.2 are presented in this
guide:
■
SQL statements
■
SQL segments
■
Stored Procedure Language (SPL) statements
■
INFORMIX-OnLine/Optical statements
This guide presents the syntax diagrams for statements and segments but
does not provide any explanatory information. For complete descriptions of
SQL and SPL statements and segments, see the Informix Guide to SQL: Syntax.
For complete descriptions of INFORMIX-OnLine/Optical statements, see the
INFORMIX-OnLine/Optical User Manual. These manuals provide the following information for each statement:
■
A brief introduction that explains the purpose of the statement
■
A syntax diagram that is identical to the syntax diagram in the
SQL Quick Syntax Guide
■
A syntax table that provides the purpose, restrictions, and syntax for
all italicized elements in a syntax diagram
■
Rules and examples of usage
■
References to related statements
Introduction
3
Syntax Conventions
Syntax Conventions
This section describes conventions for syntax diagrams. Each diagram
displays the sequences of required and optional keywords, terms, and
symbols that are valid in a given statement, command line, or other
specification, as in the following diagram of the SET EXPLAIN statement.
+
SET EXPLAIN
ON
OFF
Each syntax diagram begins at the upper left corner and ends at the upper
right corner with a vertical terminator. Between these points, any path that
does not stop or reverse direction describes a possible form of the statement.
(For a few diagrams, notes in the text identify path segments that are
mutually exclusive.)
Syntax elements in a path represent terms, keywords, symbols, and segments
that can appear in your statement. Except for separators in loops, which the
path approaches counterclockwise from the right, the path always
approaches elements from the left and continues to the right. Unless
otherwise noted, at least one blank character separates syntax elements.
4
INFORMIX
Elements That Can Appear on the Path
Elements That Can Appear on the Path
You might encounter one or more of the following elements on a path.
Element
Description
KEYWORD
A word in UPPERCASE characters is a keyword. You
must spell the word exactly as shown; however, you
can use either uppercase or lowercase characters.
(.,;@+*-/)
Punctuation and other non-alphanumeric characters
are literal symbols that you must enter exactly as
shown.
' '
Single quotes are literal symbols that you must enter
as shown.
variable
A word in italics represents a value that you must
supply. A table immediately following the diagram
explains the value.
ADD
Clause
p. 1-14
ADD Clause
CREATE OPTICAL
CLUSTER
Statement
see INFORMIXOnLine/Optical
User Manual
OL
A term shown in a rectangle represents a subdiagram
on the same page (if no page number is supplied) or a
specified page, as if the subdiagram were spliced into
the diagram at this point. The same subdiagram can be
represented by rectangles of different shapes, as in
these symbols for the ADD Clause subdiagram.
This reference represents a statement described in the
INFORMIX-OnLine/Optical User Manual. Imagine
that the statement is spliced into the diagram at this
point.
An icon is a warning that this path is valid only for
some products, or only under certain conditions.
Characters on the icons indicate what products or
conditions support the path.
These icons might appear in a syntax diagram:
OL
This path is valid only for
INFORMIX-OnLine Dynamic Server.
SE
This path is valid only for INFORMIX-SE.
(1 of 3)
Introduction
5
Elements That Can Appear on the Path
Element
Description
D/B
This path is valid only for DB-Access.
ESQL
This path is valid only for SQL statements in INFORMIX-ESQL/C and
INFORMIX-ESQL/COBOL.
E/C
ALL
This path is valid only for
INFORMIX-ESQL/C.
E/CO
This path is valid only for
INFORMIX-ESQL/COBOL.
SPL
This path is valid only for only if you are
using Informix Stored Procedure
Language (SPL).
OP
This path is valid only for
INFORMIX-OnLine/Optical.
+
This path is an Informix extension to
ANSI SQL-92 entry-level standard SQL. If
you initiate Informix extension checking
and include this syntax branch, you
receive a warning. If you have set the
DBANSIWARN environment variable at
compile time, or have used the -ansi
compile flag, you receive warnings at
compile time. If you have DBANSIWARN
set at runtime, or if you compiled with
the -ansi flag, warning flags are set in the
sqlwarn structure.
GLS
This path is valid only only if your
database or application is GLS compliant.
A shaded option is the default. If you do not specify
any of the available options, this option is in effect by
default.
Syntax that is enclosed between a pair of arrows is
a subdiagram.
(2 of 3)
6
INFORMIX
Elements That Can Appear on the Path
Element
Description
The vertical line is a terminator. This symbol only
appears at the right, indicating that the syntax
diagram is complete.
IS
NULL
NOT
NOT FOUND
ERROR
A branch below the main path indicates an optional
path. (Any term on the main path is required, unless
a branch can circumvent it.)
A set of multiple branches indicates that a choice
among more than two different paths is available.
WARNING
,
variable
A loop indicates a path that you can repeat.
Punctuation along the top of the loop indicates the
separator symbol for list items, as in this example. If no
symbol appears, a blank space is the separator,
statement
,
3
size
A gate ( 3 ) on a path indicates that you can only use
that path the indicated number of times, even if it is
part of a larger loop. Here you can specify size no more
than three times within this statement segment.
(3 of 3)
Introduction
7
How to Read a Syntax Diagram
How to Read a Syntax Diagram
Figure 1 shows a syntax diagram that uses most of the path elements that are
listed in the previous table.
Figure 1
Elements of a Syntax Diagram
Main diagram
CREATE DATABASE database name
OL
IN dbspace
Subdiagram
OL
OL Log Clause
SE
SE Log Clause
OL Log Clause
WITH
LOG
BUFFERED
LOG MODE ANSI
SE Log Clause
WITH LOG IN 'pathname'
MODE ANSI
To use this diagram to construct a statement, start at the top left with the
keywords CREATE DATABASE. Then follow the diagram to the right,
proceeding through the options that you want.
8
INFORMIX
Sample-Code Conventions
To read the example syntax diagram
1.
You must type the words CREATE DATABASE.
2.
You must supply a database name.
3.
You can stop, taking the direct route to the terminator, or you can
take one or more of the optional paths.
4.
If desired, you can designate a dbspace by typing the word IN and a
dbspace name.
5.
If desired, you can specify logging. Here, you are constrained by the
database server with which you are working:
6.
❑
If you are using INFORMIX-OnLine Dynamic Server, go to the
subdiagram named OL Log Clause. Follow the subdiagram by
typing the keyword WITH, then choosing and typing either LOG,
BUFFERED LOG, or LOG MODE ANSI. Then follow the arrow back
to the main diagram.
❑
If you are using INFORMIX-SE, go to the subdiagram named SE
Log Clause. Follow the subdiagram by typing the keywords
WITH LOG IN, typing a quote, supplying a pathname, and closing the quotes. You can then choose the MODE ANSI option
below the line or continue to follow the line across.
Once you are back at the main diagram, you come to the terminator.
Your CREATE DATABASE statement is complete.
Sample-Code Conventions
Examples of SQL code occur throughout this manual. Except where noted,
the code is not specific to any single Informix application development tool.
If only SQL statements are listed in the example, they are not delimited by
semicolons. To use this SQL code for a specific product, you must apply the
syntax rules for that product. For example, if you are using the Querylanguage option of DB-Access, you must delimit multiple statements with
semicolons. If you are using an SQL API, you must use EXEC SQL and a
semicolon (or other appropriate delimiters) at the start and end of each
statement, respectively.
Introduction
9
Section
SQL Statements
ALLOCATE DESCRIPTOR
ALLOCATE DESCRIPTOR
+
ESQL
ALLOCATE
DESCRIPTOR
' descriptor '
descriptor
variable
WITH MAX
occurrences
occurrences
variable
ALTER FRAGMENT
OL
+
ALTER FRAGMENT ON
TABLE
INDEX
surviving table
surviving index
ATTACH
Clause
p. 14
DETACH
Clause
p. 14
INIT
Clause
p. 14
ADD
Clause
p. 16
DROP
Clause
p. 16
MODIFY
Clause
p. 16
SQL Statements 13
ALTER FRAGMENT
ATTACH Clause
ATTACH
Clause
,
ATTACH
consumed table
,
AS frag-expression
BEFORE
surviving table
AFTER
AS frag-expression
DETACH
Clause
dbspace-name
new table
INIT Clause
INIT
Clause
INIT
WITH ROWIDS
FRAGMENT
BY
Clause
for Tables
FRAGMENT
BY
Clause
for Indexes
IN dbspace
14
SQL Quick Syntax Guide
1
AS REMAINDER
DETACH Clause
DETACH
dbspace
ALTER FRAGMENT
FRAGMENT BY Clause for Tables
FRAGMENT
BY
Clause
for Tables
,
FRAGMENT BY
ROUND ROBIN IN
dbspace
,
dbspace
,
EXPRESSION
frag-expression
IN dbspace
,
frag-expression
IN dbspace
REMAINDER IN
remainder dbspace
,
FRAGMENT BY Clause for Indexes
FRAGMENT
BY
Clause
for Indexes
FRAGMENT BY
,
EXPRESSION
frag-expression
IN dbspace
,
frag-expression
IN dbspace
,
REMAINDER IN
remainder dbspace
SQL Statements 15
ALTER FRAGMENT
ADD Clause
ADD
Clause
new dbspace
ADD
frag-expression IN new dbspace
BEFORE
existing dbspace
AFTER
REMAINDER IN new dbspace
DROP Clause
DROP
Clause
dbspace-name
DROP
MODIFY Clause
MODIFY
Clause
MODIFY
,
mod-dbspace
TO
frag-expression IN new-dbspace
1
16
SQL Quick Syntax Guide
REMAINDER IN new-dbspace
ALTER INDEX
ALTER INDEX
+
Index Name
p. 104
ALTER INDEX
TO
CLUSTER
NOT
ALTER TABLE
,
+
ALTER TABLE
Table Name
p. 108
ADD Clause
p. 18
Synonym
Name
p. 108
DROP Clause
p. 21
MODIFY Clause
p. 21
ADD CONSTRAINT
Clause p. 22
DROP CONSTRAINT
Clause p. 23
OL
1
MODIFY NEXT SIZE
Clause p. 23
1
LOCK MODE
Clause p. 23
1
ADD ROWIDS
DROP ROWIDS
SQL Statements 17
ALTER TABLE
ADD Clause
ADD Clause
Add Column
Clause
ADD
,
(
Add Column
Clause
)
Add Column Clause
Add Column
Clause
new
column
name
18
Data Type
p. 94
SQL Quick Syntax Guide
DEFAULT
Clause
p. 19
New Column
Constraint
Definition
p. 19
BEFORE
column
name
ALTER TABLE
DEFAULT Clause
DEFAULT
Clause
DEFAULT
literal
NULL
CURRENT
p. 97
DATETIME
Field Qualifier
p. 95
USER
p. 97
TODAY
p. 97
SITENAME
p. 97
DBSERVERNAME
p. 97
New Column Constraint Definition
New Column
Constraint
Definition
UNIQUE
+
NOT
NULL
+
DISTINCT
+
ConstraintMode
Definitions
p. 20
PRIMARY
KEY
ConstraintMode
Definitions
p. 20
REFERENCES
Clause
p. 20
CHECK
Clause
p. 20
SQL Statements 19
ALTER TABLE
Constraint-Mode Definitions
Constraint-Mode
Definitions
CONSTRAINT
Constraint
Name
p. 93
DISABLED
ENABLED
FILTERING
WITHOUT
ERROR
WITH
ERROR
REFERENCES Clause
REFERENCES
Clause
table name
REFERENCES
OL
+
,
(
column
name
)
ON DELETE CASCADE
CHECK Clause
CHECK
Clause
CHECK
20
SQL Quick Syntax Guide
(
Condition
p. 89
)
ALTER TABLE
DROP Clause
DROP
Clause
DROP
column name
,
(
column name
)
MODIFY Clause
MODIFY
Clause
Modify Column
Clause
MODIFY
,
(
Modify Column
Clause
)
Modify Column Clause
Modify Column
Clause
column
name
Data Type
p. 94
DEFAULT
Clause
p. 19
,
New Column
Constraint
Definition
p. 19
SQL Statements 21
ALTER TABLE
ADD CONSTRAINT Clause
ADD CONSTRAINT
Clause
Table-Level
Constraint
Definition
ADD CONSTRAINT
,
(
)
Table-Level
Constraint
Definition
Table-Level Constraint Definition
Table-Level
Constraint Definition
,
(
UNIQUE
+
column
name
)
Constraint-Mode
Definitions
p. 20
DISTINCT
PRIMARY
KEY
FOREIGN KEY
,
(
column
name
CHECK
Clause
p. 20
22
SQL Quick Syntax Guide
+
)
REFERENCES
Clause
p. 20
ALTER TABLE
DROP CONSTRAINT Clause
DROP CONSTRAINT
Clause
Constraint
Name
p. 93
DROP CONSTRAINT
,
(
Constraint
Name
p. 93
)
MODIFY NEXT SIZE Clause
MODIFY NEXT SIZE
Clause
MODIFY NEXT SIZE
kbytes
LOCK MODE Clause
LOCK MODE
Clause
LOCK MODE
(
PAGE
)
ROW
SQL Statements 23
BEGIN WORK
BEGIN WORK
+
BEGIN WORK
CHECK TABLE
SE
DB
+
CHECK TABLE
CLOSE
ESQL
cursor id
CLOSE
CLOSE DATABASE
+
CLOSE DATABASE
COMMIT WORK
COMMIT WORK
24
SQL Quick Syntax Guide
Table
Name
p. 108
CONNECT
CONNECT
+
Database
Environment
CONNECT TO
ESQL
ESQL
AS
' connection
name '
AS
conn_nm
variable
USER
Clause
p. 26
DEFAULT
ESQL
WITH CONCURRENT TRANSACTION
Database Environment
Database
Environment
'dbname'
'@dbservername'
'dbname@dbservername'
ESQL
db_env variable
SE
'pathname/dbname'
'pathname/dbname@dbservername'
SQL Statements 25
CREATE AUDIT
USER Clause
USER
Clause
USER
' user identifier '
auth variable
USING
user_id
variable
CREATE AUDIT
SE
+
Table
Name
p. 108
CREATE AUDIT FOR
IN
' pathname '
Synonym
Name
p. 108
CREATE DATABASE
+
CREATE
DATABASE
Database
Name
p. 93
OL
IN
dbspace
SE LOG Clause
SE Log Clause
WITH LOG IN ' pathname '
MODE ANSI
26
SQL Quick Syntax Guide
SE
SE Log Clause
OL
OL Log Clause
CREATE INDEX
OL LOG Clause
OL Log Clause
WITH
LOG
BUFFERED
LOG MODE ANSI
CREATE INDEX
+
CREATE
INDEX
UNIQUE
CLUSTER
Index
Definition
p. 28
DISTINCT
OL
OL
FILLFACTOR percent
IN dbspace
FRAGMENT
BY
EXPRESSION
Clause
p. 28
Object Modes
for Unique
Indexes
p. 28
Object Modes
for Duplicate
Indexes
p. 29
SQL Statements 27
CREATE INDEX
Index Definition
Index
Definition
,
Index
Name
p. 104
ON
(
table name
)
column name
ASC
Synonym
Name
p. 108
DESC
FRAGMENT BY EXPRESSION Clause
FRAGMENT BY
EXPRESSION
Clause
,
FRAGMENT BY
EXPRESSION
frag-expression
IN dbspace
,
frag-expression
IN dbspace
,
Object Modes for Unique Indexes
Object Modes for
Unique Indexes
DISABLED
ENABLED
FILTERING
WITHOUT
ERROR
WITH
ERROR
28
SQL Quick Syntax Guide
REMAINDER IN
remainder
dbspace
CREATE PROCEDURE
Object Modes for Duplicate Indexes
Object Modes for
Duplicate Indexes
DISABLED
ENABLED
CREATE PROCEDURE
+
CREATE
PROCEDURE
Procedure
Name
p. 107
(
)
,
DBA
RETURNING
Clause
p. 30
Parameter
Statement
Block
p. 31
END
PROCEDURE
;
,
DOCUMENT
Quoted String
p. 107
WITH
LISTING IN
' pathname '
Parameter Option
Parameter
SQL Data Type
(Subset)
p. 94
variable
name
DEFAULT
LIKE
table
name
.
REFERENCES
column
name
default
value
BYTE
TEXT
DEFAULT
NULL
SQL Statements 29
CREATE PROCEDURE
RETURNING Clause
RETURNING
Clause
RETURNING
,
;
SQL Data Type
(Subset)
p. 94
REFERENCES
BYTE
TEXT
30
SQL Quick Syntax Guide
CREATE PROCEDURE
Statement Block
Statement Block
DEFINE
Statement
p. 114
CALL Statement
p. 113
ON
EXCEPTION
Statement
p. 119
CONTINUE
Statement
p. 113
EXECUTE PROCEDURE
Statement
p. 50
EXIT Statement
p. 115
FOR Statement
p. 116
FOREACH Statement
p. 117
IF Statement
p. 117
LET Statement
p. 119
RAISE EXCEPTION
Statement
p. 119
RETURN Statement
p. 120
SYSTEM Statement
p. 120
TRACE Statement
p. 120
WHILE Statement
p. 120
Subset of SQL
Statement
see SQLS
BEGIN
Statement
Block
;
END
SQL Statements 31
CREATE PROCEDURE FROM
CREATE PROCEDURE FROM
ESQL
+
' filename '
CREATE PROCEDURE FROM
variable
name
CREATE ROLE
+
CREATE ROLE
role name
OL
CREATE SCHEMA
DB
CREATE SCHEMA
AUTHORIZATION
CREATE TABLE
Statement
p. 33
user
name
+
CREATE INDEX
Statement
p. 27
CREATE VIEW
Statement
p. 44
+
CREATE SYNONYM
Statement
p. 33
GRANT
Statement
p. 55
CREATE TRIGGER
Statement
p. 40
OP
32
SQL Quick Syntax Guide
CREATE OPTICAL
CLUSTER
Statement
p. 123
CREATE SYNONYM
CREATE SYNONYM
+
CREATE
SYNONYM
PUBLIC
Synonym
Name
p. 108
Table Name
p. 108
FOR
View Name
p. 109
PRIVATE
CREATE TABLE
,
CREATE
TABLE
Table
Name
p. 108
(
Column
Definition
,
,
+
TEMP
TABLE
Clause
p. 37
Table-Level
Constraint
Definition
p. 36
)
OL
WITH
ROWIDS
Storage
Option
p. 39
Column-Definition Option
Column
Definition
column
name
Data Type
p. 94
DEFAULT
Clause
p. 34
Column-Level
Constraint
Definition
p. 35
SQL Statements 33
CREATE TABLE
DEFAULT Clause
DEFAULT
Clause
literal
DEFAULT
NULL
+
CURRENT
p. 97
+
DATETIME
Field Qualifier
p. 95
USER
p. 97
TODAY
p. 97
OL
+
+
SITENAME
p. 97
DBSERVERNAME
p. 97
34
SQL Quick Syntax Guide
CREATE TABLE
Column-Level Constraint-Definition Option
Column-Level
Constraint
Definition
UNIQUE
+
NOT
NULL
+
DISTINCT
+
ConstraintMode
Definitions
PRIMARY
KEY
ConstraintMode
Definitions
REFERENCES
Clause
p. 36
CHECK
Clause
p. 36
Constraint-Mode Definitions
Constraint-Mode
Definitions
DISABLED
CONSTRAINT
Constraint
Name
p. 93
ENABLED
FILTERING
WITHOUT
ERROR
WITH
ERROR
SQL Statements 35
CREATE TABLE
REFERENCES Clause
REFERENCES
Clause
table
name
REFERENCES
OL
+
,
(
column
name
)
ON DELETE CASCADE
CHECK Clause
CHECK
Clause
(
CHECK
Condition
p. 89
)
Table-Level Constraint-Definition Option
Table-Level
Constraint Definition
,
(
UNIQUE
+
column
name
)
Constraint-Mode
Definitions
p. 35
DISTINCT
PRIMARY
KEY
FOREIGN KEY
,
(
column
name
CHECK
Clause
36
SQL Quick Syntax Guide
+
)
REFERENCES
Clause
CREATE TABLE
TEMP TABLE Clause
TEMP TABLE
Clause
,
TEMP
TABLE
temp
table
name
(
Temporary
Table
Column
Definition
)
,
,
WITH NO LOG
Temp Table
Table-Level
Constraint
p. 38
Temporary Table Column Definition
Temporary Table Column
Definition
column
name
Data Type
p. 94
DEFAULT
Clause
p. 34
NOT NULL
Temp Table
Column
Constraint
Definition
p. 38
SQL Statements 37
CREATE TABLE
Temp Table Column Constraint Definition
Temp Table Column
Constraint Definition
UNIQUE
+
DISTINCT
PRIMARY
KEY
CHECK
Clause
p. 36
Temp Table Table-Level Constraint
Temp Table Table-Level
Constraint
UNIQUE
,
(
+
DISTINCT
PRIMARY
KEY
CHECK
Clause
p. 36
38
SQL Quick Syntax Guide
column
name
)
CREATE TABLE
Storage Option
Storage
Option
OL
Extent Option
IN dbspace
LOCK MODE
Clause
p. 40
FRAGMENT
BY
Clause
IN 'pathname'
SE
FRAGMENT BY Clause
FRAGMENT
BY
Clause
,
FRAGMENT BY
ROUND ROBIN IN
,
dbspace
dbspace
,
EXPRESSION
frag-expression
IN dbspace
,
frag-expression
IN dbspace
,
REMAINDER IN
remainder
dbspace
Extent Option
Extent Option
EXTENT SIZE
first
kbytes
NEXT SIZE
next
kbytes
SQL Statements 39
CREATE TRIGGER
LOCK MODE Clause
LOCK MODE Clause
LOCK MODE
PAGE
ROW
CREATE TRIGGER
DB
ESQL
+
CREATE TRIGGER
Trigger
Name
p. 41
Trigger Object
Modes
p. 43
INSERT
ON
Table
Name
p. 108
Action
Clause
p. 41
Insert
REFERENCING
Clause
p. 42
DELETE
ON
Table
Name
p. 108
Action
Clause
p. 41
Delete
REFERENCING
Clause
p. 42
UPDATE
Clause
p. 41
ON
Table
Name
p. 108
SQL Quick Syntax Guide
Action
Clause
Referencing
p. 43
Action
Clause
p. 41
Update
REFERENCING
Clause
p. 42
40
Action
Clause
Referencing
p. 43
Action
Clause
Referencing
p. 43
CREATE TRIGGER
Trigger Name
Trigger
Name
Identifier
p. 104
owner.
UPDATE Clause
UPDATE
Clause
UPDATE
,
OF
column name
Action Clause
Action
Clause
BEFORE
Triggered
Action List
p. 43
FOR EACH
ROW
FOR EACH
ROW
Triggered
Action List
p. 43
Triggered
Action List
p. 43
AFTER
AFTER
AFTER
Triggered
Action List
p. 43
Triggered
Action List
p. 43
Triggered
Action List
p. 43
SQL Statements 41
CREATE TRIGGER
Insert REFERENCING Clause
Insert
REFERENCING
Clause
REFERENCING
correlation name
NEW
AS
Delete REFERENCING Clause
Delete
REFERENCING
Clause
REFERENCING
correlation name
OLD
AS
Update REFERENCING Clause
Update
REFERENCING
Clause
REFERENCING
1
correlation
name
OLD
AS
1
correlation
name
NEW
AS
42
SQL Quick Syntax Guide
CREATE TRIGGER
Action Clause Referencing
Action
Clause
Referencing
BEFORE
Triggered
Action List
Triggered
Action List
FOR EACH
ROW
AFTER
Triggered
Action List
Triggered Action List
Triggered
Action List
,
,
(
WHEN
(
Condition
p. 89
)
INSERT
Statement
p. 59
)
DELETE
Statement
p. 45
UPDATE
Statement
p. 84
EXECUTE
PROCEDURE
p. 50
Trigger Object Modes
Trigger Object
Modes
DISABLED
ENABLED
SQL Statements 43
CREATE VIEW
CREATE VIEW
CREATE VIEW
View
Name
p. 109
AS
,
(
column
name
SELECT
Statement
(Subset)
p. 69
)
DATABASE
+
DATABASE
Database
Name
p. 93
EXCLUSIVE
DEALLOCATE DESCRIPTOR
+
ESQL
DEALLOCATE DESCRIPTOR
'descriptor '
descriptor
variable
44
SQL Quick Syntax Guide
WITH CHECK
OPTION
DECLARE
DECLARE
ESQL
cursor
id
DECLARE
INSERT
Statement
(Subset)
p. 59
+
FOR
CURSOR
+
+
cursor
variable
WITH
HOLD
+
+
SELECT
Statement
(Subset)
p. 69
FOR READ ONLY
FOR
UPDATE
,
column
name
OF
SCROLL
CURSOR
FOR
WITH
HOLD
SELECT
Statement
p. 69
statement id
+
statement id
variable
EXECUTE
PROCEDURE
Statement
p. 50
DELETE
DELETE FROM
Table
Name
p. 108
View
Name
p. 109
Synonym
Name
p. 108
WHERE
Condition
p. 89
ESQL
CURRENT
OF
cursor
name
SQL Statements 45
DESCRIBE
DESCRIBE
ESQL
+
DESCRIBE
USING
SQL DESCRIPTOR
statement id
statement
id variable
E/C
descriptor
variable
INTO
DISCONNECT
+
DISCONNECT
CURRENT
ESQL
DEFAULT
ALL
' connection name '
conn_nm variable
DROP AUDIT
SE
+
Table Name
p. 108
DROP AUDIT FOR
Synonym
Name
p. 108
DROP DATABASE
+
46
SQL Quick Syntax Guide
DROP DATABASE
' descriptor '
Database
Name
p. 93
sqlda pointer
DROP INDEX
DROP INDEX
+
Index
Name
p. 104
DROP INDEX
DROP PROCEDURE
+
Procedure
Name
p. 107
DROP PROCEDURE
DROP ROLE
+
DROP ROLE
OL
role name
DROP SYNONYM
+
Synonym
Name
p. 108
DROP SYNONYM
DROP TABLE
+
DROP TABLE
Table
Name
p. 108
Synonym
Name
p. 108
CASCADE
RESTRICT
SQL Statements 47
DROP TRIGGER
DROP TRIGGER
DB
ESQL
+
Trigger
Name
p. 41
DROP TRIGGER
DROP VIEW
+
DROP VIEW
View
Name
p. 109
CASCADE
Synonym
Name
p. 108
RESTRICT
EXECUTE
ESQL
EXECUTE
statement id
statement
id variable
48
SQL Quick Syntax Guide
INTO
Clause
p. 49
USING
Clause
p. 49
EXECUTE
USING Clause
USING
Clause
,
storage
variable
name
USING
E/C
:
+
INDICATOR
storage
indicator
variable
storage
indicator
variable
'storage descriptor '
SQL DESCRIPTOR
E/C
storage descriptor variable
storage sqlda pointer
DESCRIPTOR
INTO Clause
INTO
Clause
,
output
variable
name
INTO
E/C
: output
indicator
variable
+
INDICATOR
SQL DESCRIPTOR
E/C
output
indicator
variable
' output descriptor '
output descriptor variable
DESCRIPTOR
output sqlda pointer
SQL Statements 49
EXECUTE IMMEDIATE
EXECUTE IMMEDIATE
+
ESQL
Quoted
String
p. 107
EXECUTE IMMEDIATE
statement variable name
EXECUTE PROCEDURE
+
EXECUTE
PROCEDURE
Procedure
Name
p. 107
(
)
,
Argument
ESQL
SPL
INTO
Argument
Argument
Expression
p. 96
parameter name =
SELECT
Statement
(Singleton)
p. 69
50
SQL Quick Syntax Guide
,
host
variable
FETCH
FETCH
,
ESQL
FETCH
INTO
cursor id
+
data
variable
cursor
variable
+
NEXT
PREVIOUS
indicator
INDICATOR variable
data structure
+
PRIOR
FIRST
LAST
CURRENT
USING
row
position
RELATIVE
indicator
variable
SQL
DESCRIPTOR
descriptor
variable
E/C
+
DESCRIPTOR
-
'descriptor'
sqlda
pointer
row
position
ABSOLUTE
FLUSH
ESQL
+
FLUSH
cursor
id
cursor
variable
SQL Statements 51
FREE
FREE
ESQL
+
cursor id
FREE
cursor
variable
statement
id
statement
id variable
GET DESCRIPTOR
+
ESQL
GET DESCRIPTOR
'descriptor '
host variable = COUNT
,
descriptor
variable
VALUE
item
number
item
number
variable
52
SQL Quick Syntax Guide
Described
Item Info
p. 53
GET DIAGNOSTICS
Described Item Info
Described
Item Info
field host
variable
=
TYPE
LENGTH
PRECISION
SCALE
NULLABLE
INDICATOR
NAME
DATA
ITYPE
IDATA
ILENGTH
GET DIAGNOSTICS
+
ESQL
GET DIAGNOSTICS
Statement
Clause
EXCEPTION
Clause
p. 54
Statement Clause
,
Statement
Clause
st_var =
MORE
NUMBER
ROW_COUNT
SQL Statements 53
GET DIAGNOSTICS
EXCEPTION Clause
EXCEPTION
Clause
,
EXCEPTION
except_num
en_var
ex_var
=
CLASS_ORIGIN
CONNECTION_ALIAS
MESSAGE_LENGTH
MESSAGE_TEXT
RETURNED_SQLSTATE
SERVER_NAME
SUBCLASS_ORIGIN
54
SQL Quick Syntax Guide
GRANT
GRANT
GRANT
Database-Level
Privileges
+
TO
PUBLIC
,
user
' user '
OL
+
TO
role name
PUBLIC
,
user
WITH
GRANT
OPTION
' user '
role
name
' role
name'
+
EXECUTE ON
Procedure
Name
p. 107
Table Privileges
for PUBLIC
and Users
p. 56
OL
Table Privileges
for Roles
p. 56
+
Database-Level Privileges
Database-Level
Privileges
CONNECT
RESOURCE
DBA
SQL Statements 55
GRANT
Table Privileges for PUBLIC and Users
Table Privileges for
PUBLIC and Users
TableLevel
Privileges
p. 57
ON
Table
Name
p. 108
TO
View
Name
p. 109
PUBLIC
,
user
' user '
Synonym
Name
p. 108
+
+
AS grantor
WITH GRANT OPTION
Table Privileges for Roles
Table Privileges
for Roles
TableLevel
Privileges
p. 57
ON
Table
Name
p. 108
View
Name
p. 109
TO
role name
' role name'
Synonym
Name
p. 108
AS grantor
56
SQL Quick Syntax Guide
GRANT
Table-Level Privileges
Table-Level Privileges
ALL
PRIVILEGES
,
INSERT
DELETE
SELECT
,
+
(
UPDATE
,
+
(
,
(
INDEX
)
column
name
REFERENCES
+
)
column
name
column
name
)
ALTER
SQL Statements 57
GRANT FRAGMENT
GRANT FRAGMENT
,
OL
+
GRANT
FRAGMENT
Fragment-Level
Privileges
ON
tablename
(
dbspace
,
user
TO
WITH GRANT
OPTION
'user '
Fragment-Level Privileges
Fragment-Level
Privileges
ALL
,
INSERT
DELETE
UPDATE
58
SQL Quick Syntax Guide
AS
grantor
)
INFO
INFO
DB
INFO
TABLES
+
COLUMNS
Table
Name
p. 108
FOR
INDEXES
ACCESS
PRIVILEGES
REFERENCES
STATUS
OL
FRAGMENTS
INSERT
INSERT INTO
Table
Name
p. 108
View
Name
p. 109
VALUES Clause
p. 61
,
(
column
name
Synonym
Name
p. 108
EXECUTE PROCEDURE
)
SELECT
Statement
(Subset)
p. 69
+
Procedure
Name
p. 107
(
,
)
Argument
p. 60
SQL Statements 59
INSERT
Argument
Argument
Expression
p. 96
parameter
name
60
SQL Quick Syntax Guide
=
SELECT
Statement
(Singleton)
p. 69
INSERT
VALUES Clause
VALUES Clause
VALUES
,
ESQL
(
variable
name
)
: indicator variable
+
$ indicator variable
NULL
Literal Number
p. 107
Quoted String
p. 107
+
USER
p. 97
Literal DATETIME
p. 105
Literal INTERVAL
p. 106
TODAY p. 97
CURRENT p. 97
OL
SITENAME p. 97
DBSERVERNAME
p. 97
SQL Statements 61
LOAD
LOAD
DB
+
LOAD FROM
' filename '
DELIMITER
INSERT INTO
' delimiter '
Table
Name
p. 108
,
Synonym
Name
p. 108
column
name
)
)
View
Name
p. 109
LOCK TABLE
+
LOCK TABLE
Table
Name
p. 108
Synonym
Name
p. 108
62
SQL Quick Syntax Guide
IN
SHARE
EXCLUSIVE
MODE
OPEN
OPEN
ESQL
OPEN
cursor
id
E/C
+
cursor
variable
WITH REOPTIMIZATION
,
variable name
USING
'descriptor'
SQL DESCRIPTOR
descriptor
variable
E/C
DESCRIPTOR
sqlda
pointer
OUTPUT
DB
+
OUTPUT TO
filename
WITHOUT
HEADINGS
PIPE program
SELECT
Statement
p. 69
PREPARE
ESQL
+
PREPARE
statement
id
statement
id variable
FROM
Quoted
String
p. 107
statement
variable
name
SQL Statements 63
PUT
PUT
ESQL
+
PUT
cursor
id
,
cursor
variable
FROM
variable
name
+
: indicator
variable
$ indicator
variable
INDICATOR
USING
indicator
variable
' descriptor '
SQL DESCRIPTOR
descriptor
variable
E/C
DESCRIPTOR
sqlda
pointer
RECOVER TABLE
SE
+
RECOVER TABLE
Table
Name
p. 108
RENAME COLUMN
+
64
RENAME COLUMN
SQL Quick Syntax Guide
Table
Name
p. 108
.old column name
TO
new column name
RENAME DATABASE
RENAME DATABASE
OL
RENAME DATABASE
+
old database name
TO
new database name
RENAME TABLE
+
RENAME TABLE
Table
Name
p. 108
TO
new table
name
REPAIR TABLE
SE
DB
+
REPAIR TABLE
Table
Name
p. 108
SQL Statements 65
REVOKE
REVOKE
+
REVOKE
Table-Level
Privileges
p. 67
ON
table name
FROM
PUBLIC
,
view name
user
synonym
name
EXECUTE ON
OL
'user '
Procedure
Name
p. 107
OL
role
name
role name
' role '
name
Table-Level
Privileges
p. 67
ON
table name
FROM
PUBLIC
,
view name
user
synonym
name
'user '
OL
CASCADE
RESTRICT
DatabaseLevel
Privileges
p. 67
FROM
PUBLIC
,
user
'user '
66
SQL Quick Syntax Guide
REVOKE
Table-Level Privileges
Table-Level
Privileges
ALL
PRIVILEGES
,
INSERT
DELETE
SELECT
UPDATE
INDEX
ALTER
REFERENCES
Database-Level Privileges
Database-Level
Privileges
CONNECT
RESOURCE
DBA
SQL Statements 67
REVOKE FRAGMENT
REVOKE FRAGMENT
OL
+
REVOKE
FRAGMENT
Fragment-Level
Privileges
ON
tablename
,
FROM
,
(
'user '
)
dbspace
Fragment-Level Privileges
Fragment-Level
Privileges
ALL
,
INSERT
DELETE
UPDATE
ROLLBACK WORK
ROLLBACK
WORK
68
SQL Quick Syntax Guide
user
ROLLFORWARD DATABASE
ROLLFORWARD DATABASE
SE
+
ROLLFORWARD DATABASE
Database
Name
p. 93
SELECT
UNION
UNION ALL
SELECT
Select
Clause
p. 70
FROM
Clause
p. 71
ESQL
SPL
INTO
Clause
p. 71
WHERE
Clause
p. 72
ORDER BY
Clause p. 74
HAVING
Clause
p. 74
GROUP BY
Clause p. 73
+
+
INTO TEMP
Clause p. 74
FOR READ ONLY
FOR UPDATE
,
OF
column
name
SQL Statements 69
SELECT
SELECT Clause
,
Select
Clause
Select
List
ALL
DISTINCT
+
UNIQUE
Select List
Select
List
Expression
p. 96
display
label
AS
*
70
SQL Quick Syntax Guide
Table
Name
p. 108
.
View
Name
p. 109
.
Synonym
Name
p. 108
.
SELECT
INTO Clause
INTO
Clause
,
INTO
data variable
ESQL
+
: indicator
variable
$ indicator
variable
INDICATOR
indicator variable
data structure
FROM Clause
FROM
Clause
FROM
Table
Name
p. 108
View
Name
p. 109
Synonym
Name
p. 108
alias
+
,
Additional
Tables
p. 72
AS
SQL Statements 71
SELECT
Additional Tables
Additional
Tables
,
Table
Name
p. 108
alias
View
Name
p. 109
+
+
AS
Synonym
Name
p. 108
OUTER
Table
Name
p. 108
alias
View
Name
p. 109
AS
Synonym
Name
p. 108
OUTER
(
Table
Name
p. 108
View
Name
p. 109
)
alias
AS
Synonym
Name
p. 108
WHERE Clause
WHERE
Clause
AND
WHERE
Condition
p. 89
Join
p. 73
72
SQL Quick Syntax Guide
,
Additional
Tables
SELECT
Join Option
Join
column
name
Relational
Operator
p. 108
column
name
Table
Name
p. 108
.
Table
Name
p. 108
.
alias
.
alias
.
View
Name
p. 109
.
View
Name
p. 109
.
Synonym
Name
p. 108
Synonym
Name
p. 108
.
.
GROUP BY Clause
,
GROUP BY
Clause
column
name
GROUP BY
Table
Name
p. 108
View
Name
p. 109
.
Synonym
Name
p. 108
.
alias
.
+
select
number
SQL Statements 73
SELECT
HAVING Clause
HAVING
Clause
Condition
p. 89
HAVING
ORDER BY Clause
ORDER BY
Clause
,
column
name
ORDER BY
Table
Name
p. 108
.
View
Name
p. 109
.
Synonym
Name
p. 108
.
alias
.
+
ASC
[ first, last ]
select
number
+
display
label
ROWID
INTO TEMP Clause
INTO TEMP
Clause
INTO TEMP
temp table name
WITH NO LOG
74
SQL Quick Syntax Guide
DESC
SET
SET
+
Table-Mode
Format
p. 75
SET
OL
List-Mode
Format
p. 77
TransactionMode Format
p. 77
Table-Mode Format
Table-Mode Format
,
CONSTRAINTS
INDEXES
TRIGGERS
FOR
table name
Object Modes for
Constraints and
Unique Indexes
p. 76
Object Modes for
Triggers and
Duplicate Indexes
p. 76
SQL Statements 75
SET
Object Modes for Constraints and Unique Indexes
Object Modes for Constraints
and Unique Indexes
DISABLED
ENABLED
FILTERING
WITHOUT
ERROR
WITH
ERROR
Object Modes for Triggers and Duplicate Indexes
Object Modes for Triggers
and Duplicate Indexes
DISABLED
ENABLED
76
SQL Quick Syntax Guide
SET
List-Mode Format
List-Mode Format
,
CONSTRAINTS
constraint
name
Object Modes for
Constraints and
Unique Indexes
p. 76
,
INDEXES
index name
Object Modes for
Constraints and
Unique Indexes
p. 76
Object Modes for
Triggers and
Duplicate Indexes
p. 76
,
TRIGGERS
trigger name
Object Modes for
Triggers and
Duplicate Indexes
p. 76
Transaction-Mode Format
Transaction-Mode Format
CONSTRAINTS
,
constraint
name
IMMEDIATE
ALL
DEFERRED
SQL Statements 77
SET CONNECTION
SET CONNECTION
ESQL
'connection name'
SET CONNECTION
E/C
+
+
DORMANT
conn_nm variable
Database
Environment
p. 25
+
DEFAULT
E/C
+
CURRENT
SET DATASKIP
OL
+
SET DATASKIP
ON
,
dbspace
OFF
DEFAULT
SET DEBUG FILE TO
+
SET DEBUG FILE TO
'
filename
'
variable name
character
expression
78
SQL Quick Syntax Guide
WITH APPEND
SET DESCRIPTOR
SET DESCRIPTOR
+
ESQL
SET DESCRIPTOR
' descriptor '
COUNT =
value
count
variable
descriptor
variable
VALUE
item
number
,
Item
Descriptor
Information
item
number
variable
Item Descriptor Information
Item
Descriptor
Information
=
TYPE
literal integer
integer-host
variable
LENGTH
PRECISION
SCALE
NULLABLE
INDICATOR
ITYPE
ILENGTH
DATA
IDATA
NAME
=
Literal Number
p. 107
Literal DATETIME
p. 105
Literal INTERVAL
p. 106
Quoted String
p. 107
data variable
SQL Statements 79
SET EXPLAIN
SET EXPLAIN
+
ON
SET EXPLAIN
OFF
SET ISOLATION
OL
+
SET ISOLATION TO
DIRTY READ
COMMITTED READ
CURSOR STABILITY
REPEATABLE READ
SET LOCK MODE TO
+
SET LOCK MODE TO
WAIT
OL
seconds
NOT WAIT
SET LOG
OL
+
SET
LOG
BUFFERED
80
SQL Quick Syntax Guide
SET OPTIMIZATION
SET OPTIMIZATION
+
HIGH
SET OPTIMIZATION
LOW
SET PDQPRIORITY
OL
+
SET PDQPRIORITY
DEFAULT
LOW
OFF
HIGH
percent-of-resources
SET ROLE
+
SET ROLE
role name
OL
NULL
NONE
SET SESSION AUTHORIZATION
ESQL
OL
SET SESSION AUTHORIZATION TO
'user '
SQL Statements 81
SET TRANSACTION
SET TRANSACTION
,
SET TRANSACTION
READ WRITE
1
READ ONLY
OL
1
ISOLATION LEVEL
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
,
READ ONLY
1
OL
1
ISOLATION LEVEL
READ UNCOMMITTED
START DATABASE
SE
+
START DATABASE
Database
Name
p. 93
WITH LOG IN
' pathname '
MODE ANSI
WITH NO LOG
82
SQL Quick Syntax Guide
START VIOLATIONS TABLE
START VIOLATIONS TABLE
+
START VIOLATIONS TABLE FOR
tablename
,
MAX ROWS
violations
USING
diagnostics
numrows
STOP VIOLATIONS TABLE
STOP VIOLATIONS TABLE FOR
+
tablename
UNLOAD
DB
+
UNLOAD TO
SELECT
Statement
p. 69
'filename'
DELIMITER
'delimiter'
UNLOCK TABLE
+
UNLOCK TABLE
Table
Name
p. 108
Synonym
Name
p. 108
SQL Statements 83
UPDATE
UPDATE
Table
Name
p. 108
UPDATE
SET Clause
SET
Condition
p. 89
WHERE
View
Name
p. 109
ESQL
SPL
Synonym
Name
p. 108
CURRENT OF
cursor
id
SET Clause
SET
Clause
,
column
name
=
Expression
(Subset)
p. 96
(
+
SELECT
Statement
(Subset)
p. 69
)
NULL
,
(
column
name
,
)
=
(
*
(
SELECT
Statement
(Subset)
p. 69
NULL
84
SQL Quick Syntax Guide
)
Expression
(Subset)
p. 96
)
UPDATE STATISTICS
UPDATE STATISTICS
UPDATE STATISTICS
+
FOR PROCEDURE
Procedure
Name
p. 107
LOW
Table
Name
p. 108
FOR
TABLE
DROP
DISTRIBUTIONS
,
Synonym
Name
p. 108
column
name
(
)
MEDIUM
FOR
TABLE
Table
Name
p. 108
RESOLUTION
percent
,
Synonym
Name
p. 108
(
column
name
)
OL
conf
DISTRIBUTIONS ONLY
HIGH
FOR
TABLE
RESOLUTION
Table
Name
p. 108
Synonym
Name
p. 108
,
(
column
name
percent
OL
)
DISTRIBUTIONS ONLY
SQL Statements 85
WHENEVER
WHENEVER
ESQL
WHENEVER
SQLERROR
CONTINUE
+
GO TO
NOT FOUND
: label
GOTO
+
STOP
SQLWARNING
E/CO
CALL
ERROR
label
E/CO
function
name
+
PERFORM
86
SQL Quick Syntax Guide
paragraph
name
Section
SQL Segments
Condition
Condition
AND
OR
Comparison
Condition
p. 90
NOT
Condition with
Subquery
p. 91
SQL Segments
89
Condition
Comparison Conditions (Boolean Expressions)
Comparison
Condition
Expression
p. 96
Relational
Operator
p. 108
Expression
p. 96
Expression
p. 96
Expression
p. 96
BETWEEN
NOT
Expression
p. 96
,
Expression
p. 96
+
AND
Literal
Number
p. 107
(
IN
NOT
)
Literal
DATETIME
p. 105
Literal
INTERVAL
p. 106
Quoted
String
p. 107
90
TODAY
Table
Name
p. 108
.
View
Name
p. 109
.
Synonym
Name
p. 108
.
alias
.
Table
Name
p. 108
.
View
Name
p. 109
.
Synonym
Name
p. 108
.
alias
.
SQL Quick Syntax Guide
USER
CURRENT
DATETIME
Field
Qualifier
p. 95
OL
SITENAME
DBSERVERNAME
column
name
IS
NULL
NOT
LIKE
NOT
+
MATCHES
column
name
Quoted
String
p. 107
ESCAPE 'char'
column name
Condition
Condition with a Subquery
Condition
with
Subquery
IN
Subquery
p. 92
EXISTS
Subquery
p. 92
ALL/ANY/SOME
Subquery
p. 92
SQL Segments
91
Condition
IN Subquery
IN
Subquery
Expression
p. 96
IN
(
NOT
Condition
with
Subquery
p. 91
)
EXISTS Subquery
EXISTS
Subquery
EXISTS
(
NOT
Condition
with
Subquery
p. 91
)
ALL/ANY/SOME Subquery
ALL/ANY/SOME
Subquery
Expression
p. 96
Relational
Operator
p. 108
(
ALL
ANY
SOME
92
SQL Quick Syntax Guide
Condition
with
Subquery
p. 91
)
Constraint Name
Constraint Name
Identifier
p. 104
owner.
OL
:
database
'owner'.
@dbservername
Database Name
dbname
OL
OL
@ dbservername
' //dbservername/dbname '
ESQL
variable name
SE
' //dbservername/pathname/dbname '
' /pathname/dbname@dbservername '
SQL Segments
93
Data Type
Data Type
CHAR
(
CHARACTER
)
size
(1)
GLS
NCHAR
+
+
DATE
+
DATETIME
DATETIME Field Qualifier p. 95
DECIMAL
(
DEC
)
precision
NUMERIC
, scale
16
FLOAT
(
float
precision
)
DOUBLE PRECISION
INTEGER
INT
+
INTERVAL
+
MONEY
INTERVAL Field Qualifier p. 105
(
)
precision
16
,2
, scale
SERIAL
+
(
+
(1)
start
)
SMALLFLOAT
REAL
SMALLINT
OL
94
SQL Quick Syntax Guide
INFORMIX-OnLine Dynamic Server-Specific Data Types p. 95
DATETIME Field Qualifier
INFORMIX-OnLine Dynamic Server-Specific Data Types
INFORMIX-OnLine Dynamic
Server-Specific
Data Types
+
TEXT
+
BYTE
IN
TABLE
blobspace
OP
family name
+
VARCHAR
(
)
max
,
GLS
+
,0
NVARCHAR
CHARACTER VARYING
reserve
(
)
max
,
reserve
DATETIME Field Qualifier
YEAR
MONTH
TO YEAR
DAY
TO MONTH
HOUR
MINUTE
TO DAY
TO HOUR
SECOND
TO MINUTE
FRACTION
TO SECOND
TO FRACTION
(3)
(digit)
SQL Segments
95
Expression
Expression
+
-
*
/
||
Column
Expressions
p. 97
+
Constant
Expressions
p. 97
Function
Expressions
p. 98
Aggregate
Expressions
p. 103
Procedure Call
Expressions
p. 103
variable name
SPL
procedure variable name
(
96
SQL Quick Syntax Guide
Expression
)
Expression
Column Expressions
column
name
Table
Name
p. 108
.
alias
.
View
Name
p. 109
.
Synonym
Name
p. 108
.
+
+
[first, last ]
ROWID
Constant Expressions
Quoted
String
p. 107
USER
OL
+
SITENAME
DBSERVERNAME
+
Literal
Number
p. 107
TODAY
CURRENT
Literal
DATETIME
p. 105
DATETIME
Field
Qualifier
p. 95
Literal
INTERVAL
p. 106
n
UNITS
datetime
unit
SQL Segments
97
Expression
Function Expressions
+
Algebraic
Functions
p. 99
DBINFO
Function
p. 100
Exponential and
Logarithmic
Functions
p. 100
HEX
Function
p. 100
LENGTH
Function
p. 101
Time
Functions
p. 101
Trigonometric
Functions
p. 102
TRIM
Function
p. 102
98
SQL Quick Syntax Guide
Expression
Algebraic Functions
Algebraic
Functions
)
ABS
(
MOD
(
dividend, divisor
)
POW
(
base, exponent
)
ROOT
(
radicand
num_expression
)
, index
,2
ROUND
(
)
Expression
p. 96
, rounding factor
,0
SQRT
(
TRUNC
(
sqrt_radicand
)
)
Expression
p. 96
, truncating factor
,0
SQL Segments
99
Expression
DBINFO Function
DBINFO Function
DBINFO
(
'DBSPACE'
OL
)
tblspace num
,
expression
'sqlca.sqlerrd1'
'sqlca.sqlerrd2'
OL
+
'sessionid'
Exponential and Logarithmic Functions
Exponential and Logarithmic Functions
float expression
LOGN
(
(
float expression
)
)
LOG10
(
float expression
)
EXP
HEX Function
HEX Function
HEX
100
SQL Quick Syntax Guide
(
integer
expression
)
Expression
LENGTH Function
LENGTH
Functions
+
Quoted
String
p. 107
(
LENGTH
CHAR_LENGTH
SPL
ESQL
CHARACTER_LENGTH
)
variable
name
column
name
OCTET_LENGTH
Table
Name
p. 108
.
Time Functions
Time Functions
+
DATE
DAY
(
non-date
expression
)
(
date/
datetime
expression
)
MONTH
WEEKDAY
YEAR
(
EXTEND
MDY
(
date/
datetime
expression
month
integer
expression
,
)
,
day
integer
expression
first TO last
,
year
integer
expression
)
SQL Segments
101
Expression
Trigonometric Functions
Trigonometric
Functions
COS
(
radian
expression
)
(
numeric
expression
)
SIN
TAN
ASIN
ACOS
ATAN
ATAN2
(
y, x
)
TRIM Function
TRIM
Function
TRIM
source
character
value
expression
(
LEADING
TRAILING
FROM
trim
character
value
expression
BOTH
trim
character
value
expression
102
SQL Quick Syntax Guide
FROM
)
Expression
Aggregate Expressions
COUNT (*)
(
AVG
MAX
MIN
SUM
COUNT
DISTINCT
UNIQUE
(
DISTINCT
+
UNIQUE
(
COUNT
Table
Name
p. 108
.
Synonym
Name
p. 108
.
View
Name
p. 109
.
column
name
)
ALL
(
AVG
MAX
MIN
SUM
ALL
)
Expression
(Subset)
p. 96
RANGE
STDEV
VARIANCE
Procedure Call Expressions
,
Procedure
Name
p. 107
(
Expression
p. 96
called
variable
)
=
SQL Segments
103
Identifier
Identifier
letter
underscore
letter
digit
underscore
Delimited
Identifier
Delimited Identifiers
Delimited
Identifier
double quote
letter
double quote
digit
underscore
nonalphanumeric character
Index Name
Identifier
owner.
OL
:
database
@ dbservername
104
SQL Quick Syntax Guide
'owner'.
INTERVAL Field Qualifier
INTERVAL Field Qualifier
YEAR
TO YEAR
(y-precision)
(4)
MONTH
TO MONTH
(precision)
(2)
DAY
TO DAY
(precision)
(2)
HOUR
TO HOUR
(precision)
(2)
MINUTE
TO MINUTE
(precision)
(2)
SECOND
(precision)
TO SECOND
(2)
TO FRACTION
FRACTION
(f-precision)
(3)
Literal DATETIME
DATETIME
(
Numeric
Date
p. 106
)
DATETIME
Field Qualifier
p. 95
SQL Segments
105
Literal Interval
Numeric Date
Numeric Date
yyyy
mo
dd
space
hh
:
mi
:
ss
.
f
Literal Interval
INTERVAL
106
SQL Quick Syntax Guide
(
Numeric Date
)
INTERVAL
Field Qualifier
p. 105
Literal Number
Literal Number
digit
+
-
.
digit
.
digit
E
digit
+
-
Procedure Name
Identifier
p. 104
owner.
OL
+
'owner'
:
database
@ dbservername
Quoted String
'
'
character
+
"
''
"
character
""
SQL Segments
107
Relational Operator
Relational Operator
<
<=
>
=
>=
<>
+
!=
Synonym Name
owner.
OL
+
'owner'.
:
database
Identifier
p. 104
@ dbservername
Table Name
Identifier
p. 104
owner.
OL
+
'owner'.
:
database
@ dbservername
108
SQL Quick Syntax Guide
View Name
View Name
Identifier
p. 104
owner.
OL
+
:
database
'owner'.
@ dbservername
SQL Segments
109
Section III
Stored Procedure Language
Statements
CALL
CALL
CALL
Procedure
Name
p. 107
(
)
;
,
,
Argument
RETURNING
procedure
variable
Argument
Argument
Expression
p. 96
parameter
name
=
SELECT
Statement
(Subset)
p. 69
CONTINUE
CONTINUE
FOR
;
WHILE
FOREACH
SPL Statements
113
DEFINE
DEFINE
,
DEFINE
SQL Data Type
(Subset)
p. 94
variable
name
GLOBAL
OL
REFERENCES
DEFAULT
Default
Value
p. 115
DEFAULT
NULL
BYTE
TEXT
,
SQL Data Type
(Subset)
p. 94
variable
name
OL
REFERENCES
BYTE
TEXT
LIKE
Table Name
p. 108
Synonym
Name
p. 108
View Name
p. 109
PROCEDURE
114
SQL Quick Syntax Guide
.
column name
;
EXIT
Default Value Clause
Default
Value
Literal Number
p. 107
Quoted String
p. 107
Literal Interval
p. 106
Literal Datetime
p. 105
CURRENT
p. 90
DATETIME
Field
Qualifier
p. 95
USER
TODAY
NULL
OL
DBSERVERNAME
SITENAME
EXIT
EXIT
FOR
;
WHILE
FOREACH
SPL Statements
115
FOR
FOR
,
FOR
variable
name
IN
(
left
right
TO
expression
expression
)
Statement
Block
p. 31
END
FOR
;
STEP
increment
expression
,
expression
=
left
expression
TO
STEP
116
SQL Quick Syntax Guide
right
expression
increment
expression
FOREACH
FOREACH
SELECT...INTO
Statement
p. 71
FOREACH
cursor
name
Statement
Block
p. 31
END
FOREACH
;
WITH HOLD
FOR
WITH HOLD
EXECUTE
PROCEDURE
Procedure
Name
p. 107
(
)
,
variable
name
INTO
,
parameter
name
=
Expression
(Subset)
p. 96
IF
IF
Condition
p. 89
THEN
END IF
;
IF Statement
List
p. 118
ELIF
Condition
p. 89
THEN
IF Statement
List
p. 118
ELSE
IF Statement
List
p. 118
SPL Statements
117
IF
IF Statement List
IF Statement
List
BEGIN
Statement
Block
p. 31
END
CALL
Statement
p. 113
CONTINUE
Statement
p. 113
EXIT
Statement
p. 115
FOR
Statement
p. 116
FOREACH
Statement
p. 117
IF
Statement
p. 117
LET
Statement
p. 119
RAISE EXCEPTION
Statement
p. 119
RETURN
Statement
p. 120
SYSTEM
Statement
p. 120
TRACE
Statement
p. 120
WHILE
Statement
p. 120
SQL Statement
118
SQL Quick Syntax Guide
LET
LET
,
,
,
variable
name
LET
Procedure
Name
p. 107
=
Expression
p. 96
(
called
variable
;
)
=
,
Expression
p. 96
ON EXCEPTION
Statement
Block
p. 31
ON EXCEPTION
,
IN
SET
SQL
error
variable
(
END EXCEPTION
;
error
number
)
WITH RESUME
,
ISAM
error
variable
,
error
data
variable
RAISE EXCEPTION
RAISE EXCEPTION
SQL
error
;
,
ISAM
error
,
error text
variable
SPL Statements
119
RETURN
RETURN
;
RETURN
,
Expression
p. 96
WITH RESUME
SYSTEM
;
expression
SYSTEM
character variable
TRACE
;
ON
TRACE
OFF
PROCEDURE
Expression
p. 96
WHILE
WHILE
Condition
p. 89
Statement
Block
p. 31
END WHILE
;
120
SQL Quick Syntax Guide
Section IV
INFORMIX-OnLine Optical
Statements
ALTER OPTICAL CLUSTER
ALTER OPTICAL CLUSTER
+
DB
ALTER
OPTICAL
CLUSTER
cluster
name
ESQL
CLUSTERSIZE
Clause
owner.
CLUSTERSIZE Clause
CLUSTERSIZE
Clause
CLUSTERSIZE
500
clustersize
CREATE OPTICAL CLUSTER
+
DB
ESQL
CREATE
OPTICAL
CLUSTER
cluster
name
FOR
Clause
ON
Clause
p. 124
owner.
CLUSTERSIZE
Clause
FOR Clause
,
FOR
Clause
FOR
Table Name
p. 108
(
blob column name
)
INFORMIX-OnLine/Optical Statements
123
ON Clause
ON Clause
,
ON
Clause
cluster-key
column name
(
ON
)
DROP OPTICAL CLUSTER
+
DROP
OPTICAL
CLUSTER
DB
ESQL
cluster
name
owner.
RELEASE
+
DB
ESQL
'family name'
RELEASE
ESQL
family-name
variable
SPL
procedure
variable
name
124
SQL Quick Syntax Guide
volume number
RESERVE
RESERVE
+
DB
ESQL
'family name'
RESERVE
volume number
ESQL
family-name
variable
SPL
procedure
variable
name
SET MOUNTING TIMEOUT
+
DB
ESQL
SET MOUNTING TIMEOUT TO
WAIT
NOT WAIT
seconds
Function Expressions
Function
Expressions
p. 98
+
DB
ESQL
DESCR (blob column name)
FAMILY (blob column name)
VOLUME (blob column name)
INFORMIX-OnLine/Optical Statements
125