D3 SQL User's GuideWorking with SQL tables from existing D3 filesCreating SQL views and tablesUsing phrases

Using phrases

SQL-CREATE-VIEW can incorporate the AQL notion of phrases; that is, an item defined either in the dictionary of the file for which the phrase will be used or in the account’s master dictionary, where line one of the phrase definition is an H. In the case of SQL-CREATE-VIEW, line two of the phrase definition contains column definitions using the syntax described in the section SQL-CREATE-VIEW (Advanced Use).

Syntax

SQL-CREATE-VIEW file_reference phrase_definition [(options] 

   file_reference ::=
      file_name
      | file_name, data_level
      | account_name, file_name,
      | account_name, file_name, data_level 

phrase_definition :: = attribute-defining-item{=data_type} 

   nested_table ::=
      {|[ [’alternate_tablename’] column [column...] [nested_table] }|]

No option is specified when using a phrase definition with SQL-CREATE-VIEW. The program searches the account’s master dictionary for the specified phrase. If no phrase definition is found, a column definition is assumed.

 

  1. Customizing the SQL macro
    By default, SQL-CREATE-VIEW moves elements referenced by the D3 macro into the SQL macro (i.e., the list of columns used when the * is specified for the SQL column list). Additional columns can be added to the SQL macro by specifying those attributes at the TCL prompt after the file reference.
  2. Manual data typing
    To manually specify the SQL data type, each command line attribute argument can take the form attribute=datatype. No spaces are permitted around the = character.
  3. Mapping files containing MultiValue attributes
    By default, SQL-CREATE-VIEW forms nested tables based on controlling/dependent sets of attributes. Although this provides good results for simple files, there may be single attributes that are not part of a controlling/dependent set, but are MultiValue. Furthermore, there may be multi-subvalued sets that are not indicated in the dictionary. Therefore, except for the simplest dictionaries, it is best for the user to explicitly provide a command line specifying data types as well as MultiValue attributes when using SQL-CREATE-VIEW.
  4. Pseudo columns
    In the table, INVENTORY_MODEL_COLOR, a column named INVENTORY_SVC is included. This column is not an ADI in the underlying D3 file and its data is not stored in the file. These columns are called pseudo columns because they are generated on-the-fly for nested tables to indicate each row’s value or subvalue count. These columns are incorporated into the primary key for the nested table, allowing each row to be uniquely identified.
  5. Querying files containing MultiValue attributes
    Once a D3 file containing MultiValue attributes has been flattened, the result is several SQL tables, such as in the INVENTORY example above. To retrieve the desired data, nested tables must be joined in the WHERE clause of the SQL SELECT statement.
  6. Accessing data through OSFI
    D3 SQL can be used to access both non-local and non-D3 data through the OSFI (Open System File Interface). To reference OSFI files using D3 SQL, a local SQL catalog structure reflecting the remote files must exist.

Related topics
Creating SQL views and tables
Basic use of the commands
Advanced use of the commands
Creating SQL tables