Customizing SQL accesses in Segments

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

  1. 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 nonstandard.
    • The standard access types are automatically implemented in the instances (Programs, Screens, or Servers) which call the Segment. The following access types exist:
      • R: Select
      • RU: Select for Update
      • W: Insert
      • RW: Update
      • D: Delete
      • P: Open Cursor
      • RA: Select (Pacbench C/S)
      • RN: Fetch
      • DC: Declare Cursor
      • CL: Close Cursor
      Note: The DC (Declare Cursor) standard access is not managed for SQL accesses where DC is generated in the WORKING-STORAGE SECTION.
      Figure 1. Example of the customization of the standard select access:
      Type   Description 
      G      SQL R
    • A nonstandard access must be coded on one or two characters. It must be explicitly implemented in the instances which call this Segment. This access can be:
      • Combined with a standard access if you want to modify only part of the standard access and leave the generator to manage the syntax. In this case you must enter the code of the standard access first and, after a blank, the code of the nonstandard access.
      • Used alone if you want to create an access. In this case you must describe this access entirely and implement it in specific code. You must manage the syntax (such as the punctuation or keywords).
      Figure 2. Example of the customization of a nonstandard select access:
      Type   Description 
      G      SQL R1 RA
      Figure 3. Example of the creation of a nonstandard access which counts the number of records in the clients table.
      Type   Description 
      G      SQL CP
  2. 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 nonstandard 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


Feedback