I have a loop that pulls data from a database node and aggregates the results. However, when a query returns an empty result, the loop fails and cannot continue. The database node returns an empty table with no columns, for queries which have no result and I cannot change this. What I want to be able to do, is continue the loop even though one of the queries returned an empty table. Note all my queries will return the same columns (except queries which have no results). Also the database node has a specific interface and I cannot combine the queries in anyway.
I have had similar problems in loops.
the best way of dealing with this is to insert an empty table switch node so that the branch is inactivated when there are no rows, then follow this node with a end case node. Also connected to the end case node, you want an empty table which has all the correct table structure of columns. You can do this with a create table structure node and then rename node.
the key reason for the loop failing is the fact the column structure changed, rather than the table having no rows.
If you need the loop to execute even when no rows are present, the alternative is to create an empty table with all the correct columns present, and then concatenate this with the database output before the loop end node - this way you will end each iteration with the correct columns present
I have a variant problem to this; the outer loop gets stuck with a nested loop error - I have to execute the outer loop repeatedly to finish collecting iterations… it’s not a hard fail, the light on the outer end loop goes to yellow whenever the empty table switch is triggered and the placeholder table is brought in.
In the picture I have posted, I have left the experimental flowvariable path dependencies I’ve already attempted - to no avail.
Here’s what happens when the empty loop is triggered, the nodes upstream (not shown in picture) are all green on the active path of the if-empty:
After a good night’s sleep, I realized an alternative solution. Instead of protecting the inner loop from empty tables, I allowed the inner loop to consume an empty table but protected portions of the workflow inside the inner loop that could not tolerate empty tables. This architecture seems to work for now.