How to get a count of record number in the same SQL sentence when we are inserting a data into a file in SQLRPGLE

user_13040808 0
Viewed 48

Recently in an interview I was asked , How to get the data count when inserting data into the file using an INSERT statement , I told we can use a Count statement later on to get the count , but he insisted can we get the count while executing the insert statement. I would really like to know can we get the data count when executing the insert statement in SQLRPGLE if yes then I would really like to know the answer.

rpg rpgle ibm-midrange
1 Answers

You have two options.

  1. SQL Communications area

SQLERRD(3)

For a CONNECT for status statement, SQLERRD(3) contains information about the connection status. See CONNECT (type 2) for more information. For INSERT, MERGE, UPDATE, REFRESH, and DELETE, shows the number of rows affected.

For a TRUNCATE statement, the value will be -1.

For a FETCH statement, SQLERRD(3) contains the number of rows fetched.

For the PREPARE statement, contains the estimated number of rows selected. If the number of rows is greater than 2 147 483 647, then 2 147 483 647 is returned.

usage

exec SQL insert ...;
recCnt = sqlErrD(3);
  1. GET DIAGNOSTICS statement

ROW_COUNT

Identifies the number of rows associated with the previous SQL statement that was executed. If the previous SQL statement is a DELETE, INSERT, REFRESH, or UPDATE statement, ROW_COUNT identifies the number of rows deleted, inserted, or updated by that statement, excluding rows affected by either triggers or referential integrity constraints. If the previous SQL statement is a MERGE statement, ROW_COUNT identifies the total number of rows deleted, inserted, and updated by that statement, excluding rows affected by either triggers or referential integrity constraints. If the previous SQL statement is a multiple-row-fetch, ROW_COUNT identifies the number of rows fetched. Otherwise, the value zero is returned.

usage

exec SQL insert ...;
exec SQL GET DIAGNOSTICS :recCnt = ROW_COUNT;
user_29331770
Related