Identifiers are the smallest lexical units used in entity names.
The following entities referenced in SQL statements have identifiers:
Identifiers for some entities can be qualified by other identifiers. For example, table identifiers can be qualified by schema names.
Sometimes an identifier by itself does not uniquely identify an entity. For instance, a SELECT statement may include two columns with the same identifier, each from a different table. To uniquely identify each of these columns, you must qualify each column identifier with the associated table name or alias.
To qualify an identifier, specify the qualifier first, followed by a period (.), followed by the identifier you are qualifying. The qualified identifier in the following example identifies the EMPLOYEE table associated with the DEMOEMPL schema:
demoempl.employee
Authorization-identifier and table-name
Authorization-identifier and table-name are syntactic elements representing identifiers that occur in multiple SQL statements. For expanded syntax for these elements, see Expansion of Authorization-identifier and Expansion of Table-name.
Valid characters
An identifier consists of a combination of:
The first character of an identifier must be a letter, @, $, or #.
Maximum length
Identifiers for all entities except columns, access modules, segments, RCMs, and external names of SQL-invoked routines can be as many as 18-characters long.
Identifiers for access modules, segments, RCMs, and external names of SQL-invoked routines can be as many as eight-characters long.
An identifier for a column can be as many as 32-characters long.
Why delimit identifiers
You can delimit an identifier in double quotation marks to:
"&ATM*F(0517). MA"
To include a double quotation mark as part of the identifier itself, use two consecutive double quotation marks. For example:
"M1K""L9&ZZ".
Lowercase letters in quotation marks are not equal to uppercase letters or to lowercase letters that are not in quotation marks. In the example below, the identifiers on the left all identify the same table; the identifier on the right identifies a different table:
employee "employee" EMPLOYEE "EMPLOYEE"
Placement of quotation marks
If one or more parts of a qualified identifier require quotation marks, place the quotation marks only around the individual parts. Do not include two identifiers in one set of quotation marks. For example, both parts of the following qualified identifier require quotation marks:
"temp-tab-1"."Commission to Date"
When you calculate the length of an identifier, do not include delimiting quotation marks.
Eliminating ambiguity
You should avoid issuing an SQL statement which specifies an identifier that matches a keyword in the syntax for the statement. This eliminates potential ambiguity that could cause CA IDMS to read the statement in a way that is not meant.
If you must use a keyword as an identifier, delimit the identifier with double quotation marks as described in Delimited Identifiers.
Recognizing keywords
The syntax diagrams in this guide present keywords in roman (that is, nonitalicized) type.
For example, in the following syntax for CREATE VIEW, the keywords are CREATE VIEW, AS, and WITH CHECK OPTION:
►►─── CREATE VIEW ─┬────────────────┬─ view-identifier ───────────────────────► └─ schema-name. ─┘ ►─┬────────────────────────────────┬─────────────────────────────────────────► │ ┌──────── , ─────────┐ │ └─ ( ─▼─ view-column-name ─┴─ ) ─┘ ►─── AS query-specification ─────────────────────────────────────────────────► ►─┬──────────────────────────┬───────────────────────────────────────────────► └─ order-by-specification ─┘ ►─┬─────────────────────┬────────────────────────────────────────────────────►◄ └─ WITH CHECK OPTION ─┘
Delimited example identifier
If it were necessary to create a view called AS, you should identify the view as follows:
create view "AS" (col1, col2, col3) as (select...
|
Copyright © 2014 CA.
All rights reserved.
|
|