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

Creating SQL views and tables

SQL-CREATE-VIEW creates SQL tables that are read-only, called VIEW tables, while SQL-CREATE-TABLE creates one or more SQL tables from an existing D3 file that are read/write, called BASE tables. Thus, the underlying D3 file can be modified through SQL statements. Existing items can be modified or deleted and new items can be inserted.

The SQL-CREATE-VIEW/TABLE commands create the SQL tables needed to access flat attributes as well as MultiValue attributes contained in controlling/dependent sets. Each controlling/dependent set becomes a nested SQL table. The advanced syntax for these commands is useful if MultiValue or multi-subvalue relationships exist in a D3 file and these relationships are not fully defined by controlling/dependent dictionary structures. The advanced syntax allows you to define these relationships at the TCL command line.

These TCL commands share identical syntax. Both a basic and a more advanced syntax are described.

  1. 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.
  2. Advanced use of the commands
    SQL-CREATE-VIEW can become more powerful using the advanced syntax. You can specify the columns in the SQL table, the data types, the precision and scale.
  3. 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).
  4. Creating SQL tables
    These features are only supported for SQL-CREATE-TABLE.

Related topics
Working with SQL tables from existing D3 files
Displaying SQL tables
Deleting SQL tables