Identifiers

You are looking at an older version of the documentation. The latest version is found here.

SQL commands contain references to tables and columns in the form of identifiers uniquely identifying the tables and columns in the context of the command. All queries are processed in the context of a virtual database, or VDB. As information can be federated across multiple sources, tables and columns must be scoped in some manner to avoid conflicts. This scoping is provided by schemas containing the information for each data source or set of views.

Fully-qualified table and column names are of the following form where the separate 'parts' of the identifier are delimited by periods:

• TABLE: <schema_name>.<table_spec>
• COLUMN: <schema_name>.<table_spec>.<column_name>

Syntax Rules

• Identifiers can consist of alphanumeric characters and the underscore (_) character and must begin with an alphabetic character. Any Unicode character may be used in an identifier;
• Identifiers in double quotes can consist of any characters. The double-quote character can be escaped with an additional double quote, e.g. "some "" id";

• As different data sources organize tables in different ways, some prepending catalogue or schema or user information, the Data Virtuality Server allows table specification to be a dot-delimited construct;

  

  When a table specification contains a dot, resolving will allow for the match of a partial name against any number of the end segments in the name, e.g. a table with the fully-qualified name vdbname."sourcescema.sourcetable" will match the partial name source table.

• Columns, schemas, and aliases identifiers cannot contain a dot;
• Identifiers, even when quoted, are not case-sensitive in the Data Virtuality Server. 

Here are some examples of valid fully-qualified table identifiers are:

• MySchema.Portfolios
• "MySchema.Portfolios"
• MySchema.MyCatalog.dbo.Authors

And here are some examples of valid fully-qualified column identifiers are:

• MySchema.Portfolios.portfolioID
• "MySchema.Portfolios"."portfolioID"
• MySchema.MyCatalog.dbo.Authors.lastName

Fully-qualified identifiers can always be used in SQL commands. Partially- or unqualified forms can also be used, as long as the resulting names are unambiguous in the context of the command. Different forms of qualification can be mixed in the same query.

There are also some internal identifiers in Data Virtuality Server which have some restrictions in syntax rules:

• Data sources identifiers
• Virtual Schemas identifiers
• User and Role identifiers
• VDBs identifiers

The restrictions are as follows:

• Identifiers can consist of alphanumeric characters or the underscore (_) character. The dash and the dot character can be used only for user and role identifiers;
• Identifiers in double-quotes are prohibited.