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.

The filter generates two types of RRTs:

  • ~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>]

  • ~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>].

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.

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.

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.

This filter generates the following boolean RRTs that can be used in the Validate filter for metadata validation:

  • ~DFT::%field name#DoesNotExist%~ is replaced with TRUE if there are no records in the result set. Otherwise it is replaced with FALSE.
  • ~DFT::%field name#Exists%~ is 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:

 

LOGIN PASSWORD PERMISSION
John 123 Admin
Albert 321 User
Christian 231 Guest
Carlos 213 User

The filter parameters:

Table: USERS

Columns: LOGIN, PASSWORD, PERMISSION

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:

LOGIN PASSWORD PERMISSION
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"