The FOR NEW statement inserts new rows in an SQL object using a dataview defined for the object. The FOR NEW statement is not iterative. To repeat processing of a FOR NEW, you must include it in a looping construct.
This statement has the following format:
<<label>>
FOR [THE] NEW dataview_name
statements
[ WHEN DUPLICATE ]
[ statements ]
[ WHEN ERROR ]
[ statements ]
ENDFOR
An optional 1‑ to 15‑character name of the FOR NEW construct. You can use it to refer to the construct in the QUIT statement.
Inserts a new row. You can add the reserved word THE for readability.
FOR NEW initializes the column values in the new row if the program does not initialize them. The column values are initialized to NULL for fields that can have the null value, or to:
If initial values were specified in the dataview definition, these values initialize the field.
It is recommended that the views you use in FOR NEW include all columns in the underlying table that were defined as NOT NULL. Otherwise, any insert using this view fails because the missing columns cannot be supplied with initial values.
The name of the dataview for which a new row is inserted. The underlying SQL object must be insertable.
Do not qualify the dataview name with an authorization ID.
The WHEN DUPLICATE clause contains statements that are executed when the value of an index column to add matches the value of an index existing in the database and when the database does not allow duplicate index values. The criteria for invalid duplicates are defined by the site database administrator when the database is defined.
If you omit the WHEN DUPLICATE clause and duplication is not allowed, control passes to the WHEN ERROR statement if a duplicate is found. If the WHEN ERROR is not coded, control passes to the error procedure.
If duplication is allowed, the WHEN DUPLICATE clause is ignored.
Note: Although the file is not updated when a duplicate record is found (the duplicate record is not added), the WHEN DUPLICATE clause does not affect the execution of the statements that precede it. The statements in the WHEN DUPLICATE clause execute when the duplication is detected at the ENDFOR. At that point, all other statements in the scope of the FOR were already executed. If the FOR construct includes statements that increment counters or set messages, you can correct those values in the WHEN DUPLICATE processing. However, you cannot continue executing the FOR construct.
Specifies statements to execute when a database error is encountered in the scope of the FOR construct. If the WHEN ERROR is not specified, errors are processed by the user‑defined or default error procedure.
The statements specified following a WHEN ERROR clause can access $ERROR and $SQL functions and should resolve the error with either a PROCESS NEXT or DO ERROR statement. If processing falls through to the ENDFOR, the $ERROR and $SQL functions are no longer available.
Note: Only database errors are handled by the WHEN ERROR clause. System and internal errors are handled by the user‑specified or default error procedure.
A reserved word that terminates the FOR construct. If FOR constructs are nested, the most recent unterminated FOR construct is terminated. You can reference any row added in the FOR NEW after ENDFOR unless a QUIT statement is used.
You can reference columns processed in the FOR construct in PDL statements using the column name and, if necessary, the dataview name as qualifier. Typically, the statements in the scope of the FOR NEW construct place values into the newly created row (see list of examples in this section)
Insertion of a new row into the table or view occurs at the ENDFOR.
Any update of a column value in the scope of a FOR virtually updates the database. The update to the actual table or view takes place at the ENDFOR for the current iteration. Therefore, any QUIT in the scope of a FOR NEW abandons the insertion of the new row. Further reference to that row outside of the FOR is invalid.
You cannot delete a row in the logical scope of a FOR NEW construct.
You cannot reference column values for a row before the FOR is executed.
A FOR NEW construct can contain TRANSMIT, COMMIT, BACKOUT, and so on statements.
SQL errors and warnings, resulting from the execution of either embedded SQL statements or the SQL that CA Ideal generates for a FOR construct are available in the SQL communications area, SQLCA. SQLCA fields can be tested for warnings or errors using:
For more information about SQLCA, see the $SQL Functions (SQL Access Only) in the "Symbolic Debugger Commands" chapter.
If inserting a new row causes the database management system to issue an abnormal error, the WHEN ERROR statements execute. If no WHEN ERROR statement is coded, the error procedure gets control at ENDFOR.
Examples
In this example, one row is added to the INVEN_ITEM table with column values of a numeric literal, an alphanumeric literal, a working data item, and a panel field.
FOR THE NEW INVEN_ITEM
MOVE 1915464 TO CODE
MOVE 'WIDGETS' TO DESC
MOVE WORK.COST TO INVEN_ITEM.COST
MOVE PNL_ASK.QTY TO INVEN_ITEM.QTY
ENDFOR
In the following example, the FOR NEW construct is included in a LOOP construct to process multiple records. Notice that a WHEN DUPLICATE clause is specified to correct the NEW_COUNT total when the duplicate record was not added.
LOOP UNTIL TRANSCODE = 'Q'
TRANSMIT INVEN_PNL
FOR THE NEW INVEN_ITEM
MOVE INVEN_PNL TO INVEN_ITEM BY NAME
SET NEW_COUNT = NEW_COUNT + 1
WHEN DUPLICATE
SET NEW_COUNT = NEW_COUNT ‑ 1
ENDFOR
ENDLOOP
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|