Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

Identifier

An identifier specifies the simple name of a database object, such as a column, table, index, or view. Use the Identifier segment whenever you see a reference to an identifier in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
digit
An integer that forms part of the identifier
You must specify a number between `0` and `9`, inclusive.
Literal Number, p. 1-1001

letter
A letter that forms part of the identifier
If you are using the default locale, a letter must be an uppercase or lowercase character in the range `a` to `z` (in the `ASCII` code set). If you are using a nondefault locale, letter must be an alphabetic character that the locale supports. See "Support for Non-ASCII Characters in Identifiers" for further information.
Letters are literal values that you enter from the keyboard.

underscore
An underscore character that forms part of the identifier
You cannot substitute a space character, dash, hyphen, or any other nonalphanumeric character for the underscore character.
The underscore character (_) is a literal value that you enter from the keyboard.

(1 of 2)
`ADA`	execute	order
all	exists	pascal
and	fetch	pli
any	float	precision
as	for	primary
asc	fortran	procedure
authorization	found	privileges
avg	from	public
begin	go	real
between	goto	rollback
by	group	schema
char	having	section
character	in	select
check	indicator	set
close	insert	smallint
cobol	int	some
commit	integer	sql
continue	into	sqlcode
count	is	sqlerror
create	language	sum
current	like	table
cursor	max	to
dec	min	union
decimal	module	unique
declare	not	update
delete	null	user
desc	numeric	values
distinct	of	view
double	on	whenever
end	open	where
escape	option	with
exec	or	work

You can flag identifiers as ANSI-reserved words by taking the following steps:

Set the DBANSIWARN environment variable or use the -ansi flag at compile time to receive compile-time warnings.
Set the DBANSIWARN environment variable at runtime to receive warning flags set in the SQLWARN array of sqlca.

Support for Non-ASCII Characters in Identifiers

GLS

If you are using a nondefault locale, you can use any alphabetic character that your locale recognizes as a letter in an SQL identifier name. You can use a non-ASCII character as a letter as long as your locale supports it. This feature enables you to use non-ASCII characters in the names of database objects such as indexes, tables, and views. For a list of SQL identifiers that support non-ASCII characters, see the Guide to GLS Functionality.

Delimited Identifiers

Element Purpose Restrictions Syntax
digit
An integer that forms part of the delimited identifier
You must specify a number between 0 and 9, inclusive.
Literal Number, p. 1-1001

