SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-14
Understanding ANSI External and Internal Names
Understanding ANSI External and Internal
Names
ANSI SQL objects have both external and internal names. When objects are created in
NonStop SQL/MX, they are given external names. However, when the same objects
are stored in the metadata, they are stored by their internal name. You need to be
aware of external and internal naming when performing metadata queries.
When you use an object name in a search condition, such as a comparison predicate,
use the internal name. When you use the name as part of the table in a table-ref
clause (for example, the FROM clause), use the external name.
Regular Identifiers and Object Naming
If the object name is a regular identifier, the external name is not case sensitive.
However, the internal name is always stored in uppercase:
Delimited Identifiers and Object Naming
If the object name is a delimited identifier, the external name is enclosed in double
quotes and any double quotes found inside the name are doubled. The internal name
is the same name (case intact), but the surrounding double quotes are removed, and
the doubled up double quotes are replaced with only one double quote:
This query uses internal and external names:
>> select substring (object_name, 1, 15) as TABLENAME,
case object_security_class
when 'UM' then 'USER METADATA TABLE'
when 'UT' then 'USER-DEFINED TABLE'
when 'SM' then 'SYSTEM METADATA TABLE'
when 'MU' then 'OTHER'
end as SECURITY_CLASS,
create_time,
object_owner as owner
from "my "" catalog".definition_schema_version_1200.objects
where object_type = 'BT'and schema_uid =
(select schema_uid
from nonstop_sqlmx_figaro.system_schema.schemata
where schema_name = 'SALES' and cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys
where cat_name = 'my " catalog'
)
)
Example External Name Internal Name
CREATE TABLE mytable . . . mytable MYTABLE
Example External Name Internal Name
CREATE TABLE "My
""Delimited"" Table" ...
"My
""Delimited""
Table"
My "Delimited"
Table