Note/Update This topic started as a nested-if discussion but turned out to be about 'Test Variable' action and numeric values.
Is it possible to create nested If...Then...Else conditions in Automation designer?
The GUI allows to do it, however when I execute it, the second IF seems to be ignored.
In the example above, the numbers is green are the value assigned to each variable. In the first IF 6200 <= 3500 is evaluated FALSE, so the control goes to the ELSE, the second IF 2061 <= 75 should be evaluated as FALSE, but still the "Email-Readings BW" gets sent!!
I have confirmed the value of the variables because they are included in the body of the email, so it doesn't make any sense, unless Nested IFs are not allowed (in which case it shouldn't be allowed in the GUI the begin with)
BTW: I had to build the nested IFs because I couldn't find a way to do an OR condition on the first IF. Is that even possible? Unfortunately there is really not much information about "Toad for Oracle Automation Designer" out there, and the documentation is really poor. I'd appreciate any help with this.
Thanks
I'm running Toad for oracle 13.1.0.78
Additional info:
Actually, the problem doesn't seem to be the IFs. The problem is the "Test Variable" action which doesn't work. It always evaluates to True.
I've tried setting the variables as Local or Global without success. The variables hold the right values (I can see them in the email body and if a use a Message action. They just don't work on the Test Variable action.
Not sure if I'm doing it correctly, but this action looks pretty straight forward:
I’ve attached a small example of two nested If…Then…Else statements that you can import and try. They work based on a TEST variable defined in Options and react accordingly when I update it.
App1.txt (7.3 KB)
I tried recreating a more accurate logically situation to what you’ve got above and am also seeing an issue.
Thanks for checking.
I found this thread https://forums.toadworld.com/t/using-variables-in-a-toad-automation-query/44477/7 where it’s mentioned that query iterator variables can not be used in a Test Variable action. It was for a previous version of Toad, so I wonder if this is still an issue.
Without query iterators I’m still hitting an issue with my second If…Then…Else also returning TRUE so it seems to be an issue with the nested If…Then…Else statements themselves.
To be clear, when I said…
“But it looks like “Test Variable” won’t let you choose a Query Iterator variable. I can fix that for the next version but it won’t help you with 12.11.”
…I was referring to the fact that if you click the button below, the GUI would not give you the option to choose a query iterator variable, only variables defined in the Toad options window. But that’s been fixed.
@JohnDorlon
That's good to know!. However, I still cant see the query iterator variables in the list:
I'm using Toad for oracle 13.1.0.78. Is there anything else I'd need to do to see the variables?
@mike.hamer
I added a bit of things to your App1 and it seems to work fine for me (I tested changing NUM1 in the query for 40, 60 and 80)
I initialized the variables TEST1 and TEST2 under options with a 0 initial value. It's weird it works in this example but not in my original App...
App1.1.txt (11.7 KB)
@humberto.higuera Sorry, I failed to check it in 13.1. Turns out I fixed it more recently than that. It’s in 13.2 beta.
Well, this is weird. The App works depending on the Query!
If I run a simple query getting 3 values, assigning one of them (NUM2) to a variable TREAD, it works:
Result:
Now, if I use my query, which returns a row using the same column names as above, not changing anything else on the App, it doesn't work!:
Result:
And it is evident that the variable value is correctly assigned.
Here is the new App;
App1.2.txt (11.7 KB)
I don't understand why changing the query to one that gives similar results (a row with same column names) would make the App (specifically the "Test variable1" action) fail?
False alarm! Is not the query. If I change the NUM2 value to 2280 in the simple query it doesn’t work anymore. It works with a value of 60 but not with a value of 2280
Weird thing
For some reason, we are comparing those values as strings instead of numbers. We have code in there to treat them as variants and then use the proper comparison, for some reason that’s not working.
Ok! That makes sense now!
If I use strings ‘002280’ comparing against ‘000050’ it works! I guess I can use this as workaround in the meantime
Hmmm…still doesn’t work if I wrap the result column around a TO_CHAR(…,‘0000000’) but it works if I explicitly return a string
For some reason it treats SELECT to_char(6831,'0000000') different to SELECT '0006831'
Any idea?
I see one they are treated slightly differently when you do that, but neither as numeric…
Ok.
After a lot of testing and Oracle documentation reading about data types I managed to make it work. I had to convert the result of the query to a leading zero format with to_char and then wrap it around a cast to char().
cast(to_char(nvl(sum(case when s.SERVER_NAME like 'BWTTCOM%' then 1 else 0 end),0),'0000000') as char(8)) NUM2,
The additional trick was to make sure I added a leading space on the Test variable constant I’m using (the ‘cast to char’ seems to always add a space for the sign) so the test works correctly.
There is a lot of workaround though. Most concerning is that if at any point you guys change the way Automation Designer treats the variables my changes may stop working. I guess I’ll just have to keep it mind
Thanks for checking into this and for the help figuring it out.
There isn’t a way to declare the type of a variable in Toad, so we were just trying to treat numeric values as numbers, and everything else as strings. I made a change so that it should work as intended. So in 13.2 your original code should work, without all the casting.
Thanks!
I’ll remember it when the update becomes available.
