In all cases, the failure of the WRITE statement causes program termination.
Varying Length Records
For varying length records:
If the FROM option is used, the length of the record is determined by the data definition of the field or group.
If the FROM option is not used, you can set the length of the record by setting the value of the system variable, SYSTEM.RECLENGTH before writing the record. If SYSTEM.RECLENGTH is 0 or is greater than the length of the record, then Jazz sets SYSTEM.RECLENGTH to the maximum length of the record.
For example, a file is defined with each record containing a maximum of 80 characters, and containing a group with a maximum of 60 characters.
DEFINE VREC TYPE(V)
DATA (L80_REC CHAR(80),
L60_REC VREC.L80_REC GROUP,
F1 CHAR(30),
F2 CHAR(30),
ENDGROUP);
To write a 60 character record:
WRITE(VREC) FROM(VREC.L60_REC.*);
To write a 44 character record:
SYSTEM.RECLENGTH = 44;
WRITE(VREC);
To write a maximum length record:
WRITE(VREC) FROM(L80_REC);
or
SYSTEM.RECLENGTH = 80;
WRITE(VREC);
OPTIONS
FROM (field-name | group-name)
Valid only for TYPE(V) and TYPE(VB) records.
Use to specify the length of a varying length record. The field or group must start in the first column of a defined record.
FUNCTION
Use to insert a new row or rows into a database table.
FORMAT
Format 1:
INSERT [INTO] table-name
[(insert-column-list)]
[VALUES(expression-list)]
;
Format 2:
INSERT [INTO] table-name
[(insert-column-list)]
[VALUES(expression-list) | query-specification]
;
insert-column-list::=
* | column-list
query-specification::=
SELECT [ALL | DISTINCT] select-list
FROM tablename [AS correlation-name] [,tablename [AS correlation-name]]...
[GROUP [BY] (field-reference [,field-reference]...)
[HAVING (condition)]]
[WHERE (condition)]
FUNCTION
Format 1: V12L40 only, AIM/RDB or RDB II only.
Format 2: V20L10 only, RDB II only.
You cannot use RDB or RDB II Set Functions within the INSERT statement.
If the INSERT statement is used within a MATCH or PROCESS block to access the same table, results may be unpredictable.
When used with an RDB II table, you can determine whether the INSERT executed successfully, by checking the value of SQLSTATE.
When used with an AIM/RDB table, you cannot use AQLCODE to determine whether the INSERT was successful. You must use the MATCH... UPDATE statement and then check the value of the MATCHED flag.
You must ensure that all columns with a NOTNULL attribute are assigned appropriate values.
OPTIONS
insert-column-list
Specifies the columns to be inserted. Each column must exist in the specified table, and must not be specified more than once. A column-list must include all columns that have a NOTNULL attribute.
You can use an asterisk to specify that all columns of the table are to be inserted. If the insert-column-list is omitted, Jazz generates a list of all columns that have been modified in the program.
Each column is updated to either:
The value specified in the VALUES option.
The value from the select list supplied by the query-specification.
The current (working storage) value if neither the VALUES option nor the query-specification is used.
If a column-list is specified, but a column is not included in the list, the column will be updated with NULL.
You must ensure that all columns with a NOTNULL attribute are assigned appropriate values.
query-specification
When a query-specification is used, one row of values for each row supplied by the query-specification is inserted into the table.
The rules for query-specification are the same as for a singleton SELECT (see "SELECT" statement), without the INTO option. A query specification may result in the selection of multiple rows.
The number of elements in the select-list of the query-specification must be equal to the number of columns specified in the insert-column-list.
The data types of elements in the select-list of the query-specification must be compatible with the data types specified in the insert-column-list.
When NULL is the result of a query-specification, the corresponding column must not be defined with the NOTNULL attribute.
Examples:
1. A select-list containing a column, a literal and an expression.
INSERT ORDERS(*)
SELECT PRODUCT.ID,'Y',10*PRODUCT.QTY
FROM PRODUCT
WHERE...;
2. A generic select-list.
INSERT ORDERS(*)
SELECT *
FROM ORDERS2;
VALUES(expression-list)
Specifies the values to be used for each column. Note that:
You must specify a single value for each column explicitly specified in the insert-column-list.
You must ensure that all columns with a NOTNULL attribute are assigned appropriate values (including any columns not specified in the insert-column-list).
A value may be either a literal, a variable, or an expression.
For example:
INSERT ORDERS (ORDER_NO, ITEM_ID, DATE_ORDERED)
VALUES (WS.LAST-ORDER + 1, 'I4809', WS.TODAY);
This statement inserts a row in the ORDERS table with column values taken from the VALUES option, in this case an expression, a literal and a variable.