Hi,

I have a job that I'm trying to set up, where it queries one database and loops through the results. For each result, it queries another database to check something relating to the result. Now, if it finds a result from this second query, I want it to log that result and then run an update query on the first database using a combination of the results of the first query and the second query. I'm having a couple of issues with this:

1. I can't seem to get the Task Flow working. I have it set up to say "if this query runs successfully, and if the number of rows returned is larger than 0, then continue with the next task". At least, I think that's what the attached image shows. However, that's not what's happening. It's going to the next task even if there are no results.
   
2. Within a loop, I can reference each individual column within a result set using {LOOP(CurrentValueXArray|0)}, and adjusting the "0" to whatever column number I want. But with this second query, there will either be 0 rows or 1 row returned, never more than 1 row, so I don't need a loop. How do I reference each column individually in the output? I tried putting this in a loop, but VisualCron doesn't seem to support nested loops.
   

Thanks.

Just a quick update on this - I did manage to solve problem #2. I found that I can do this:

{STRING(GetColumn|1|1|,|{TASK(c52448b7-e110-445a-bfbe-b9c708e99362|StdOut)})}

So what I did is adjusted the output of my SQL task so that it doesn't include text qualifiers, because the GetColumn thing above doesn't seem to have an option to specify what the qualifier is, so it was still showing the quotes. Once I did that, I was able to reference each column separately, and my update query worked.

I'm still having trouble with problem #1 though. I don't want it to go ahead with the update SQL if it can't find a value to update it with. Did I do something wrong in the task flow?

Thanks.

I thought it might be easier to understand with a picture, so here's my list of task steps.

1. Start the log entry for the current run - this step simply opens up the log file and writes a message with a timestamp that says "hey, I'm running now".
   
2. Get a list of open indirect activities - this step queries Database #1 for a list of things it needs to close off.
   
3. Log the number if indirect activities found - this step writes to the log file from step 1, saying "I found 3 activities to close off", or whatever the number is that step 2 returned.
   
4. Log the current record - this is the start of the loop, and logs the current transaction number in case we need to go back and look at something later on.
   
5. Look for a clock out for the current user - this step queries Database #2 using values from the results of step 2's query from Database #1. This is where I'm trying to set up the task flow. What I want it to say is "if you find a result, continue with step 6; if not, skip steps 6 and 7 and process the next record in the results of step 2".
   
6. Log the clock out - this step logs the result of step 5's query.
   
7. Add a closure time for the activity and resend the transaction to LM - this step runs an Oracle PL/SQL block that inserts 1 record and runs 2 update statements using values from the results both step 2 and step 5.
   

Just another update - I managed to figure out a way to do what I want. It's not exactly intuitive like a simple "if, then, else" statement would be, and basically makes use of "goto" statements which is generally frowned upon in the world of computer programming, but it works. What I did is this:

1. Dummy task ("wait") - the task itself does absolutely nothing, but I added an On Start flow that checks the number of records returned from a query. If the value is greater than zero, it skips the next task.
   
2. Write File - this task writes "no records found" to a log file.
   
3. Write File - this task writes "found X records", where X is the number of records returned by the query. I added an On Start flow that says "if the query returned 0 records, continue with the next task". This way, if there's no records, it simply skips over this task.
   
4. SQL - this task updates the database using values from the two different queries. It also has an On Start flow that skips this task if the query returned 0 records.