on_error - full360/sneaql GitHub Wiki

Command: ON_ERROR

description:

error handling. if the previously executing tag raises an error, the action specified by this tag will be taken. if the previous command does not raise an error, the on_error command will be ignored, and the transform will exit.

parameters:

• action - continue, exit_step, execute

behavior:

if an error is raised by the previous command tag, the action parameter will specify how to handle the error:

• continue - error will be ignored and SneaQL will continue to run.
• exit_step - the current step will be exited (much like the exit_step_if command tag).
• execute - the SQL statement following the tag will be executed. note that this will only happen in the event of an error.

on_error will only handle errors from the previous command, in the same step. can not handle an error from the current step with an on_error command in the beginning of the next step.

if you are handling an error thrown by an iterate step, you can reference the fields in the record that raised the error using the syntax :last_iterated_record.field_name. by accessing this record, you can log the failure into your database.

examples:

/*-iterate rs-*/
--runs an update for each record in the rs recordset
update table1 
set value =':rs.foo' 
where id = :rs.id;

/*-on_error continue-*/ -- error will be ignored
--or
/*-on_error exit_step-*/ --step will be exited, next step will execute
--or
/*-on_error execute-*/
--logs the error
insert into failure_table values(:last_iterated_record.id, ':last_iterated_record.foo');
commit;

/*-execute-*/
--moving forward amicably
update aviary set bird='turkey';