ISQL and Tools
•start_position is the position where the field starts. It must be an unsigned integer.
•end_position is the position where the field ends. It must be an unsigned integer. The first position of each record is 1 and not 0.
If date and time types are to be inserted they can be specified as characters in the data file. If it is a fixed length record then the type specification can be CHAR.
The following is an example of the DEFINE RECORD statement for fixed length records:
DEFINE RECORD rec_one OF FIXED LENGTH 20 AS (
fld1 POSITION (1:4) SHORT, fld2 POSITION (5:15) CHAR, fld3 POSITION (16:20) CHAR
) ;
4.5.2The FOR EACH Statement
The FOR EACH statement scans for each valid record in the data file and inserts the record into the database. The syntax for the FOR EACH statement is shown below:
FOR EACH RECORD record_name FROM data_file_name, ...
INSERT INTO owner_name.target_table [ (field_name, ...) ]
VALUES (value, ...) ;
NEXT RECORD
The following are the variable descriptions of the FOR EACH statement:
•record_name is the record name that is specified in the DEFINE RECORD statement.
•data_file_name is the name of the input data file name.
•owner_name.target_table is the target table name identified along with the owner name of the table. The target_table must already exist in the database and must have appropriate permissions for inserting the records.
•field_name is the name of the field or column in the table.
•value is the value that must be inserted into the table.
The target_table can also be a synonym on another table with the INSERT access. The list of values that are to be inserted must follow the VALUES keyword. The values that can be inserted are:
•Name of the field in the input data file
•A constant (both numeric as well as character)
•NULL
The values specified in the VALUES list must correspond one to one with that in the target table list. The list can be in any order compared to the list specified in the DEFINE RECORD
FairCom Corporation |