You customize SQL access in the Segment -GG
Lines, on G type lines. On the
first line, you enter the access type that you want to customize. On
the following lines, you specify the parts of the access you want
to modify or add.
Procedure
- Enter the first line of the customized access type.
Open the -GG Lines subtab of a Segment.
Click Add and select G in
the Type field
Enter
SQL at
the beginning of the
Description field to indicate
that you want to customize an SQL access type. After a space, you
enter the access type you want to customize. The access type is standard
or nonstandard.
- Enter the following lines:
The SQL access
is described by clauses (such as ACCESS, WHERE, ORDER).
Each clause is described by a keyword and data that is related to
the keyword. From the second line onwards, you enter these keywords,
which apply to the customization of both standard and nonstandard
access types.
The corresponding data will replace the data that
would have been generated otherwise. If it must be added as a complement,
enter ADD before or after each keyword.
Select
a
G type line for the following lines. Enter
the keyword and the parameters that required for its operation in
the
Description field.
Notes: A keyword that is described on more than one line
is not to be repeated on each line.
The corresponding delimiter
is automatically generated at the end of each line. If a column name
and a host variable name exceed one line, add a V type
line and continue on the next line (a G type
line). In this case, and if you enter a column name and a host variable
name (with or without :-), you must code the
host variable on a new line.
You can enter the following
keywords with their associated parameters:
- ACCESS, followed by the SQL access type:
With
this keyword, you can rewrite the clause of an access type (such as SELECT, UPDATE, DELETE).
- COLUMN, followed by the list of columns:
These
columns can be coded either as SQL-Data Element (in
this case they are replaced upon generation by their relational name)
or directly under their relational name.
Each column can be
followed, after one blank at least, by the code of the host variable
(and indicator). This code is either :-Data Element (upon
generation, the Screen Segment that is defined on the -CS
Lines will be added before :-Data Element),
or the host variable name. If the host variable is not coded in an
access type where it is required, it will be generated with the Screen
Segment code and the Data Element indicated in the column (as SQL-Data
Element, otherwise the code of the host variable must
be indicated).
The parameters that are associated with this
keyword follow specific punctuation rules:
- If the column and host variable names exceed one line, you can
use one or more V lines but only for the host
variable name. The column name is entered on one line only.
- For the UPDATE statement (RW)
of the COLUMN keyword, the host variable and its
indicator must be coded on the same line whatever their input format.
- FROM, followed by the list of tables:
Upon
generation, this keyword is either transformed into INTO or
remains FROM according to the access type.
- ORDER, followed by the list of columns:
With
this keyword, you can indicate the ORDER BY.
The
column names can be coded as SQL-Data Element and
the host variables and indicators as :-Data Element.
- PARAM, followed by additional parameters:
With
this keyword, you can code additional parameters such as COUNT or HAVING.
The ADD keyword is not necessary.
The column
names can be coded as SQL-Data Element and
the host variables and indicators as :-Data Element.
- WHERE, followed by selection criteria:
The
column names can be coded as SQL-Data Element and
the host variables and indicators as :-Data Element.
Important: The generated format of the date
and variable Data Elements (such as CURDAT (current
date) as a parameter of the ORDER statement) varies
according to the Block type.
The option UPPER=NO,
entered on an O type line in the -GO
Lines of the instance that calls the Segment, inhibits
the lowercase uppercase conversion of the values that are entered
between two literal delimiters (the delimiter is defined in the Library).
The
syntax of SQL statements is not implemented on RDMS Blocks (R organization).
The prefixing rule does not apply. The table name is kept as is and
the period at the beginning is deleted if it exists.
Example
- Customization of a nonstandard select access type to select a
client number higher than or equal to the requested number.
You
modify the WHERE clause only but you do not want
to modify the standard access directly because this selection applies
to few Screens only.
Figure 4. Example of the customization
of a nonstandard select access: typeType Description
G SQL RB RA
G WHERE SQL-NOTJ >= :-NOTJ
- Example of the creation of a nonstandard access type to count
the number of records in the clients table.
In this case, it is
really a specific need. So you describe a new access type.
Figure 5. Example of the creation of a nonstandard access type:Type Description
G SQL CP
G ACCESS SELECT COUNT (*)
G INTO :WWCA-COMPTEUR
G FROM PTB0.TRAJET
Generating keywords is possible only for standard or similar
access types (CP, R). For a CP access
(which is not followed by a standard access type), you must also write
the keyword on the G line.