You customize SQL access on the Segment -GG lines, on G type
lines. On the first line, you enter the access 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.
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. After a space, you enter
the access type you want to customize. The access type is standard
or non-standard.
- 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 related to the keyword.
From the second line onwards, you enter these keywords, which apply
to the customization of both standard and non-standard accesses.
The
corresponding data will replace the data which 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
required for its operation in the
Description field.
Notes: A keyword 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:
With
this keyword, you can rewrite the clause of an access (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 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 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 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 the CURDAT (current
date) column entered 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 which calls the Segment, inhibits the lowercase uppercase
conversion of the values entered between two literal delimiters (the
delimiter is defined at the Library level).
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 located at the beginning is deleted if it exists.
Example
- Customization of a non-standard select access 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 non-standard
select access:Type Description
G SQL RB RA
G WHERE SQL-NOTJ >= :-NOTJ
- Example of the creation of a non-standard access 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.
Figure 5. Example
of the creation of a non-standard access: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
accesses (CP, R). For a CP access
(which is not followed by a standard access), you must also write
the keyword on the G line.