Parameterizing Query DB at Runtime

You can configure the Query DB stage so that values in the WHERE clause are specified at runtime. This is useful in cases where you want to make the column name in the WHERE clause configurable using Dataflow Options.

  1. Open the dataflow in Spectrum Enterprise Designer.
  2. Configure the Connection and Table/View name fields to point to the database you want to query.
  3. In the Where field, enter a WHERE statement using the following format for values you want to parameterize: ${parameter}.
    For example:
    ${COL}=${EmployeeID}

    Here COL represents a Dataflow option which will be populated with the column name for the table at runtime.

  4. Close the Query DB options window.
  5. Click the Dataflow Options icon on the toolbar or click Edit > Dataflow Options. The Dataflow Options window appears.
  6. Click Add. The Define Dataflow Options window appears.
  7. Select the Query DB stage.
  8. Specify Option name and Option label.
    The value in the Option name field should be the same value as entered in the format ${parameter} in the WHERE clause. In the Option label field, you can specify a different label or keep it the same as the Option name.
    For example: COL
  9. Specify the Default value. For example: "EmpID".
  10. Click OK.
This procedure maps the actual database column name i.e. EmpID, to the runtime option name "COL". The database column name needs to be appropriately quoted with the specific quote identifier for the particular database.