Predict offers various file types for documenting tables and views of the SQL systems listed below. The file objects which document the SQL tables and views can be used to generate SQL CREATE statements, DDMs and copy code members for 3GLs. The CREATE statements are stored as Natural members in file FDIC.
This document covers the following topics:
Special naming conventions apply to the following objects in Predict
SQL file types. See table below.
Fields linked as children to these file types
Constraint names
Correlation names
Tablespace for Oracle
The file IDs must be fully qualified. A fully qualified ID consists of three parts:
Hyphen to separate creator/schema from table/view name
Table/view name. The maximum length depends on the SQL system. See table below.
Fully qualified IDs may not exceed 32 characters.
The permitted characters listed in the table below apply to creator/schema and table/view name.
| Filetype | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| AT,B, A(SQL) | BT, BV | D, E, IV, IT | JT, JV | OT, OV | X | XT, XV | YT, YV | ||
| Convention | Maximum length of table/view name | 32 | 18 | 18 | 24 | 30 | 18 | 18 | 30 |
| Upper case | Y | Y | Y | ||||||
| Upper/lower case | Y | Y | Y | Y | Y | ||||
| '_' allowed at first pos. | Y | Y | Y | ||||||
| '#' allowed at first pos. | Y | Y | |||||||
| '$' allowed at first pos. | Y | Y | |||||||
| '@' allowed at first pos. | Y | Y | |||||||
| '_' allowed from second pos. | Y | Y | Y | Y | Y | Y | Y | Y | |
| '#' allowed from second pos. | Y | Y | Y | Y | Y | Y | |||
| '$' allowed from second pos. | Y | Y | Y | Y | Y | Y | |||
| '@' allowed from sec. pos. | Y | Y | Y | Y | |||||
| Numbers allowed from second pos. | Y | Y | Y | Y | Y | Y | Y | Y | |
Type-specific rules are also given in the respective parts of this section.
The following parameters are valid for all or most SQL file types.
These parameters apply to all SQL views.
| Select |
|
||||
| With check option |
|
Profile options are described in the section Defaults in the Predict Administration documentation. The editors are described in the section Editors in Predict in the Predict Reference documentation.
This option is available for all SQL views.
This option is available for the following SQL tables:
Adabas D
DB2
Oracle
Informix
Ingres
Sybase
It is also available for the following file type:
General SQL file
No special checks are performed when check expression is saved.
| Column | Meaning |
|---|---|
| Ty | Field type. |
| L | Field level. |
| Field ID | ID of field object documenting the SQL view. The ID of the field object in Predict documenting a field in a view can differ from the name of the field in the original table or view. |
New fields can easily be inserted into the field list of an SQL view using one the following two methods:
Enter parameter Field ID described above. See the section Naming Conventions for SQL Objects.
Choose Import to import fields from other SQL tables or views into the current field list.
The following rules apply:
In the first part of the subquery clause, the related master files and their correlation names can be specified in SQL syntax.
The file type of the related master files must be compatible with the file type:
| File Type of View | Related Master File Type |
|---|---|
| B | A(SQL), AT, B |
| BV | BT, BV |
| E, IV | D, E, IV |
| JV | JT, JV |
| OV | OT, OV |
| XV | XT, XV |
| YV | YT, YV |
Any correlation name that is specified must be used whenever the file is referred to. Type-dependent rules apply to the length of a correlation name and the characters permitted. See table in the section Naming Conventions for SQL Objects.
The first part of the subquery is generated automatically if the fields of the file are defined in Predict before the subquery is edited.
The second part of the subquery contains the selection criteria of the view: the WHERE clause, GROUP BY clause or HAVING clause or any combination of these. The name of each field referenced in the selection criteria must be qualified by the ID of the file from which the field is taken or - if a correlation name has been specified in the first part of the subquery - by the correlation name.
If joined views are edited, the selection criteria and the type of join are displayed for each join.
If union views are edited, placeholders are used instead of field selection lists, because field selection lists are maintained with the field list editor. The placeholder indicates where the selected field list will be added when generating the view. Placeholders are displayed in angle brackets.
When generating a CREATE VIEW statement for a view, hyphens (-) are replaced by underscores (_) or points (.).
The subquery can include comment lines (with /*, * or ** in the first two columns) and line comments (preceded by /*).
