Configuring a Query Variable as a Dataflow Option

  1. Open the required job for which the query containing the variable(s) has been defined in a Read From DB stage.
  2. Open Edit > Dataflow Options....
  3. Click Add.
  4. In the Map dataflow options to stages section, expand the Read From DB entry.
    The variables defined in the SQL query in the Read From DB stage are listed along with the other attributes of the stage.
  5. Select the variable you wish to customize using the corresponding checkbox.
  6. Enter a relevant name for the variable in the Option label field.
  7. In the Default value field, enter the column name which is to be used instead of the selected variable in the where clause of the SQL query. Alternatively, enter a constant value to be used instead of the variable in the where clause.
    For example, for the below SQL query defined in the Read From DB stage:
    select * from CUSTOMERS where #{condition1} > 28 and #{condition2} > 1200

    you can select the column AGE of the table CUSTOMERS as the Default value for the variable condition1, and the column SALARY as the Default value for the variable condition2.

    At runtime, the query is interpreted as:

    select * from CUSTOMERS where AGE > 28 and SALARY > 1200
  8. Repeat steps 5-7 for all the variables placed in the SQL query in the Read From DB stage.
  9. Click OK.
On running the dataflow, the customized query is used to fetch the required data.