Informix Guide to SQL: Syntax Segments

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.

The elements of an identifier cannot be separated by blanks. To include a space character in an identifier, you must specify a delimited identifier. For more information, see Delimited Identifiers.

An identifier can contain up to 128 bytes, inclusive. For example, the following table name is valid: employee_information.

An identifier can contain up to 18 bytes, inclusive. For example, the following table name is valid: employee_info.

If you are using a multibyte code set, keep in mind that the maximum length of an identifier refers to the number of bytes, not the number of characters. For further information on the GLS aspects of identifiers, see the Informix Guide to GLS Functionality.

The database server checks the internal version number of the client application and the setting of the IFX_LONGID environment variable to determine whether a client application is capable of handling long identifiers (identifiers that are up to 128 bytes in length). For further information on the IFX_LONGID environment variable, see the Informix Guide to SQL: Reference.

You can specify the name of a database object with uppercase characters, but the database server shifts the name to lowercase characters unless the DELIMIDENT environment variable is set and the name of the database object is enclosed in double quotes. When these conditions are true, the database server treats the name of the database object as a delimited identifier and preserves the uppercase characters in the name. For further information on delimited identifiers, see Delimited Identifiers.

Although you can use almost any word as an identifier, syntactic ambiguities can result from using reserved words as identifiers in SQL statements. The statement might fail or might not produce the expected results. For a discussion of the syntactic ambiguities that can result from using reserved words as identifiers and an explanation of workarounds for these problems, see Potential Ambiguities and Syntax Errors.

For a list of all the reserved words in the Informix implementation of SQL in Dynamic Server, see Appendix A, "Reserved Words for Dynamic Server".

For a list of all the reserved words in the Informix implementation of SQL in Enterprise Decision Server, see Appendix B, "Reserved Words for Enterprise Decision Server".

Delimited identifiers provide the easiest and safest way to use a reserved word as an identifier without causing syntactic ambiguities. No workarounds are necessary when you use a reserved word as a delimited identifier. For the syntax and usage of delimited identifiers, see Delimited Identifiers.

Tip: If you receive an error message that seems unrelated to the statement that caused the error, check to determine whether the statement uses a reserved word as an undelimited identifier.

Support for Non-ASCII Characters in Identifiers

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 Informix Guide to GLS Functionality.

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.

When you create a database object, avoid including one or more trailing blanks in a delimited identifier. In other words, immediately follow the last non-blank character of the name with the end quote.

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

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. For a list of identifiers that support non-ASCII characters and for information on non-ASCII characters in delimited identifiers, see the Informix Guide to GLS Functionality.

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

If the DELIMIDENT environment 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'

If a delimited identifier name is used in the SELECT statement that defines a view, then the DELIMIDENT environment variable must be set in order for the view to be accessed, even if the view name itself contains no special characters.

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 enclosed in double quotes ("), you could not use a space character 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" (...)

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

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

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.

The following two examples show a workaround for using a built-in 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 the 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/98')



SELECT user,current,today FROM mytab

The database server interprets user, current, and today in the SELECT statement as the built-in 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;

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

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 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)

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;

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

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

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

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;

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 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;

If you use any of the following keywords as identifiers for variables in a user-defined routine (UDR), you can create ambiguous syntax.

A UDR 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

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.

If you define an SPL 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

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

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; --

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 BEGINEND statement 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

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

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 BEGINEND statement 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 BEGINEND statement block:

ON EXCEPTION IN (-107)
BEGIN
SET LOCK MODE TO WAIT; -- ok
END
END EXCEPTION

For a discussion of owner naming, see your Performance Guide.

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