Parameter Assignment Example

Application Scenario

There is a data table named Product in the demo1 database. You intend to synchronize the data where Category_ID = 2 to the demotest database.

Implementation Method

In the Product table, Product_ID is the primary key. First, you need to retrieve the Product_ID values of the data where Category_ID = 2 and set them as parameters; then, you can use these parameters in downstream data synchronization to synchronize the data that meets the criteria to the target database.

For detailed demo examples, see Parameter Assignment at https://endemo.finedatalink.com/webroot/decision/.

Assigning Values for the Parameter

1. Create a scheduled task and drag a Data Transformation node onto the page.

2. Retrieve the Product_ID values of the data where Category_ID = 2 and set them as parameters, as shown in the following figure. 

SQL Statement:

SELECT Product_ID from `demo1`.`Product` 
where Category_ID=2

Click Data Preview to see the extracted Product_ID values of data where Category_ID = 2, as shown in the following figure.

3. Output the retrieved Product_ID as a parameter.

Click Output Parameter and set the parameter name as id_para; since the retrieved Product_ID field is in the first column of the extracted data, retrieve all row data of the first column as the parameter value. The number of field values output by a single parameter in the Parameter Assignment node is restricted to a maximum of 10,000.

As the retrieved value of Product_ID is a numeric value, do not enable the Enclosure Character. Click Parameter Preview. After a successful preview, the first five values will be automatically selected and filled into the debug value of the corresponding parameter.

Note:

The debug value is only set for downstream nodes to preview in advance.

The page is shown in the following figure.

Note:

The Parameter Assignment node can only generate a single piece of data. If it is necessary to output multiple pieces of data separately, the Loop Container node can be used.

At this time, if you found that the retrieved data requires complex operations (such as column-to-row conversion, data association, JSON parsing, and other data processing and transformation) to be used as parameters, you can use the Data Transformation function. You can add a Data Transformation node based on the data source and output parameters of the Parameter Assignment node. You can use the Data Transformation node instead of the Parameter Assignment node to perform complex data processing, and set parameters for the processed data using the Parameter Output function. For details, see Data Transformation Generation.

Using theParameter

After setting the parameters, you can use them in downstream data synchronization to synchronize the data that meets the criteria to the target database.

1. Drag in a Data Filtering and Output node and connect it to the Parameter Assignment node.

2. Configure the Data Filtering and Output node to retrieve all data from the Product table in the demo1 database that meets the parameter conditions in the Parameter Assignment node — that is, retrieve all data corresponding to the Product_ID where Category_ID = 2.

SQL Statement:

SELECT * FROM `demo1`.`Product` WHERE Product_ID IN  (${id_para})

Note

:

Unlike custom parameters, single quotes are not necessarily required when referencing parameters of the Parameter Assignment node. If the delimiter defined in the Parameter Assignment node is correct, you can directly use ${parameter name} when using the parameter.

3. Configure Data Destination and Mapping. Synchronize the data that meets the criteria to the Product_Parameter table in the demotest database, as shown in the following figure.

3. Use the default write method.

Effect Display

1. Click Run to execute the task. The running result in Log upon successful execution is shown in the following figure.

2. Data in the database table is shown in the following figure.