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.
The D3 file containing the data for the SQL table is the first file specified after the SQL-CREATE-VIEW command. For example, assume that the dictionary of a D3 file, CUST, has 5 attribute-defining items, CUST.ID, CUST.NAME, ADDRESS, PHONE and ZIP.CODE as defined in the table below:
| CUST.ID | CUST.NAME | ADDRESS | PHONE | ZIP.CODE | |
| Dictionary-code | a | s | a | a | a |
| Attribute-count | 0 | 0 | 1 | 2 | 3 |
| Correlative | G0*1 | ||||
| Attribute-type | L | L | L | L | L |
| Column-width | 25 | 20 | 50 | 12 | 5 |
Now, assume that the d-pointer’s macro for the CUST file is CUST.ID PHONE. The command SQL-CREATE-VIEW CUST (z would produce these results:
| TABLE ID: | 000123 |
| TABLE NAME: | cust |
| TABLE TYPE: | VIEW |
| PRIMARY KEY: | cust_id |
| MACRO: | cust_id phone |
| ATTRIBUTE | COLUMN-ID | DATA-TYPE | COL# | SIZE | CORRELATIVE |
| cust.id | cust_id | VARCHAR | 0 | 25 | |
| phone | phone | VARCHAR | 2 | 12 |
If no d-pointer macro exists, all base attributes in the dictionary are included in the table. Continuing with the example of the CUST file, assume that the CUST file has no macro defined in the d-ptr. The command SQL-CREATE-VIEW CUST (oz would produce the following results:
| TABLE ID: | 000123 |
| TABLE NAME: | cust |
| TABLE TYPE: | VIEW |
| PRIMARY KEY: | cust_id |
| MACRO: | cust_id address phone zip_code |
| ATTRIBUTE | COLUMN-ID | DATA-TYPE | COL# | SIZE | CORRELATIVE |
| cust.id | cust_id | VARCHAR | 0 | 25 | |
| address | address | VARCHAR | 1 | 50 | |
| phone | phone | VARCHAR | 2 | 12 | |
| zip.code | zip_code | VARCHAR | 3 | 5 |
The attribute-defining item CUST.NAME is not included in the SQL table because it is defined as a synonym attribute. To include synonym attributes, as well as base attributes, use the s option. To include protected attributes, as well as base attributes, use the x option. To include all attribute-defining items, use both the s and x options. For example, SQL-CREATE-VIEW CUST (sxoz would produce the following results:
| TABLE ID: | 000123 |
| TABLE NAME: | cust |
| TABLE TYPE: | VIEW |
| PRIMARY KEY: | cust_id |
| MACRO: | cust_id cust_name address phone zip_code |
| ATTRIBUTE | COLUMN-ID | DATA-TYPE | COL# | SIZE | CORRELATIVE |
| cust.id | cust_id | VARCHAR | 0 | 25 | |
| cust.name | cust_name | VARCHAR | 0 | 20 | g0*1 |
| address | address | VARCHAR | 1 | 50 | |
| phone | phone | VARCHAR | 2 | 12 | |
| zip.code | zip_code | INTEGER | 3 | 5 |
If the USING clause is employed, then the attribute-defining items are found in a different file from that containing the data for the SQL table. For example, assume that the dictionary of the file, CUST.DICT, contains 5 attribute-defining items, namely, CUST.ID, STREET, ZIP, CITY, and STATE, as defined below:
| CUST.ID | STREET | ZIP | CITY | STATE | |
| Dictionary-code | a | a | a | s | s |
| Attribute-count | 0 | 1 | 3 | 3 | 3 |
| Correlative | tGET.LOC;x;1;1 | tGET.LOC;x;1;2 | |||
| Attribute-type | L | L | L | L | L |
| Column-width | 25 | 50 | 5 | 20 | 2 |
Now assume that the d-pointer’s macro for the CUST file is CUST.ID STREET CITY STATE ZIP.
The command SQL-CREATE-VIEW CUST USING dict CUST.DICT (soz produces this SQL table:
| TABLE ID: | 000123 |
| TABLE NAME: | cust |
| TABLE TYPE: | VIEW |
| PRIMARY KEY: | cust_id |
| MACRO: | cust_id street city state zip |
| ATTRIBUTE | COLUMN-ID | DATA-TYPE | COL# | SIZE | CORRELATIVE |
| cust.id | cust_id | VARCHAR | 0 | 25 | |
| street | street | VARCHAR | 1 | 50 | |
| city | city | VARCHAR | 3 | 20 | TGETLOC;x;1;1 |
| state | state | VARCHAR | 3 | 2 | TGETLOC;x;1;2 |
| zip | zip | INTEGER | 3 | 5 |