Using If Statements

I've been doing a lot of searches on this forum as well as on google but did not get any answers on how to use the IF module in TDP.
I have version 4 and 5.6 with not much difference.
I have several scripts and Export modules that are run which I wanted to capture in a log if any of them failed.
I run these on a remote server and have to monitor the output at the expected scheduled time but you only know the TAS has failed if the output does not land when expected and that's when you log in and check the logs.
I have an e-mail script that I have got setup to run in Oracle which i wanted to use in the case of TAS failing.

So after many hours, I managed to get this setup - but am not totally convinced it will capture everything - but may help anyone out there

So I have my scripts which would run and I'd capture the error in the IF module as such

1st left IF module - captures if any of the scripts above failed
#Execute_1_RCOUNT#<0 or #Execute_2_RCOUNT#<0

2nd IF module - captures if ALL of the scripts have run fine
#Execute_1_RCOUNT#>0 and #Execute_2_RCOUNT#>0

Note that one has an 'OR' whilst the other 'AND'

I have then captured the results in my log below
My log = Script has failed
(Script 1) #Execute_1_RCOUNT#
(Script 2 )#Execute_2_RCOUNT#

I have tested this and works so far but am not totally convinced this is the correct process

I think you have your logic almost there, and maybe even spot on if certain assumptions are in place:

  • Yes, It is true that the rowcount variables from the Script Execution task return the value of -1 if there is a failure in the query execution.
  • Logic will NOT work, if you didn't specify "Continue on Error" in the Settings step
  • Your Logic assumes that your queries (before the If-Then step) are returning at least one row. If it's not an error when the queries return zero rows, then you'll need to specify that condition in your Success branch of the If-Then step...
    e.g. not simply
    #Execute_1_RCOUNT#>0 and #Execute_2_RCOUNT#>0
    but
    #Execute_1_RCOUNT# >= 0 and #Execute_2_RCOUNT# >=0
  • Finally, it might be useful to display the value of the TDP variable #ACTIVITY_RESULT# in your Failure logic branch, which variable typically contains the error message encountered

Hope this helps.

Thanks Gary.
I noticed that I can't use my own solution if I'm using a export module and only use this if I'm running scripts module.

Any idea what I can use to capture a failure on an export or an import module along with scripts?

I pinched this from this forum somewhere -
"Instr('#_ACTIVITY_RESULT#', 'ORA-00942')<=0 and Instr('#_ACTIVITY_RESULT#','Succeeded')<=0
and
Instr('#_ACTIVITY_RESULT#', 'ORA-00942')<=0"

but this does not work effectively and not sure what the 'succeeded' bit means as have never seen this in the logs

Not sure about capturing the error message of one of the modules, but definitely look at what gets returned from the #ACTIVITY_RESULT# variable... e.g. do a quick test with a two step job, where you run your export/import, maybe with known error (step one task) then produce a comment in the automation log that displays the #ACTIVITY_RESULT# value (Log Comment step as task two).

Maybe the error message is still captured in the #ACTIVITY_RESULT# variable, and then you can define your If-Then logic accordingly, since you'll know what to look for.

Is there anywhere I can get an understanding on how to use the IF functions.
I'm looking at : Instr(, [, [, ]])
vs
:Instr('#_ACTIVITY_RESULT#', 'ORA-00942')<=0 and Instr('#_ACTIVITY_RESULT#','Succeeded')<=0
and
Instr('#_ACTIVITY_RESULT#', 'ORA-00942')<=0

I can't seem to work out what it's doing.

I have a test TAS where I'm trying to understand how all of this works, where I have 2 'run scripts', one working and one that would fail.

I have also added a log below each one to capture the #_ACTIVITY_RESULT#.
If it works fine then I get no result and if it fails then I get this :
('Execute_Fail - Oracle Database Error; Inner exception: ORA-01843: not a valid month',)

So with the instr bit above, how do I capture this in my 'IF' block so it works.

This is what I have at the moment :
'#_ACTIVITY_RESULT#'>0 this is for capturing errors

and this for when there is no errors

Instr('#_ACTIVITY_RESULT#', 'ORA-')<=0

and regardless of me enabling either script 1 (working) or script 2 (which would fail) I get the same output stating that the output has no errors even when one of the script would fail.

I'm also trying to include an export module and also trying to capture the result, but the 'log' does not capture the status of the export being successful or not

hope the above makes sense

The INSTR(string, substring) function is an Oracle function returns the location of the first character in which substring occurs in string.

  • If Instr('#_ACTIVITY_RESULT#', 'ORA-')<=0 -----
    You are using this expression to check for no errors. This expression holds true if 'ORA' is not present in the message TDP returns in #_ACTIVITY_RESULT#. IF there is an Oracle error, then this would be good logic. However, if it's possible for an error message to get returned that does NOT include an instance of 'ORA' then you will need to look at some other conditional. I would simply use
    . . . If #_ACTIVITY_RESULT#='Succeeded'

  • If '#_ACTIVITY_RESULT#'>0 ------
    You are using this expression to check for errors? This is not a valid expression (a string compared to a number?), or maybe you really meant to indicate Instr('#_ACTIVITY_RESULT#', 'ORA-')>0 ? Again, this may not work if the error message doesn't include 'ORA'.
    Perhaps better if you check for no instance of 'Succeeded':
    . . . If Instr('#_ACTIVITY_RESULT#', 'Succeeded-')>0
    or, more directly,
    . . . If #_ACTIVITY_RESULT#<>'Succeeded'

Hope this helps.