Qualified names

You can qualify the names of objects in an SQL statement in order to uniquely identify different objects that have the same name. The following qualifications are possible:

• qualification with the database name for:
  schema, space, storage group, table, index, integrity constraint and routine

• qualification with the schema name for:
  table, index, integrity constraint and routine

• qualification with the table name or the correlation name for:
  column (see "Table specifications")

The syntax overview below illustrates these possibilities:

---

qualified_name ::=

{ index

| integrity_constraint_name

| routine

| schema

| space

| stogroup

| table }

index ::= [[ catalog .] unqual_schema_name .] unqual_index_name

integrity_constraint_name ::= [[ catalog .] unqual_schema_name .] unqual_constraint_name

routine ::= [[ catalog .] unqual_schema_name .] unqual_routine_name

schema ::= [ catalog .] unqual_schema_name

space ::= [ catalog .] unqual_space_name

stogroup ::= [ catalog .] unqual_stogroup_name

table ::=

{    [[ catalog .] unqual_schema_name .] unqual_base_table_name | 

    [[ catalog .] unqual_schema_name .] unqual_view_name |
        correlation_name }

---

Implicit qualification

The following implicit qualification is valid:

• If no schema qualification is specified, the name refers to the default schema.

• If no catalog qualification is specified, the name refers to the default database.

The default schema and database are set with the precompiler option SOURCE-PROPERTIES (see the “ ESQL-COBOL for SESAM/SQL-Server” manual). The default database and schema names can be redefined with SET CATALOG and SET SCHEMA respectively. The redefined default values are valid for all statements prepared with PREPARE or executed with EXECUTE IMMEDIATE from the time redefinition is performed up until the defaults are redefined again or until the end of the SQL session.

Other rules for implicit qualification apply to CREATE and GRANT statements within a CREATE SCHEMA statement (see section "CREATE SCHEMA - Create schema" ).

Example

Qualifying a table name indicates the schema and database to which the table belongs:

ordercust.orderproc.customers:

CUSTOMERS table in the ORDERPROC schema of the ORDERCUST database

orderproc.customers:

CUSTOMERS table in the ORDERPROC schema of the default database.

customers:

CUSTOMERS table in the default schema

Overview

Name type	Examples	Meaning
Regular name	Customers customers	“Customers” and “customers” are equivalent
	job_2	Numerics and the underscore character are permitted
Special name	"TAB-ELLE" ";$&%!"	Special characters are permitted
	"with_2_quotes:"""""	Quotes must be entered twice
Unqualified name	orderproc	Schema ORDERPROC
Qualified name	ordercust.orderproc.View1	Table VIEW1 in the schema ORDERPROC of the database ORDERCUST
	"View"."SELECT(5)"	Single column SELECT(5) in the table View
	"VIEW"."SELECT"(5)	Occurrence of the multiple column SELECT of the table VIEW
	A.order_num	Column name ORDER_NUM qualified by the correlation name A

Table 10: Names in SESAM/SQL