D3 SQL User's GuideWorking with SQL tables from existing D3 filesCreating SQL views and tablesBasic use of the commands

Basic use of the commands

SQL-CREATE-VIEW is a TCL command (not a SQL statement) that creates one or more read-only SQL tables from an existing D3 file. By default, the column descriptors of the SQL table are based on the attribute-defining items found in the dictionary of the file, the same file from which the data is found. As in AQL, the USING clause can be employed to specify a different file to use as the dictionary when creating the column descriptors.

Syntax

SQL-CREATE-VIEW file_reference [USING [dict] file_reference][(options]
   file_reference ::=
      file_name
      | file_name, data_level
      | account_name, file_name,
      | account_name, file_name, data_level 

SQL-CREATE-TABLE file_reference [USING [dict] file_reference][(options]
   file_reference ::=
      file_name
      | file_name, data_level
      | account_name, file_name,
      | account_name, file_name, data_level

Parameters

Parameter Description
account_name The D3 account. Not required if the specified D3 file is in the current account.
file_name The D3 file name. If data_level is specified, this will be the dictionary name.
data_level Not required if the specified file (or dictionary) has the same name as the data level.
options a Include all base (a) attributes in the table. By default, only the attributes in the d-pointer macro are included.
b Backward compatibility for D3 7.1 and above. Inhibits the creation of pseudo primary keys on nested tables, making them compatible with older D3 servers.
o Overwrite existing table definitions.
p Output to printer. This option should be used with the z option to produce a report detailing the structure of the table created.
s Include all synonym (s) attributes in the table.
x Include all protected (x) attributes in the table.
z Verbose output. For each table created, this option displays any primary and nested keys created as well as a list of columns involved. For each column, the program displays the original attribute name, the SQL column name, the SQL data type, the column number, the column precision, and the correlative.
Note: Additional options are available using the advanced syntax. See Advanced use of the commands.
  1. Naming convention differences
    The table name and column names in the SQL table you create may be different from those of the originating D3 file and attribute-defining items (ADI). This is due to SQL naming conventions.
  2. Attribute mapping
    By default, SQL-CREATE-VIEW creates a column descriptor for each base attribute definition in the d-pointer’s macro. Typically, the attribute-defining items are found in the dictionary of the file containing the data for the SQL table.
  3. File mapping
    When processing a file with controlling/dependent sets, SQL-CREATE-VIEW creates multiple tables for a single D3 file, which are called a nested set of tables.
  4. Data typing
    Unlike D3, SQL requires that each column has a predefined data type. For D3 SQL, the possible data types are CHAR, VARCHAR, INTEGER, SMALLINT, NUMERIC, DATE, TIME, and TIMESTAMP.
  5. Precision and scale
    In addition to selecting a data type for each column descriptor, a precision, and for numeric data, a scale, is selected.
  6. Correlatives
    Most D3 correlatives are supported in D3 SQL. Unsupported D3 correlatives include a-correlatives using the n operator and f correlatives using branching. MultiValue a and f correlatives are also not supported.

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