Using a value returned by SQL query as a parameter

Overview

In Etlworks, you can query the database and use the returned value as a parameter for other Flow(s). There are two options:

Database Loop

The idea is that you can write a SELECT SQL statement, which, when executed, creates a cursor driving the loop. The Flow in a database loop is executed as many times as the number of records returned by the cursor. For every column in each record in a cursor, the loop automatically sets the global and Flow variables.

Read about the database loop.

Execute SQL from JavaScript or Python code and set global variables

Step 1. Create a nested Flow.

Step 2. Add named database Connection to the nested Flow.

Step 3. Create JavaScript Flow and add as a first step to the nested Flow. 

Step 4. Add the following code to the JavaScript Flow:

var value = com.toolsverse.etl.core.engine.Extractor.lookup(etlConfig, scenario, 
"connection name", // use the same name as in step 2
"value", // any name
"column_name", // the name of the column returned by SQL query
"select column_name from table" // any valid SQL to retrive the value
);

// only if you need to use the value returned by SQL 
// as a {GLOBAL_VARIABLE} referenced in connections and FROM/TO
com.toolsverse.config.SystemConfig.instance().
getProperties().put('GLOBAL_VARIABLE', com.toolsverse.util.Utils.makeString(value));

// only if you need to use the value returned by SQL 
// as a {FLOW_VARIABLE} referenced in any SQL query
// use uppercased variable name
scenario.getVariable('FLOW_VARIABLE').setValue(com.toolsverse.util.Utils.makeString(value)); 

Step 5. If you are planning to the value returned by the query to parameterize the Connection or FROM/ TO, then all you need to do is reference it anywhere as {GLOBAL_VARIABLE}. The actual name of the global variable can be anything as long as it is the same as you set in the JavaScript code above.  

Step 6. If you plan to use the value returned by the query to parameterize another SQL query, you will need to add the FLOW_VARIABLE as a parameter to the nested Flow created in step 1. The actual name of the Flow variable can be anything as long as it is the same as you set in the JavaScript code, uppercased, and does not include spaces. Finally, you can reference the Flow variable in any SQL query.