RelationalTable Extract

This extract allows retrieval of data from a table or view of a relational database without writing a SQL query statement. It allows selecting columns with aliases, filtering with WHERE and HAVING clauses, sorting, and aggregation.

Settings

Connection Connection to a relational database.
Schema The name of the schema in the relational database (if database engine supports schemas).
Table The name of the table in the relational database.
Output columns Columns that should be returned by RelationalTable extract.
Where-filter Operators:
  • Equals: (=)
  • Not equals: (<>)
  • Less than: (<)
  • Less or equal: (<=)
  • Greater than: (>)
  • Greater or equal: (>=)
  • Like: in this case, the filter value is a pattern. The condition is true if the field data matches the pattern. You can use the following wildcard characters in the pattern: % for a sequence of any characters (including spaces) and _ for a single character. The Like operator performs a case-insensitive match. Example: appl%
  • IN: the filter value is a list of values. The condition is true if the value equals any one of the specified values. Example: 'California', 'New York'
  • Not IN: The filter value is a list of values. The condition is true if the value does not equal any one of the specified values.
  • Is Null
  • Is Not Null
Logical filter expression A Boolean condition can be applied for if several filter conditions have been defined. Valid symbols are: [0-9] ( ) AND OR NOT

Examples:

  • (1 AND 2) OR 3: Retrieves records that match both the first two filter rows or the third.
  • 1 AND (2 OR 3): Retrieves records that match both the first filter row and one of the last two.

If no logical filter expression is defined an AND filter is applied, i.e. 1 AND 2 AND ... AND n.
Sorting Elements can be sorted in ascending or descending order, with or without case sensitivity.
Distinct When checked, only distinct values from the source are returned.
Aggregated columns

Use aggregate functions from a relational source. Select the column and type of aggregation. If aggregate functions are used, the GROUP-BY statement is automatically generated from output columns table.

Possible aggregations are:

  • SUM: gets the sum of the selected column (from all result rows)
  • MIN: gets the minimum value of the selected column
  • MAX: gets the maximum value of the selected column
  • AVG: gets the average value of the selected column (from all result rows).
  • COUNT: counts occurrences of selected columns (of all result rows)
  • SUM_DISTINCT: same as SUM, but applied to distinct values*
  • AVG_DISTINCT: same as AVG, but applied to distinct values*
  • COUNT_DISTINCT: same as COUNT, but applied to distinct values*

* duplicated result rows are not calculated

Having-filter Same as Where-Filter, but applied to result of aggregated columns.
Logical filter expression Same as logical filter expression, but applied to Having-filters.
Query attachment An optional part of a SQL query with additional options that are appended to the generated SQL statement.

Example:

LIMIT 10 OFFSET 10
Use caching Memory, disk or none (default). See article Caching in Extracts.

Note: if you require other SQL capabilities not covered by this extract, you either have to use a Relational extract, which allows a free SQL query statement, or you have to use separate RelationalTable extracts with subsequent changes.

Examples:

  • JOIN
  • UNION
  • Nested SQL statements

Updated March 15, 2024