SQL File Types

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:

Naming Conventions for SQL Objects

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.

Common Parameters for SQL File Types

The following parameters are valid for all or most SQL file types.

SQL Attributes

SQL attributes

These parameters apply to all SQL views.

Select
A Select all: Redundant duplicates are not eliminated.
D Select distinct: Redundant duplicates are eliminated.
With check option
Y All inserts and updates to the view are checked against the view definition.

Additional attributes / Associations

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.

Subquery

Subquery

This option is available for all SQL views.

Check Expression

Check expression

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.

Field Lists of SQL Views

Field list

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.

Adding new Fields to Field Lists of SQL Views

New fields can easily be inserted into the field list of an SQL view using one the following two methods:

Manually

Enter parameter Field ID described above. See the section Naming Conventions for SQL Objects.

Import

Choose Import to import fields from other SQL tables or views into the current field list.

Editing the Subquery of an SQL View

Structure of a Subquery Clause

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, IV
    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 /*).