Lookup in DB Filter dialog box

This filter retrieves data from a database.

The filter executes an SQL query in a database to select the information from the specified columns of a specified table and generates RRT replaced by the list of the output values both for each column of the query result and for the entire query result.

Note: The user specified for work with databases should have all necessary permissions for the following list of actions:
  • To run a SQL query.
  • To read the database table, including listing all the columns of the database table and getting their names and types.

If the user has no necessary permissions listed above, an attempt to work with the database will lead to an error.

Filter configuration

The filter configuration window consists of four tabs:

Tab Description
General Tab This tab allows configuring the field name of FRTNs generated by the filter and the delimiters used in the RRT values. This tab also allows specifying the data source, the database account’s credentials, and a table for retrieving data.
Output Columns Tab This tab allows specifying the output columns that will be used in the SQL query.
Conditions Tab This tab allows you to specify the conditions that will be used in the SQL query.
Result Tab This tab allows configuring settings for the query execution results.

Generated errors and warnings

The filter generates an error in the following situations:

  • The filter fails to connect to the specified data source.
  • The specified table name is incorrect or is not present in the table list requested by the component.
  • The configured table does not contain one or more specified output columns.
  • The configured table does not contain the column by which a query result is ordered.
  • The specified limit result number is incorrect.
  • The configured table does not contain one or more conditions’ columns.
  • ODBC driver generates an error during query execution.
  • One of the conditions’ columns has an unsupported type. The only types allowed in the conditions’ columns are:
    • Char
    • VarChar
    • LongVarChar
    • nVarChar
    • Decimal
    • Numeric
    • TinyInt
    • SmallInt
    • Integer
    • BigInt
    • Real
    • Float
    • Double
    • Date
    • Time
    • TimeStamp

The filter generates a warning in the following situation:

  • If the condition’s value cannot be converted to the type of the condition column.

Generated RRTs

The filter generates two types of RRTs:

RRT Name Description
The filter generates the following two types of RRTs with the result of a SQL query.
~DFT::%field name%~ The list of all the query result’s output values.

The RRT value is: [<Column 1>, <1>]<C> [<Column 2>, <1>]<C>...[<Column N>, <1>]<R>[<Column 1>, <2>]<C> [<Column 2>, <2>]<C>...[<Column N>, <2>]<R>...[<Column 1>, <M>]<C> [<Column 2>, <M>]<C>...[<Column N>, <M>]


The parts of the above value schemes mean the following:

  • <Column N> is a name of output column with number N;
  • [<Column N>, <M>] is an output value of record with number M of <Column N> column;
  • <Column name> is a column name portion of RRT tag;
  • [<Column name>, <M>] is an output value of M-th record of <Column name> column;
  • <R> is the record value delimiter;
  • <C> is the column value delimiter.
~DFT::%field name,<Column name>%~ The list of the output values for a "<Column name>" column.

The RRT value is: [<Column name>, <1>]<R> [<Column name>, <2>]<R>...[<Column name>, <M>].

Note: See the note for the ~DFT::%field name%~ RRT.
This filter generates the following Boolean RRTs that can be used in the Validate filter for metadata validation.
~DFT::%field name#DoesNotExist%~ Replaced with TRUE if there are no records in the result set.Otherwise, it is replaced with FALSE.
~DFT::%field name#Exists%~ Replaced with TRUE if the result set contains one or more records. Otherwise, it is replaced with FALSE.

The example of this filter usage:

The content of USERS table:

John 123 Admin
Albert 321 User
Christian 231 Guest
Carlos 213 User

The filter parameters:

Table: USERS


Condition 1: LOGIN like "C%"

Condition 2: PERMISSION = "User"

Condition option: ANY

Field name: User data

Record value delimiter: ;

Column value delimiter: ,

Result of the query will be:

Albert 321 User
Christian 231 Guest
Carlos 213 User

~DFT::%User data%~ will be replaced by "Albert,321,User;Christian,231,Guest;Carlos,213,User"

~DFT::%User data, LOGIN%~ will be replaced by "Albert,Christian,Carlos"