SQL_ON_ROWCOUNT_ZERO
Use the SQL_ON_ROWCOUNT_ZERO script element to determine the return code if an SQL statement does not supply a hit. You cancel a job with a defined return code if an SQL statement such as SELECT or UPDATE does not return any hits. The specification made with SQL_ON_ROWCOUNT_ZERO applies for all subsequent SQL statements until the job ends or until the next SQL_ON_ROWCOUNT_ZERO statement.
Important!
-
SQL_ON_ROWCOUNT_ZERO can only be used for SELECT, UPDATE, INSERT and DELETE. Other SQL statements such as CREATE TABLE never supply hits. Using lSQL_ON_ROWCOUNT_ZERO in a job always requires RETCODE being set to 0 before the particular SQL statements are used.
-
All SQL statements until the last COMMIT are undone if the job is canceled.
Syntax
SQL_ON_ROWCOUNT_ZERO
RETCODE=...
Parameters
-
SQL_ON_ROWCOUNT_ZERO
Determines the return code if an SQL statement does not supply a hit -
RETCODE=
Return code
Default value: 1
If you define the value 0, the number of hits that are found in SQL statements are ignored and the job continues.
Examples
The job is canceled and returns code 1 if one of the three SQL statements returns 0 hits.
SQL_ON_ROWCOUNT_ZERO RETCODE=1;
select * from person;
insert into person values (1,'Smith');
update person set address = '403 E. Main Street'
where name = 'Spencer';
See also: