Introduction

Overview of SQL Syntax

SQL Statements

In This Chapter

ALLOCATE COLLECTION

ALLOCATE DESCRIPTOR

ALLOCATE ROW

ALTER ACCESS_METHOD

ALTER FRAGMENT

ALTER FUNCTION

ALTER INDEX

ALTER PROCEDURE

ALTER ROUTINE

ALTER SEQUENCE

ALTER TABLE

BEGIN WORK

CLOSE

CLOSE DATABASE

COMMIT WORK

CONNECT

CREATE ACCESS_METHOD

CREATE AGGREGATE

CREATE CAST

CREATE DATABASE

CREATE DISTINCT TYPE

CREATE DUPLICATE

CREATE EXTERNAL TABLE (XPS)

CREATE FUNCTION

CREATE FUNCTION FROM

CREATE INDEX

CREATE OPAQUE TYPE

CREATE OPCLASS

CREATE PROCEDURE

CREATE PROCEDURE FROM

CREATE ROLE

CREATE ROUTINE FROM

CREATE ROW TYPE

CREATE SCHEMA

CREATE SCRATCH TABLE

CREATE SEQUENCE

CREATE SYNONYM

CREATE TABLE

CREATE TEMP TABLE

CREATE Temporary TABLE

CREATE TRIGGER

CREATE VIEW

CREATE XADATASOURCE

CREATE XADATASOURCE TYPE

DATABASE

DEALLOCATE COLLECTION

DEALLOCATE DESCRIPTOR

DEALLOCATE ROW

DECLARE

DELETE

DESCRIBE

DESCRIBE INPUT

DISCONNECT

DROP ACCESS_METHOD

DROP AGGREGATE

DROP CAST

DROP DATABASE

DROP DUPLICATE

DROP FUNCTION

DROP INDEX

DROP OPCLASS

DROP PROCEDURE

DROP ROLE

DROP ROUTINE

DROP ROW TYPE

DROP SEQUENCE

DROP SYNONYM

DROP TABLE

DROP TRIGGER

DROP TYPE

DROP VIEW

DROP XADATASOURCE

DROP XADATASOURCE TYPE

EXECUTE

EXECUTE FUNCTION

EXECUTE IMMEDIATE

EXECUTE PROCEDURE

FETCH

FLUSH

FREE

GET DESCRIPTOR

GET DIAGNOSTICS

GRANT

GRANT FRAGMENT

INFO

INSERT

LOAD

LOCK TABLE

MERGE

MOVE TABLE

OPEN

OUTPUT

PREPARE

PUT

RENAME COLUMN

RENAME DATABASE

RENAME INDEX

RENAME SEQUENCE

RENAME TABLE

REVOKE

REVOKE FRAGMENT

ROLLBACK WORK

SAVE EXTERNAL DIRECTIVES

SELECT

The Order of Qualifying Rows

Using the SKIP Option (IDS)

Using the FIRST Option

The LIMIT Keyword (IDS)

Using SKIP, FIRST, LIMIT, or MIDDLE as a Column Name

Using the SKIP Option with the FIRST Option (IDS)

Using the MIDDLE Option (XPS)

Allowing Duplicates

Data Types in Distributed Queries (IDS)

Expressions in the Select List

Declaring a Display Label

INTO Clause with Indicator Variables

INTO Clause with Cursors

Preparing a SELECT ... INTO Query

Using Array Variables with the INTO Clause

Error Checking

Aliases for Tables or Views

Table Expressions (XPS)

Restrictions on External Tables in Joins and Subqueries

Application Partitioning: The LOCAL Keyword (XPS)

Sampled Queries: The SAMPLES OF Keywords (XPS)

The ONLY Keyword (IDS)

Selecting from a Collection Variable (IDS)

Selecting from a Row Variable (IDS, ESQL/C)

Iterator Functions (IDS)

Queries that Join Tables

ANSI-Compliant Joins (IDS)

Using the ON Clause

Informix-Extension Outer Joins

Using a Condition in the WHERE Clause

Specifying a Join in the WHERE Clause

Relationship of GROUP BY and Projection Clauses

NULL Values in the GROUP BY Clause

Using Select Numbers

Ordering by a Column or by an Expression

Ordering by a Substring

Ascending and Descending Orders

Nested Ordering

Using Select Numbers

Ordering by Rowids

ORDER BY Clause with DECLARE

Placing Indexes on ORDER BY Columns

Syntax That is Incompatible with the FOR UPDATE Clause

Using the FOR READ ONLY Clause in Read-Only Mode

Syntax That Is Incompatible with the FOR READ ONLY Clause

Results When No Rows are Returned

Restrictions with INTO Table Clauses in ESQL/C

INTO TEMP Clause

Using SELECT INTO to Create a New Permanent Table

Using the WITH NO LOG Option

INTO EXTERNAL Clause (XPS)

INTO SCRATCH Clause (XPS)

Restrictions on a Combined SELECT

Duplicate Rows in a Combined SELECT

UNION in Subqueries

SET ALL_MUTABLES

SET AUTOFREE

SET COLLATION

SET CONNECTION

SET CONSTRAINTS

SET Database Object Mode

SET DATASKIP

SET DEBUG FILE

SET Default Table Space

SET Default Table Type

SET DEFERRED_PREPARE

SET DESCRIPTOR

SET ENCRYPTION PASSWORD

SET ENVIRONMENT

SET EXPLAIN

SET INDEX

SET INDEXES

SET ISOLATION

SET LOCK MODE

SET LOG

SET OPTIMIZATION

SET PDQPRIORITY

SET PLOAD FILE

SET Residency

SET ROLE

SET SCHEDULE LEVEL

SET SESSION AUTHORIZATION

SET STATEMENT CACHE

SET TABLE

SET TRANSACTION

SET Transaction Mode

SET TRIGGERS

START VIOLATIONS TABLE

STOP VIOLATIONS TABLE

TRUNCATE (IDS)

TRUNCATE (XPS)

UNLOAD

UNLOCK TABLE

UPDATE

UPDATE STATISTICS

WHENEVER

SPL Statements

Data Types and Expressions

Other Syntax Segments

Appendix A. Reserved Words for IBM Informix Dynamic Server

Appendix B. Reserved Words for IBM Informix Extended Parallel Server

Appendix C. Accessibility

Notices

Index