Query DB

The Query DB stage allows you to use fields as parameters into a database query and return the results of the query as new fields in the dataflow.

Note: If you want to query a spatial database, use Query Spatial Data instead of Query DB.
To use the Query DB stage, perform these steps:
  1. On the Spectrum Flow Designer Home page, click New.
  2. On the New Flow page, click Job, Service, or Subflow, as required and then click the corresponding blank canvas.
  3. Click Ok.
  4. In the dialog box that appears, give a name to the Flow, Job, Service, or Subflow you are creating.
  5. Click Ok.
  6. From the Palette Panel, drag the Query DB stage to the canvas.
  7. Drag Sources (of the records that are to be joined) to the canvas and connect their Output Port to the Query DBInput Port.
  8. Drag the Sinks for the stage and connect its Input Port to the Query DB Output Port.
  9. Click Query DB and configure the General tab as described below.

General Tab

Option Description
Connection Select the connection you want to use. The drop down displays all the connections you have configured. To configure a new connection or edit any existing connections, click the Manage Connections link. It takes you to the Connections page in the Spectrum Management Console. For details on configuring new connections, see Connections.
Note: Click the Refresh icon to update any modification, deletion or addition to your list of connections.

Table/View

 Specify the table to which you want to write the data.
  1. Click the Browse button ([...]). The Select Table pop-up window is displayed, which shows a list of the tables and views.
  2. Choose the required table.
    Note: You can also use the search text box on the top of the pop-up window to narrow down your search.
  3. Click Select.
The fields of the selected table are displayed in the Fields section on the right side of the page. These are the details displayed:
  • Database Fields: This column lists the field names in the database. You cannot modify these field names.
  • Stage Fields: This column lists the field names used in the data flow. You cannot modify these field names
  • Types: This column lists the data type of each field.

Where

If you want to use a WHERE statement, enter it here. Note that you should not actually include the word WHERE in the statement. The purpose of a WHERE statement is to return only the data from records that match the condition you specify.

To specify a value from a dataflow field, use this syntax:

${<field name>}

Where <field name> is the name of a field in the dataflow.

For example:

account_number=${customer_key}

In this example, the query would return data from records where the value in the table column account_number matches the value in the dataflow field customer_key.

Note: If you are querying a case-sensitive database, make sure you enter the field name in the same format as used in the database table. In other words, enclose the field name in quotes (") if the field names were quoted during table creation.

Click Preview to see a preview of the data (first 50 records) based on the criteria you defined. The preview is generated in the Preview box below the input fields.

Note: The preview feature in Query DB does not work if you use a dataflow field in the WHERE statement. Instead, you can preview the result using the dataflow inspection tool in Spectrum Enterprise Designer.

Return records with no results

Check this box if you want records whose queries return no results to still be returned by Query DB. If you clear this check box, the record will not be returned. We recommend that you leave this option checked.

Fields

In the Fields table, select the fields you want to include by clicking the corresponding Include box.

Sort Tab

If you want to sort records based on the value of a field, specify the fields you want to sort on.