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.

General Tab

Option Description

Connection

Select the database connection you want to use. Your choices vary depending on what connections are defined in the Connection Manager of Spectrum Management Console. If you need to make a new database connection, or modify or delete an existing database connection, click Manage Connections.

If you are adding or modifying a database connection, complete these fields:
Connection name
Enter a name for the connection. The name can be anything you choose.
Database driver
Select the appropriate database type.
Connection options
Specify the host, port, instance, user name, and password to use to connect to the database.

Table/View

Specifies the table or view in the database that you want to query.

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.

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.

Include

In the fields table, select the fields you want to include by clicking the Include box next to the field.

Sort Tab

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