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

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:

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"

Example of handling NULL values

The content of USERS table:

#

ID

PERMISSION

1

10

User

2

20

Guest

3

30

  • If you apply the filter 'ID = 10' then the result RRTs are:

    ~DFT::%....#Exists% = TRUE
    ~DFT::%....#DoesNotExists% = FALSE
    ~DFT::%....PERMISSION% = 'User'
    (1 record has been found)
  • If you apply the filter 'ID = 20' then the result RRTs are:

    ~DFT::%....#Exists% = TRUE
    ~DFT::%....#DoesNotExists% = FALSE
    ~DFT::%....PERMISSION% = 'Guest'
    (1 record has been found)
  • If you apply the filter 'ID = 30' then the result RRTs are:

    ~DFT::%....#Exists% = TRUE
    ~DFT::%....#DoesNotExists% = FALSE
    ~DFT::%....PERMISSION% = ''
    (1 record has been found)
  • If you apply the filter 'ID = 40' then the result RRTs are:

    ~DFT::%....#Exists% = FALSE
    ~DFT::%....#DoesNotExists% = TRUE
    ~DFT::%....PERMISSION% = ''
    (0 record has been found)

If you want to exclude the empty values from the list, create a view with the following SQL command: SELECT * FROM USERS WHERE PERMISSION IS NOT NULL.

If you run the filters applying the new view, the results for the third and fourth case are:

  • If you apply the filter 'ID = 30' then the result RRTs are:

    ~DFT::%....#Exists% = FALSE
    ~DFT::%....#DoesNotExists% = TRUE
    ~DFT::%....PERMISSION% = ''
    (0 record has been found)
  • If you apply the filter 'ID = 40' then the result RRTs are:

    ~DFT::%....#Exists% = FALSE
    ~DFT::%....#DoesNotExists% = TRUE
    ~DFT::%....PERMISSION% = ''
    (0 record has been found)