Hi, I have ETL process which using the procedure file to bui...

[Manoj Chaurasia]

Hi Thirumaran

You can right mouse click in the canvas and choose Add E-Mail object and then draw a connector to it which will be a red connector and handle the failure of the procedure.

 

 

image.png779546 14.7 KB

 

Hope that helps!

[Manoj Chaurasia]

Chuck,

Thanks for your suggestion.

The condition checks only the last &FOCERRNUM in the procedure.

Ill explain more about this with the below sample.

I have included 2 files inside the main procedure like below.

-*Main procedure

-*-INCLUDE file_1

TABLE FILE CAR

PRINT COUNTRY

BY CAR

ON TABLE HOLD AS HOLD_1

END

-RUN

-*-INCLUDE file_2

TABLE FILE CAR

PRINT MODEL

BY CAR

ON TABLE HOLD AS HOLD_2

END

-RUN

Im getting errors in the first Hold file and the FOCERRNUM was 3. the process should be stopped after this error.

But it continues with the second Include file and creating the HOLD_2 correctly. so now the FOCERRNUM is 0.

now if you check the &FOCERRNUM in the condition editor, its considering FOCERRNUM as 0 (success) and runs the next step.

The main procedure is executing all the codes even if it gets an error in the middle of the procedure, and considering the last FOCERRNUM as the error code. But Im not fully sure about this. This is what Im getting in my testing

[Martin Yergeau]

You should test your FOCERRNUM after each step

Have you tried with something such as this :

-*Main procedure

-*-INCLUDE file_1

TABLE FILE CAR

PRINT COUNTRY

BY CAR

ON TABLE HOLD AS HOLD_1

END

-RUN

-IF &FOCERRNUM NE 0 THEN GOTO XERROR;

-*-INCLUDE file_2

TABLE FILE CAR

PRINT MODEL

BY CAR

ON TABLE HOLD AS HOLD_2

END

-RUN

-IF &FOCERRNUM EQ 0 THEN GOTO XEXIT;

-XERROR

-TYPE I Got An Error Stop Process

-XEXIT

[Manoj Chaurasia]

That was going to be my suggestion as well.

[Manoj Chaurasia]

Hi,

I have ETL process which using the procedure file to build some data.

Inside the procedure file, Im INCLUDEing 5 other procedures to build the require HOLD file.

If I get any error in the 1st Hold file, the ETL process not stopping the process flow. It continues with the next hold file process(INCLUDE 2) and at the end of the procedure, it returns the &FOCERRNUM = 0. So if I connect any flow/step after this hold process step, the connector checks the &FOCERRNUM and it moves to the next step since the &FOCERRNUM =0.

But in actuality, we got an error inside the procedure and the process flow should be stopped once we got the error(like in WebFOCUS reports).

How to handle this kind of issue in ETL

See the below sample:

Inside the build_hold_file step, I have included 5 procedures. In the first Include, Im getting errors. But the process flow is not stopped and it process continues next step load_hold_file_data.

I want to stop the process immediately if get any error inside the procedure(like the WebFOCUS report will do).

[Manoj Chaurasia]

Thanks Martin,

Your solution worked well!!!