Previous Topic: OUTPUT ParameterNext Topic: Printing Parameter Lists

Accessing SQL Defined Tables › EDIT Parameter

EDIT Parameter

More information:

Edit Parameters— Overview

Purpose

For details-only tables, each type 5 detail parameter defines either an SQL column on the table, or the null indicator for the previously defined SQL column. For totals-only tables, the SQL column definitions and the corresponding null indicators are defined using type 6 total parameters.

Syntax

      Col
      2
      ▼
►►─── rrt1*nn ─── field-name ─── data-specification ──────────────────────────►

 ►───┬────────────┬───┬────────────────┬──────────────────────────────────────►◄
     └─ NOT NULL ─┘   └─ WITH DEFAULT ─┘

Syntax Rules

rrt1*nnn

Defines an edit parameter for report number rr.

The parameter type t is 5 for details-only reports and 6 for totals-only reports. Line 1 is always used when writing to SQL tables.

The info-column technique (*nnn) specifies the relative position of this data in the output buffer. CA Culprit automatically calculates the exact output position.

field-name

The field-name used on the edit parameter is used to generate SQL Create Table syntax. This field name becomes the new SQL column name. Therefore, field-names (not literals) must always be used on edit parameters when TYPE=CREATE.

data-specification

Data-specification includes the CA Culprit format code, the SZ= clause, and the DP= clause. Together, this information is used to generate the data type clause of an SQL column definition. The correspondence between the CA Culprit data specification and the CA IDMS/DB data type is shown in the following table:

CA Culprit data spec

CA IDMS/DB data type

SZ=15

CHAR(15)

FB SZ=2

SMALLINT

FB SZ=4

INTEGER

FB SZ=8

LONGINT

FP SZ=3

DECIMAL(5)

FP SZ=5 DP=2

DECIMAL(9,2)

FU

REAL

FW

DOUBLE PRECISION

FZ SZ=4

NUMERIC(4)

FZ SZ=7 DP=2

NUMERIC(7,2)

Use of the format codes FC, FD, FF, FM, FN, FS, F-, F$, F*, and F0 through F9 all produce SQL columns of data type CHAR. Data written to the SQL table is edited exactly as requested, but it is considered alphanumeric data. For most applications, this is not desirable.

CA Culprit does not currently support all of the CA IDMS/DB data types. Therefore, CA Culprit cannot create SQL tables that contain these SQL data types:

NOT NULL

NOT NULL indicates that this SQL column cannot contain null values. NOT NULL is only valid when TYPE=CREATE has been specified on the OUTPUT parameter. If NOT NULL is omitted when defining an SQL column, then a null indicator field must be specified on the subsequent edit parameter.

0151*010  PRODUCT_CODE            SZ=4  NOT NULL
0151*020  UNIT_SALES           FP SZ=5
0151*021  UNIT_SALES_NULL_IND  FB SZ=4
WITH DEFAULT

WITH DEFAULT clause is only valid when NOT NULL has already been coded and TYPE=CREATE. This clause has no effect on batch data insertion. However, it simplifies online data insertion by allowing a default value to be inserted if this column is omitted from the SQL Insert statement.