Inserting a Query Variable

  1. Open the required job, which includes a Read From DB stage. Alternatively, add a Read From DB stage to the job.
  2. Open the Read From DB Options of the Read From DB stage.
  3. Create the SQL query in the SQL field, either manually or using the Visual Query Builder. For more information, see Visual Query Builder.
  4. Add the desired conditions in the where clause of the query using variables with the syntax #{variable}.
    For example, in a table CUSTOMERS, which has the column AGE with values such as 28, 32, 30, and so forth, and a column SALARY with values such as 1000, 1500, 2200, and so on, frame an SQL query as below:
    select * from CUSTOMERS where #{condition1} > 28 and #{condition2} >  1200
    Note: On inserting a variable in the where clause of the SQL query, the Build SQL..., button is disabled.
  5. To view the schema and the sample records to be fetched by the query, enter Stage Options value on the Runtime tab, and then click the Regenerate Fields and Preview buttons respectively.
  6. Click OK.
The where clause of the SQL query can now be customized at runtime using the Dataflow Options, or while executing the job through the JobExecutor.
Note: A variable can be placed in the select clause of an SQL query as well. However, such a variable name should match the name of one of the columns of the table being queried.