Inserting a Query Variable - 23.1

Spectrum Dataflow Designer Guide
Version
23.1
Language
English
Product name
Spectrum Technology Platform
Title
Spectrum Dataflow Designer Guide
First publish date
2007
Last updated
2024-05-09
Published on
2024-05-09T23:01:03.226155
  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.