Each Metadata Definition tab has up to three Metadata Table Definition SQL entry fields:
Select—Generates SQL Select statements, and is divided into distinct fields which specify the columns that store the metadata. The columns are located in the database table described in the From field. If necessary, you can use aliases in the Select fields to distinguish between multiple tables.
From—Generates an SQL From clause, and specify the table(s) that contains metadata that applies to the database item described by the tab. You can also enter SQL to access system tables when necessary. If you need to reference more than one table in the From field, you can use table aliases in the SQL.
Where—Generates SQL Where clauses and is used on the Columns and Joins pages to indicate which topic needs to be populated with item names or joined to another topic. It can also be used to establish relationships between multiple tables or filter tables.
Entries are required in all From entry fields, and in all fields marked with an asterisk (*).
Under default settings, Metadata Definition fields specify the system-managed directory tables (except when using ODBC). You cannot modify field values when the Default radio button is selected.
:TABLE and :Column should not show quotes in the metadata set up. SQL adds the quotes automatically.
Clicking Reset at any time when defining a custom source populates the entry fields with the database default values. It may be helpful to start with the defaults when setting up metadata definitions.
You may sometimes use database variables when entering a Where clause. Interactive Reporting Studio provides :OWNER, :TABLE, :COLUMN, :LOOKUPID, :TABALIAS, and :COLALIAS variables which temporarily store a database owner, table, column, or domain ID number and aliases of the active topic or item. Each variable must be entered in all caps with a leading colon.