In-Database Block Until Done

[saubert]

As simple as the title : an In-Database Block Until Done would be a pretty nice feature to control the execution of a workflow.

[HenrietteH]

Hi @saubert

You might already know this - the write data in-db with the "temporary table" option selected works similarly to the block until done tool. It runs one query for everything upstream, puts the data into a temporary table and then uses that to execute everything downstream allowing you to break up your query into multiple steps. 

[saubert]

Hello @HenrietteH : not exactly. The BUD allows to execute several flows with different data at different level of aggregation.

If I have the flow with A, B, C as field and I want to execute first a max(A) group by B,C to append to table T1, then a second flow with a filter on C="TOTO" at the initial level of aggregation, I cannot choose the order of operation.

[slckkrky]

Hi @HenrietteH Can you explain me how to do that? Basically I am taking data from DATATABLE1 apply some calculation and computation and than write back the result to same table. Block Untill Done works perfectly when your source is a file. However as long as the "Run" button is active in your workflow, that blocks the datatable that's why you cannot write back to same table. I think that's a solution for me. Thank you

[HenrietteH]

@slckkrky 

You would use two Write Data In-DB tools - the first one is set to "temporary table", the second one writes to the table you want to write to. 

That runs two queries: 

- first it creates the temporary_table AS (your calculations)

- then it reads the temporary_table back in and uses it to update the actual table

[april_kp]

Hi HenrietteH, I want to write to DB first, then read from the same table to do calculation. How do I prevent it to read before it finish writing to DB?

Block until done only works with files, not In-DB. I basically has the same questions as they mentioned above.

[HenrietteH]

Hi @april_kp 

The Write Data In-DB tool has an input and an optional output. When you add tools to the optional output, it will read the table back in after it is finished writing it. Your database should not allow reading from a table while it is being written to, so you should not have a problem with that using the Write Data In-DB tool