double quote
The double-quote character that marks a string as a delimited identifier
If the DELIMIDENT environment variable is not set, values within double quotes are treated as quoted strings by the database server.
The double quote character (") is a literal value that you enter from the keyboard.

letter
A letter that forms part of the delimited identifier
Letters in delimited identifiers are case-sensitive. If you are using the default locale, a letter must be an uppercase or lowercase character in the range a-z (in the ASCII code set). If you are using a nondefault locale, letter must be an alphabetic character that the locale supports. See "Support for Non-ASCII Characters in Delimited Identifiers" for further information.
Letters are literal values that you enter from the keyboard.

nonalpha-numeric character
A nonalphanumeric character, such as # or $ or space, that forms part of the delimited identifier
If you are using the ASCII code set, you can specify any ASCII nonalphanumeric character.
Nonalphanumeric characters are literal values that you enter from the keyboard.

underscore
An underscore (_) that forms part of the delimited identifier
You can use a dash, hyphen, or any other appropriate character in place of the underscore character.
The underscore (_) is a literal value that you enter from the keyboard.

Delimited identifiers allow you to specify names for database objects that are otherwise identical to SQL reserved keywords, such as TABLE, WHERE, DECLARE, and so on. The only database object for which you cannot use delimited identifiers is database name.

Delimited identifiers are case sensitive.

Delimited identifiers are compliant with the ANSI standard.

Support for Nonalphanumeric Characters

You can use delimited identifiers to specify nonalphanumeric characters in the names of database objects. However, you cannot use delimited identifiers to specify nonalpha characters in the names of storage objects such as dbspaces and blobspaces.

Support for Non-ASCII Characters in Delimited Identifiers

GLS

When you are using a nondefault locale whose code set supports non-ASCII characters, you can specify non-ASCII characters in most delimited identifiers. The rule is that if you can specify non-ASCII characters in the undelimited form of the identifier, you can also specify non-ASCII characters in the delimited form of the same identifier. See the Guide to GLS Functionality for a list of identifiers that support non-ASCII characters and for information on non-ASCII characters in delimited identifiers.

Effect of DELIMIDENT Environment Variable

To use delimited identifiers, you must set the DELIMIDENT environment variable. When you set the DELIMIDENT environment variable, database objects in double quotes (") are treated as identifiers and database objects in single quotes (') are treated as strings. If the DELIMIDENT environment variable is not set, values within double quotes are also treated as strings.

If the DELIMIDENT variable is set, the SELECT statement in the following example must be in single quotes in order to be treated as a quoted string:

PREPARE ... FROM 'SELECT * FROM customer'

Examples of Delimited Identifiers

The following example shows how to create a table with a case-sensitive table name:

CREATE TABLE "Power_Ranger" (...)

The following example shows how to create a table whose name includes a space character. If the table name were not in double quotes ("), you could not use a space character or any other nonalpha character except an underscore (_) in the name.

CREATE TABLE "My Customers" (...)

The following example shows how to create a table that uses a keyword as the table name:

CREATE TABLE "TABLE" (...)

Using Double Quotes Within a Delimited Identifier

If you want to include a double-quote (") within a delimited identifier, you must precede the double-quote (") with another double-quote ("), as shown in the following example:

CREATE TABLE "My""Good""Data" (...)

Potential Ambiguities and Syntax Errors

Although you can use almost any word as an SQL identifier, syntactic ambiguities can occur. An ambiguous statement might not produce the desired results. The following sections outline some potential pitfalls and workarounds.

Using Functions as Column Names

The following two examples show a workaround for using a function as a column name in a SELECT statement. This workaround applies to the aggregate functions (AVG, COUNT, MAX, MIN, SUM) as well as the function expressions (algebraic, exponential and logarithmic, time, hex, length, dbinfo, trigonometric, and trim functions).

Using avg as a column name causes the following example to fail because the database server interprets avg as an aggregate function rather than as a column name:

SELECT avg FROM mytab -- fails

If the DELIMIDENT environment variable is set, you could use avg as a column name as shown in the following example:

SELECT "avg" from mytab -- successful

The workaround in following example removes ambiguity by including a table name with the column name:

SELECT mytab.avg FROM mytab

If you use the keyword TODAY, CURRENT, or USER as a column name, ambiguity can occur, as shown in the following example:

CREATE TABLE mytab (user char(10),

	CURRENT DATETIME HOUR TO SECOND,TODAY DATE)



INSERT INTO mytab VALUES('josh','11:30:30','1/22/89')



SELECT user,current,today FROM mytab

The database server interprets user, current, and today in the SELECT statement as the SQL functions USER, CURRENT, and TODAY. Thus, instead of returning josh, 11:30:30,1/22/89, the SELECT statement returns the current user name, the current time, and the current date.

If you want to select the actual columns of the table, you must write the SELECT statement in one of the following ways:

SELECT mytab.user,mytab.current,mytab.today FROM mytab;


EXEC SQL select * from mytab;

Using Keywords as Column Names

Specific workarounds exist for using a keyword as a column name in a SELECT statement or other SQL statement. In some cases, there might be more than one suitable workaround.

Using ALL, DISTINCT, or UNIQUE as a Column Name

If you want to use the ALL, DISTINCT, or UNIQUE keywords as column names in a SELECT statement, you can take advantage of a workaround.

First, consider what happens when you try to use one of these keywords without a workaround. In the following example, using all as a column name causes the SELECT statement to fail because the database server interprets all as a keyword rather than as a column name:

SELECT all FROM mytab -- fails

You need to use a workaround to make this SELECT statement execute successfully. If the DELIMIDENT environment variable is set, you can use all as a column name by enclosing all in double quotes. In the following example, the SELECT statement executes successfully because the database server interprets all as a column name:

SELECT "all" from mytab -- successful

The workaround in the following example uses the keyword ALL with the column name all:

SELECT ALL all FROM mytab

The rest of the examples in this section show workarounds for using the keywords UNIQUE or DISTINCT as a column name in a CREATE TABLE statement.

Using unique as a column name causes the following example to fail because the database server interprets unique as a keyword rather than as a column name:

CREATE TABLE mytab (unique INTEGER) -- fails

The workaround shown in the following example uses two SQL statements. The first statement creates the column mycol; the second renames the column mycol to unique.

CREATE TABLE mytab (mycol INTEGER)



RENAME COLUMN mytab.mycol TO unique

The workaround in the following example also uses two SQL statements. The first statement creates the column mycol; the second alters the table, adds the column unique, and drops the column mycol.

CREATE TABLE mytab (mycol INTEGER)



ALTER TABLE mytab 

	ADD (unique integer)

	DROP (mycol)

Using INTERVAL or DATETIME as a Column Name

The examples in this section show workarounds for using the keyword INTERVAL (or DATETIME) as a column name in a SELECT statement.

Using interval as a column name causes the following example to fail because the database server interprets interval as a keyword and expects it to be followed by an INTERVAL qualifier:

SELECT interval FROM mytab -- fails

If the DELIMIDENT environment variable is set, you could use interval as a column name, as shown in the following example:

SELECT "interval" from mytab -- successful

The workaround in the following example removes ambiguity by specifying a table name with the column name:

SELECT mytab.interval FROM mytab;

The workaround in the following example includes an owner name with the table name:

SELECT josh.mytab.interval FROM josh.mytab;

Using rowid as a Column Name

Every nonfragmented table has a virtual column named rowid. To avoid ambiguity, you cannot use rowid as a column name. Performing the following actions causes an error:

Creating a table or view with a column named rowid
Altering a table by adding a column named rowid
Renaming a column to rowid

You can, however, use the term rowid as a table name.

CREATE TABLE rowid (column INTEGER,

	date DATE, char CHAR(20))

Important: Informix recommends that you use primary keys as an access method rather than exploiting the rowid column.

Using Keywords as Table Names

The examples in this section show workarounds that involve owner naming when you use the keyword STATISTICS or OUTER as a table name. This workaround also applies to the use of STATISTICS or OUTER as a view name or synonym.

Using statistics as a table name causes the following example to fail because the database server interprets it as part of the UPDATE STATISTICS syntax rather than as a table name in an UPDATE statement:

UPDATE statistics SET mycol = 10

The workaround in the following example specifies an owner name with the table name, to avoid ambiguity:

UPDATE josh.statistics SET mycol = 10

Using outer as a table name causes the following example to fail because the database server interprets outer as a keyword for performing an outer join:

SELECT mycol FROM outer -- fails

The workaround in the following example uses owner naming to avoid ambiguity:

SELECT mycol FROM josh.outer

Workarounds That Use the Keyword AS

In some cases, although a statement is not ambiguous and the syntax is correct, the database server returns a syntax error. The preceding pages show existing syntactic workarounds for several situations. You can use the AS keyword to provide a workaround for the exceptions.

You can use the AS keyword in front of column labels or table aliases.

The following example uses the AS keyword with a column label:

SELECT column-name AS display-label FROM table-name

The following example uses the AS keyword with a table alias:

SELECT select-list FROM table-name AS table-alias

Using AS with Column Labels

The examples in this section show workarounds that use the AS keyword with a column label. The first two examples show how you can use the keyword UNITS (or YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION) as a column label.

Using units as a column label causes the following example to fail because the database server interprets it as a DATETIME qualifier for the column named mycol:

SELECT mycol units FROM mytab

The workaround in the following example includes the AS keyword:

SELECT mycol AS units FROM mytab;

The following examples show how the AS or FROM keyword can be used as a column label.

Using as as a column label causes the following example to fail because the database server interprets as as identifying from as a column label and thus finds no required FROM clause:

SELECT mycol as from mytab -- fails

The following example repeats the AS keyword:

SELECT mycol AS as from mytab

Using from as a column label causes the following example to fail because the database server expects a table name to follow the first from:

SELECT mycol from FROM mytab -- fails

The following example uses the AS keyword to identify the first from as a column label:

SELECT mycol AS from FROM mytab

Using AS with Table Aliases

The examples in this section show workarounds that use the AS keyword with a table alias. The first pair shows how to use the ORDER, FOR, GROUP, HAVING, INTO, UNION, WITH, CREATE, GRANT, or WHERE keyword as a table alias.

Using order as a table alias causes the following example to fail because the database server interprets order as part of an ORDER BY clause:

SELECT * FROM mytab order -- fails

The workaround in the following example uses the keyword AS to identify order as a table alias:

SELECT * FROM mytab AS order;

The following two examples show how to use the keyword WITH as a table alias.

Using with as a table alias causes the following example to fail because the database server interprets the keyword as part of the WITH CHECK OPTION syntax:

EXEC SQL select * from mytab with; -- fails

The workaround in the following example uses the keyword AS to identify with as a table alias:

EXEC SQL select * from mytab as with;

The following two examples show how to use the keyword CREATE (or GRANT) as a table alias.

Using create as a table alias causes the following example to fail because the database server interprets the keyword as part of the syntax to create an entity such as a table, synonym, or view:

EXEC SQL select * from mytab create; -- fails

The workaround in the following example uses the keyword AS to identify create as a table alias:

EXEC SQL select * from mytab as create;

Fetching Keywords as Cursor Names

In a few situations, no workaround exists for the syntactic ambiguity that occurs when a keyword is used as an identifier in an SQL program.

In the following example, the FETCH statement specifies a cursor named next. The FETCH statement generates a syntax error because the preprocessor interprets next as a keyword, signifying the next row in the active set and expects a cursor name to follow next. This error occurs whenever the keyword NEXT, PREVIOUS, PRIOR, FIRST, LAST, CURRENT, RELATIVE, or ABSOLUTE is used as a cursor name.

/* This code fragment fails */

EXEC SQL declare next cursor for

	select customer_num, lname from customer;



EXEC SQL open next;

EXEC SQL fetch next into :cnum, :lname;

Using Keywords as Variable Names

If you use any of the following keywords as identifiers for variables in a routine, you can create ambiguous syntax.


`CURRENT`	`OFF`
`DATETIME`	`ON`
`GLOBAL`	`PROCEDURE`
`INTERVAL`	`SELECT`
`NULL`

Using CURRENT, DATETIME, INTERVAL, and NULL in INSERT

A routine cannot insert a variable using the CURRENT, DATETIME, INTERVAL, or NULL keyword as the name.

For example, if you define a variable called null, when you try to insert the value null into a column, you receive a syntax error, as shown in the following example:

CREATE PROCEDURE problem()

.

.

.

DEFINE null INT;

LET null = 3;

INSERT INTO tab VALUES (null); -- error, inserts NULL, not 3

Using NULL and SELECT in a Condition

If you define a variable with the name null or select, using it in a condition that uses the IN keyword is ambiguous. The following example shows three conditions that cause problems: in an IF statement, in a WHERE clause of a SELECT statement, and in a WHILE condition:

CREATE PROCEDURE problem()

.

.

.

DEFINE x,y,select, null, INT;

DEFINE pfname CHAR[15];

LET x = 3; LET select = 300;

LET null = 1;

IF x IN (select, 10, 12) THEN LET y = 1; -- problem if



IF x IN (1, 2, 4) THEN

SELECT customer_num, fname INTO y, pfname FROM customer

	WHERE customer IN (select , 301 , 302, 303); -- problem in



WHILE x IN (null, 2) 	-- problem while

.

.

.

END WHILE;

You can use the variable select in an IN list if you ensure it is not the first element in the list. The workaround in the following example corrects the IF statement shown in the preceding example:

 IF x IN (10, select, 12) THEN LET y = 1; -- problem if

No workaround exists to using null as a variable name and attempting to use it in an IN condition.

Using ON, OFF, or PROCEDURE with TRACE

If you define a procedure variable called on, off, or procedure, and you attempt to use it in a TRACE statement, the value of the variable does not trace. Instead, the TRACE ON, TRACE OFF, or TRACE PROCEDURE statements execute. You can trace the value of the variable by making the variable into a more complex expression. The following example shows the ambiguous syntax and the workaround:

DEFINE on, off, procedure INT;



TRACE on;				--ambiguous

TRACE 0+ on;				--ok

TRACE off;				--ambiguous

TRACE ''||off;				--ok



TRACE procedure;	--ambiguous

TRACE 0+procedure;			--ok

Using GLOBAL as a Variable Name

If you attempt to define a variable with the name global, the define operation fails. The syntax shown in the following example conflicts with the syntax for defining global variables:

DEFINE global INT; -- fails;

If the DELIMIDENT environment variable is set, you could use global as a variable name, as shown in the following example:

DEFINE "global" INT; -- successful

Using EXECUTE, SELECT, or WITH as Cursor Names

Do not use an EXECUTE, SELECT, or WITH keyword as the name of a cursor. If you try to use one of these keywords as the name of a cursor in a FOREACH statement, the cursor name is interpreted as a keyword in the FOREACH statement. No workaround exists.

The following example does not work:

DEFINE execute INT;

FOREACH execute FOR SELECT col1 -- error, looks like

							 	 	 	 	 	-- FOREACH EXECUTE PROCEDURE

	 INTO var1 FROM tab1; --

SELECT Statements in WHILE and FOR Statements

If you use a SELECT statement in a WHILE or FOR loop, and if you need to enclose it in parentheses, enclose the entire SELECT statement in a BEGIN...END block. The SELECT statement in the first WHILE statement in the following example is interpreted as a call to the procedure var1; the second WHILE statement is interpreted correctly:

DEFINE var1, var2 INT;

WHILE var2 = var1

	SELECT col1 INTO var3 FROM TAB -- error, seen as call var1()

	UNION

	SELECT co2 FROM tab2;

END WHILE



WHILE var2 = var1

	BEGIN

		SELECT col1 INTO var3 FROM TAB -- ok syntax

		UNION

		SELECT co2 FROM tab2;

	END

END WHILE

The SET Keyword in the ON EXCEPTION Statement

If you use a statement that begins with the keyword SET inside the statement ON EXCEPTION, you must enclose it in a BEGIN...END block. The following list shows some of the SQL statements that begin with the keyword SET.


`SET`	`SET` `LOCK` `MODE`
`SET` `DEBUG` `FILE`	`SET` `LOG`
`SET` `EXPLAIN`	`SET` `OPTIMIZATION`
`SET` `ISOLATION`	`SET` `PDQPRIORITY`

The following examples show incorrect and correct use of a SET LOCK MODE statement inside an ON EXCEPTION statement.

The following ON EXCEPTION statement returns an error because the SET LOCK MODE statement is not enclosed in a BEGIN...END block:

ON EXCEPTION IN (-107)

	SET LOCK MODE TO WAIT; -- error, value expected, not 'lock'

END EXCEPTION

The following ON EXCEPTION statement executes successfully because the SET LOCK MODE statement is enclosed in a BEGIN...END block:

ON EXCEPTION IN (-107)

	BEGIN

	SET LOCK MODE TO WAIT; -- ok

	END

END EXCEPTION

References

In the INFORMIX-Universal Server Administrator's Guide, see the owner-naming discussion.

In the Guide to GLS Functionality, see the discussion of identifiers that support non-ASCII characters and the discussion of non-ASCII characters in delimited identifiers.

Index Name

The Index Name segment specifies the name of an index. Use the Index Name segment whenever you see a reference to an index name in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
database
The name of the database where the index resides
The database must exist.
Database Name, p. 1-856

dbservername
The name of the Universal Server database server that is home to database. The @ symbol is a literal character that introduces the database server name.
The database server that is specified in dbservername must match the name of a database server in the sqlhosts file.
Database Name, p. 1-856

owner
The user name of the owner of the index
If you are using an `ANSI`-compliant database, you must specify the owner for an index that you do not own. If you put quotation marks around the name that you enter in owner, the name is stored exactly as typed. If you do not put quotation marks around the name you enter in owner, the name is stored as uppercase letters.
The user name must conform to the conventions of your operating system.

Usage

The actual name of the index is an SQL identifier.

GLS

If you are using a nondefault locale, you can use characters from the code set of your locale in the names of indexes. For more information, see the Guide to GLS Functionality.

If you are creating an index, the name must be unique within a database.

ANSI

The owner.name combination is case sensitive. In an ANSI-compliant database, if you do not use quotes around the owner name, the name of the table owner is stored as uppercase letters. For more information, see the discussion of case sensitivity in ANSI-compliant databases on page 1-1049.

References

See the CREATE INDEX statement in this manual for information on defining indexes.

INTERVAL Field Qualifier

The INTERVAL field qualifier specifies the units for an INTERVAL value. Use the INTERVAL Field Qualifier segment whenever you see a reference to an INTERVAL field qualifier in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
f-precision
The maximum number of digits used in the fraction field. The default value of f-precision is 3.
The maximum value that you can specify in f-precision is 5.
Literal Number, p. 1-1001

precision
The number of digits in the largest number of months, days, hours, or minutes that the interval can hold. The default value of precision is 2.
The maximum value that you can specify in precision is 9.
Literal Number, p. 1-1001

y-precision
The number of digits in the largest number of years that the interval can hold. The default value of y-precision is 4.
The maximum value that you can specify in y-precision is 9.
Literal Number, p. 1-1001

Usage

The next two examples show INTERVAL data types of the YEAR TO MONTH type. The first example can hold an interval of up to 999 years and 11 months, because it gives 3 as the precision of the year field. The second example uses the default precision on the year field, so it can hold an interval of up to 9,999 years and 11 months.

YEAR (3) TO MONTH



YEAR TO MONTH

When you want a value to contain only one field, the first and last qualifiers are the same. For example, an interval of whole years is qualified as YEAR TO YEAR or YEAR (5) TO YEAR, for an interval of up to 99,999 years.

The following examples show several forms of INTERVAL qualifiers:

YEAR(5) TO MONTH



DAY (5) TO FRACTION(2)



DAY TO DAY



FRACTION TO FRACTION (4)

References

In the Informix Guide to SQL: Syntax, see the INTERVAL data type in Chapter 2 for information about specifying INTERVAL field qualifiers and using INTERVAL data in arithmetic and relational operations.

Literal Collection

The Literal Collection segment specifies the syntax for values of the collection data types: SET, LIST, and MULTISET.

Syntax

Usage

You can specify literal collection values for each of the collection data types: SET, MULTISET, or LIST. The entire literal collection value must be enclosed in quotes; each literal within the literal collection must also be enclosed in quotes, following the rule explained on page 1-992.

To specify a single literal-collection value, specify the collection type and the literal values. The following SQL statement inserts four integer values into the set_col column that is declared as SET(INT NOT NULL):

INSERT INTO table1 (set_col) VALUES ("SET{6, 9, 9, 4}")

You specify an empty collection with a set of empty braces ({}). The following INSERT statement inserts an empty list into a collection column list_col that is declared as LIST(INT NOT NULL):

INSERT INTO table2 (list_col) VALUES ("LIST{}")

If the collection is a nested collection, you must include the collection-constructor syntax for each level of collection type. Suppose you define the following column:

nest_col SET(MULTISET (INT NOT NULL) NOT NULL)

The following statement inserts three elements into the nest_col column:

INSERT INTO tabx (nest_col) 

	VALUES ("SET{'MULTISET{1, 2, 3}'}")
To learn how to use quotes in INSERT statements, see "Nested Quotation Marks".

Non-Collection Element Literal

Element Purpose Restrictions Syntax
literal opaque type
The literal representation for an opaque data type
Must be a literal that is recognized by the input support function for the associated opaque type.
Defined by the developer of the opaque type.

literal BOOLEAN
The literal representation of a BOOLEAN value
A literal BOOLEAN value can only be 't' (TRUE) or 'f' (FALSE) and must be specified as a quoted string.
Quoted String, p. 1-1014

Elements of a collection can be literal values for the following built-in data types:

The CHAR, LVARCHAR, and VARCHAR data types have literal values specified as quoted strings.

GLS

NCHAR

NVARCHAR

For more information, see

"Quoted String"

The DECIMAL, FLOAT, INT8, INTEGER, MONEY, SMALLFLOAT and SMALLINT data types have literal values specified by the Literal Number syntax.

For more information, see

"Literal Number"

The DATE data type has a literal value specified as a quoted string.

For more information, see

"Quoted String"

The DATETIME data type has literal values specified by the Literal DATETIME syntax.

For more information, see

"Literal DATETIME"

The INTERVAL data type has literal values specified by the Literal INTERVAL syntax.

For more information, see

"Literal INTERVAL"

The BOOLEAN data type has the literal values of 't' and 'f'.

BOOLEAN

Important: You cannot specify the simple-large-object data types (BYTE and TEXT) as the element type for a collection.

Collection elements can also be literal values for the following user-defined data types:

An opaque data type has a literal value that corresponds to the string representation for the opaque data type. The input support function of the opaque type defines this string representation.
A row type, named or unnamed, has a literal value specified as field values enclosed with parentheses.

When the collection element type is a named row type, you do not have to cast the values that you insert to the named row type.

For more information, see

"Literal Row"

Another collection type (SET, MULTISET, or LIST) has a literal value whose specification depends on the context its use.

A collection whose element type is another collection is called a nested collection. For information on literal collection value as a column value, see

page 1-992

Nested Quotation Marks

Whenever you nest collection literals, you use nested quotation marks. In these cases, you must follow the rule for nesting quotation marks. Otherwise, the server cannot correctly parse the strings.

The general rule is that you must double the number of quotes for each new level of nesting. For example, if you use double quotes for the first level, you must use two double quotes for the second level, four double quotes for the third level, eight for the fourth level, sixteen for the fifth level, and so on. Likewise, if you use single quotes for the first level, you must use two single quotes for the second level and four single quotes for the third level.

There is no limit to the number of levels you can nest, as long as you follow this rule.

Example of Nested Quotation Marks

The following example illustrates the case for two levels of nested collection literals, using double quotes. Table tab5 is a one-column table whose column, set_col, is a nested collection type.

The following statement creates the tab5 table:

CREATE TABLE tab5 (set_col SET(SET(INT NOT NULL) NOT NULL));

The following statement inserts values into the table tab5:

INSERT INTO tab5 VALUES (
"SET{""SET{34, 56, 23, 33}""}"

)

For any individual literal value, the opening quotation marks and the closing quotation marks must match. In other words, if you open a literal with two double quotes, you must close that literal with two double quotes
(""a literal value"").

The rules for nested quotation marks apply to all literals-collection literals and non-collection literals-that are nested in a single collection value.

E/C

To specify nested quotes within an SQL statement in an ESQL/C program, you use the C escape character for every double quote inside a single-quote string. Otherwise, the ESQL/C preprocessor cannot correctly interpret the literal collection value. For example, the preceding INSERT statement on the tab5 table would appear in an ESQL/C program as follows:

EXEC SQL insert into tab5

	values ('set{\"set{34, 56, 23, 33}\"}');

For more information, see the chapter on complex data types in the INFORMIX-ESQL/C Programmer's Manual.

References

See the INSERT, UPDATE, and SELECT statements in this manual. See also the Row Literal segment.

In the Informix Guide to SQL: Tutorial, see Chapter 10 and Chapter 12 for information about how to create and use collection data types.

In the Informix Guide to SQL: Syntax, see the SET, MULTISET, and LIST data types in Chapter 2.

In the Guide to GLS Functionality, see the discussion of customizing NCHAR and NVARCHAR data types.

Literal DATETIME

The Literal DATETIME segment specifies a literal DATETIME value. Use the Literal DATETIME segment whenever you see a reference to a literal DATETIME in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
dd
The day expressed in digits
You can specify up to 2 digits.
Literal Number, p. 1-1001

f
The decimal fraction of a second expressed in digits
You can specify up to 5 digits.
Literal Number, p. 1-1001

hh
The hour expressed in digits
You can specify up to 2 digits.
Literal Number, p. 1-1001

mi
The minute expressed in digits
You can specify up to 2 digits.
Literal Number, p. 1-1001

mo
The month expressed in digits
You can specify up to 2 digits.
Literal Number, p. 1-1001

space
A space character
You cannot specify more than 1 space character.
The space character is a literal value that you enter by pressing the space bar on the keyboard.

ss
The second expressed in digits
You can specify up to 2 digits.
Literal Number, p. 1-1001

yyyy
The year expressed in digits
You can specify up to 4 digits. If you specify 2 digits, the database server uses the setting of the DBCENTURY environment variable to extend the year value. If the DBCENTURY environment variable is not set, the database server uses the current century to extend the year value.
Literal Number, p. 1-1001

Usage

You must specify both a numeric date and a DATETIME field qualifier for this date in the Literal DATETIME segment. The DATETIME field qualifier must correspond to the numeric date you specify. For example, if you specify a numeric date that includes a year as the largest unit and a minute as the smallest unit, you must specify YEAR TO MINUTE as the DATETIME field qualifier.

The following examples show literal DATETIME values:

DATETIME (93-3-6) YEAR TO DAY



DATETIME (09:55:30.825) HOUR TO FRACTION



DATETIME (93-5) YEAR TO MONTH

The following example shows a literal DATETIME value used with the EXTEND function:

EXTEND (DATETIME (1993-8-1) YEAR TO DAY, YEAR TO MINUTE)

	- INTERVAL (720) MINUTE (3) TO MINUTE

References

In the Informix Guide to SQL: Syntax, see the DATETIME data type in Chapter 2 and the DBCENTURY environment variable in Chapter 3.

In the Guide to GLS Functionality, see the discussion of customizing DATETIME values for a locale.

Literal INTERVAL

The Literal INTERVAL segment specifies a literal INTERVAL value. Use the Literal INTERVAL segment whenever you see a reference to a literal INTERVAL in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
dd
The number of days
The maximum number of digits allowed is 2, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

f
The decimal fraction of a second
You can specify up to 5 digits, depending on the precision given to the fractional portion in the INTERVAL field qualifier.
Literal Number, p. 1-1001

hh
The number of hours
The maximum number of digits allowed is 2, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

mi
The number of minutes
The maximum number of digits allowed is 2, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

mo
The number of months
The maximum number of digits allowed is 2, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

space
A space character
You cannot use any other character in place of the space character.
The space character is a literal value that you enter by pressing the space bar on the keyboard.

ss
The number of seconds
The maximum number of digits allowed is 2, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

yyyy
The number of years
The maximum number of digits allowed is 4, unless this is the first field and the precision is specified differently by the INTERVAL field qualifier.
Literal Number, p. 1-1001

Usage

The following examples show literal INTERVAL values:

INTERVAL (3-6) YEAR TO MONTH

INTERVAL (09:55:30.825) HOUR TO FRACTION

INTERVAL (40 5) DAY TO HOUR

References

In the Informix Guide to SQL: Syntax, see the INTERVAL data type in Chapter 2 for information about using INTERVAL data in arithmetic and relational operations.

Literal Number

A literal number is an integer or noninteger (floating) constant. Use the Literal Number segment whenever you see a reference to a literal number in a syntax diagram.

Syntax

Element Purpose Restrictions Syntax
digit
A digit that forms part of the literal number. See "Floating and Decimal Numbers" for the significance of digits that follow the decimal point or the `E` symbol.
You must specify a value between `0` and `9`, inclusive.
Digits are literal values that you enter from the keyboard.

Usage

Literal numbers do not contain embedded commas; you cannot use a comma to indicate a decimal point. You can precede literal numbers with a plus or a minus sign.

Integers

Integers do not contain decimal points. The following examples show some integers:

10			-27	 		25567

Floating and Decimal Numbers

Floating and decimal numbers contain a decimal point and/or exponential notation. The following examples show floating and decimal numbers:

123.456			1.23456E2			123456.0E-3

The digits to the right of the decimal point in these examples are the decimal portions of the numbers.

The E that occurs in two of the examples is the symbol for exponential notation. The digit that follows E is the value of the exponent. For example, the number 3E5 (or 3E+5) means 3 multiplied by 10 to the fifth power, and the number 3E-5 means 3 multiplied by 10 to the minus fifth power.

Literal Numbers and the MONEY Data Type

When you use a literal number as a MONEY value, do not precede it with a money symbol or include commas.

References

See the discussions of numeric data types, such as DECIMAL, FLOAT, INTEGER, and MONEY, in Chapter 2 of the Informix Guide to SQL: Syntax.

Literal Row

The Literal Row segment specifies the syntax for literal values of named row types and unnamed row types.

Syntax

Usage

You can specify literal values for named row types and unnamed row types. The literal row value is introduced with a ROW constructor. The entire literal row value must be enclosed in quotes.

The format of the value for each field of the row type must be compatible with the data type of the corresponding field.

Non-Row Literal Values

Element Purpose Restrictions Syntax
literal opaque type
The literal representation for an opaque data type
Must be a literal that is recognized by the input support function for the associated opaque type.
Defined by the developer of the opaque type.

literal `BOOLEAN`
The literal representation of a `BOOLEAN` value
A literal `BOOLEAN` value can only be 't' (`TRUE`) or 'f' (`FALSE`) and must be specified as a quoted string.
Quoted String, p. 1-1014

Literals of an Unnamed Row Type

To specify a literal value for an unnamed row type, introduce the literal row with the ROW constructor and enclose the values in parentheses. For example, suppose you define the rectangles table, as follows:

CREATE TABLE rectangles 

(

	area FLOAT, 

	rect ROW(x INTEGER, y INTEGER, length FLOAT, width FLOAT),

)

The following INSERT statement inserts values into the rect column of the rectangles table:

INSERT INTO rectangles (rect)

	VALUES ("ROW(7, 3, 6.0, 2.0)")

LIterals of a Named Row Type

To specify a literal value for a named row, type, introduce the literal row with the ROW type constructor and enclose the literal values for each field in parentheses. In addition, you can cast the row literal to the appropriate named row type to ensure that the row value is generated as a named row type. The following statements create the named row type address_t and the employee table:

CREATE ROW TYPE address_t 
(
street CHAR(20), 
city CHAR(15),

state CHAR(2),

zipcode CHAR(9)
);


CREATE TABLE employee 

(

	name CHAR(30),

	address address_t

);

The following INSERT statement inserts values into the address column of the employee table:

INSERT INTO employee (address)
VALUES  (
"ROW('103 Baker St',  'Tracy','CA', 94060)"::address_t)

Literals for Nested Rows

If the literal value is for a nested row, specify the ROW type constructor for each row level. However only the outermost row is enclosed in quotes. For example, suppose you create the following emp_tab table:

CREATE TABLE emp_tab

(

	emp_name CHAR(10),

	emp_info ROW( stats ROW(x INT, y INT, x FLOAT))
);

The following INSERT statement adds a row to the emp_tab table:

INSERT INTO emp_tab 
VALUES ('joe boyd', "ROW(ROW(8,1,12.0))" )

Field-Level Literal Values

Fields of a row can be literal values for the following built-in data types:

The CHAR, LVARCHAR, and VARCHAR data types have literal values specified as quoted strings.

GLS

NCHAR

NVARCHAR

For more information, see

"Quoted String"

The DECIMAL, FLOAT, INT8, INTEGER, MONEY, SMALLFLOAT and SMALLINT data types have literal values specified by the Literal Number syntax.

For more information, see

"Literal Number"

The DATE data type has a literal value specified as a quoted string.

For more information, see

"Quoted String"

The DATETIME data type has literal values specified by the Literal DATETIME syntax.

For more information, see

"Literal DATETIME"

The INTERVAL data type has literal values specified by the Literal INTERVAL syntax.

For more information, see

"Literal INTERVAL"

The BOOLEAN data type has the literal values of 't' and 'f'.

For more information, see the description of the BOOLEAN data type in

Chapter 2

Important: You cannot specify the simple-large-object data types (BYTE and TEXT) as the field type for a row.

Field values can also be literal values for the following user-defined data types:

An opaque data type has a literal value that corresponds to the string representation for the opaque data type. The input support function of the opaque type defines this string representation
A collection type (SET, MULTISET, or LIST) has a literal value whose specification depends on the context its use.

For information on literal collection value as a column value, see

page 1-992

Another row type, named or unnamed, has a literal value specified as field values enclosed with parentheses.

nested row

References

See the INSERT, UPDATE, and SELECT statements in this manual. See the CREATE ROW TYPE statement for information on named row types. See "Constructor Expressions" of the Expression segment for information on ROW constructors. See also the Collection Literal segment.

Procedure Name

The Procedure Name segment specifies the name of a procedure.

Syntax

Element Purpose Restrictions Syntax
database
The name of the database where the procedure resides
The database must exist.
Database Name, p. 1-856

dbservername
The name of the server that is home to database. The @ symbol is a literal character that introduces the database server name.
The database server that is specified in dbservername must match the name of a database server in the sqlhosts file.
Database Name, p. 1-856

owner
The user name of the owner of the procedure
If you are using an `ANSI`-compliant database, you must specify an owner for a procedure you do not own. If you do not specify an owner, the default owner is the current user.
The user name must conform to the conventions of your operating system.

Usage

In a statement that calls for a Procedure Name, you can enter an identifier with an optional owner name, database name, and server name. The database and server names allow you to use a procedure stored on a remote database. A Procedure Name with a database name, server name, and owner name is called a fully qualified procedure name.

The actual name of the procedure is an SQL identifier.

ANSI

The owner name is case sensitive. In an ANSI database, if you type quotation marks around the name, it is stored as you type it. If you do not use quotation marks, the name is stored as uppercase letters. For more information, see the discussion of case sensitivity in ANSI-compliant databases on page 1-1049.

GLS

If you are using a nondefault locale, you can use characters from the code set of your locale in the names of procedures. For more information, see the Guide to GLS Functionality.

Routine Overloading

Due to routine overloading, a procedure name does not need to be unique in Universal Server. You can define more than one procedure with the same name and different parameter lists.

Procedures are uniquely identified by their signature. A procedure's signature includes the following items:

The routine type (FUNCTION or PROCEDURE)
The routine name
The number, data type, and order of the procedure's parameters

If a procedure name is not unique, Universal Server uses routine resolution to identify the instance of the procedure to execute. For more information about routine resolution, see the Extending INFORMIX-Universal Server: User-Defined Routines manual.

Database Name and Server Name

When you add the database name and server name options, you use a fully qualified procedure name to specify a remote procedure. You can use those options when:

All arguments passed to the procedure have built-in data types.
The instance of the procedure that is invoked has built-in data types for all of its parameters.

References

In this manual, see the CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE, EXECUTE FUNCTION, and EXECUTE PROCEDURE statements. See also the Function Name and Specific Name segments.

In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about how to create and use SPL routines.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

Identifier

Usage

Support for Non-ASCII Characters in Identifiers

Examples of Delimited Identifiers

Using Double Quotes Within a Delimited Identifier

Potential Ambiguities and Syntax Errors

Using Keywords as Column Names

Using INTERVAL or DATETIME as a Column Name

Using rowid as a Column Name

Using Keywords as Table Names

Workarounds That Use the Keyword AS

Using AS with Column Labels

Using AS with Table Aliases

Fetching Keywords as Cursor Names

Using Keywords as Variable Names

Using NULL and SELECT in a Condition

Using GLOBAL as a Variable Name

Using EXECUTE, SELECT, or WITH as Cursor Names

SELECT Statements in WHILE and FOR Statements

The SET Keyword in the ON EXCEPTION Statement

References

References

Usage

Non-Collection Element Literal

References

References

Usage

Floating and Decimal Numbers

Usage

Literals of an Unnamed Row Type

LIterals of a Named Row Type

Literals for Nested Rows

Field-Level Literal Values

Usage

References

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book