OK, so I have a focexec that preps key values prior to my data tranforms running. I had a situation where a select in the focexec did not function correctly. That is, a select apparently returned zero rows for whatever reason without issuing a focerror, so the key values were not updated, and the flow continued with incorrect values.

So here's what I want to do. I want to stop the run if zero records are returned from this select. Will a simple -EXIT end the entire flow, or will it just end that focexec? Is there a way for me to manually throw an error in the event &LINES EQ 0?

J.

This message has been edited. Last edited by: John_Edwards,

John,

Need to add, after each step:

-RUN
-IF &LINES EQ 0 GOTO NO_RECORDS;

If &LINES NE 0, then, just continue on.

Now, at the bottom of the program, you need to add the following:

-* THIS IS WHERE THE SUCCESSFUL PROCESSING ENDS - &LINES GT 0
END
-RUN
-GOTO EOJ

-NO_RECORDS
-* If you wamt to set a flag, or, write something to a file, do it here:
-WRITE DDNAME ZERO records for Step &STEP_NUM -* something like this

-EOJ
-EXIT


hth

Yeah, but how do I end the run? I want to throw an exception so the flow control bails out instead of continuing. Can I hand-set the value of &focerrornum and will that take precedence over the "Return Code" mentioned in the conditions? Is the &focerrornum the return code?

J.

John,

TABLE/SELECT/MATCH use &LINES / &RECORDS
MAINTAIN/MODIFY use &FOCERRNUM
-READ use &RETCODE or &IORETURN

If MAINTAIN/MODIFY, then, if I remember correctly, you have many & variables available:

&ACCEPTS
&CHNGD
&DUPLS
&INPUT
&INVALID
&NOMATCH

etc...

Run this:

 
APP PREPENDPATH IBISAMP
-RUN
-SET &ECHO=ALL;
TABLE FILE CAR
BY COUNTRY
-*  WHERE COUNTRY EQ 'XXXXX';
  ON TABLE HOLD AS HOLD1
END
-RUN

-IF &LINES EQ 0 GOTO NO_RECORD;

MODIFY FILE CAR
FIXFORM FROM HOLD1 
MATCH COUNTRY
  ON MATCH COMPUTE CAR='TRIUMPH';
MATCH CAR
  ON NOMATCH TYPE "NOT THERE"
  ON MATCH GOTO TYPEIT

CASE TYPEIT
  TYPE "GOT <D.CAR"
ENDCASE

DATA ON HOLD1
END
-RUN
-TYPE &READS &NOMATCH &INPUT &CHNGD

-IF &NOMATCH GT 0 GOTO REJ_RECORD;

-GOTO EOJ

-NO_RECORD
-HTMLFORM BEGIN
</br>
[b]There are <FONT color="RED">ZERO</FONT> records generated for the parameter selection(s) </br>
    you have made for this Report. </br>
Please re-submit the report with a different set of parameter selection(s)! </br>
</br>
Thank you.</br> </br> [/b]
-HTMLFORM END

-GOTO EOJ

-REJ_RECORD
-HTMLFORM BEGIN
</br>
[b]There are <FONT color="RED">!IBI.AMP.NOMATCH; UnMatched</FONT> records generated for the parameter selection(s) </br>
    you have made for this Report. </br>
Please re-submit the report with a different set of parameter selection(s)! </br>
</br>
Thank you.</br> </br> [/b]
-HTMLFORM END

-EOJ
-? &
-EXIT

It will generate 5 errors for 5 records; right-click and view source. All those & variable are available.

Then, UNCOMMENT the WHERE in the TABLE, there will be ZERO records...

hth

I need to throw an error on the I-Way server that the process flow will recognize. Within the process doesn't really matter to me -- I already actively manage the issue within the focexec. I need this focexec to APPEAR to the controlling process flow to be in error, so that the process flow branches appropriately. Here's the question --

In the event I choose to, how do I manually indicate to the process flow that an error has occurred in one of its stored procedures and should branch accordingly?

I've dug around a bit and looked at the process flow's source code and figured out that I can set &&KILL_RPC to 'Y' and that will drop things pretty doggone quickly. I can also see where &&CM_RETURN seems to be key to the process as well, but . I can't imagine this is best practice, but until I hear otherwise I'm going to use kill_rpc to prevent more damage from being done to my production data tables.

J.

Well, same concept:

  
-*------------------------------------------------------------------------
-* Call Stored Procedure
-*------------------------------------------------------------------------
SQL SQLMSS SET SERVER &CONNECTION
SQL SQLMSS EX Impact.dbo.Stored_Procedure_Name '&FROMDATE.EVAL', '&TODATE.EVAL', '&DATETYPE.EVAL', &CUSTKEY.EVAL;
TABLE FILE SQLOUT
PRINT *
  ON TABLE HOLD AS XSQLOUT
END
-RUN
-*------------------------------------------------------------------------
-* Check the record count and display a relevant message.
-*------------------------------------------------------------------------
-IF &LINES EQ 0 GOTO NO_RECORDS;
-*------------------------------------------------------------------------

All depends on how you set everything up. We execute the Stored Proc from within the fex. This way, we "control" the process...

Thank you for spending the time to answer in such detail, but I'd like the tool to manage the process.

If anyone else has experience with this please let me know how you addressed it. It seems that it should be relatively straightforward but I can't find reference to it in the documentation.

J.

One of the property settings under Flow Properties is "Execution". Have you checked the option on that page to "Stop processing if 0 rows selected"? We have unchecked it on most of our flows because "no new records" is a valid event for incremental updates. But it may be just the ticket in your case.

That's a good idea for this particular case. I've made that change, thanks!

The question still stands however -- in other events I'd like to be able to set a value or issue a command that lets me control the branching of the overall process flow. If anyone has information on that I'd very much like to hear it. I'd wager there's a variable I can set, but I don't know what it is.

Does anyone from the I-Way teams monitor these forums?

J.

For more granular control, you can set a custom condition for each arrow that connects the procedures/actions within your flow. Right click on any arrow and select "Edit Condition". If you select "Custom", you can make continuation of the flow conditional on any variable within your procedure. You can also create multiple paths from the same procedure, using the conditions to set one arrow to continue to the next flow procedure and another to send an alert/e-mail message and/or terminate the flow if something fails.

quote:

on any variable within your procedure.

That's the magic phrase right there. Thank you! That's everything I need to know.

J.