Relational Table Extract

image_pdfimage_print

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.

Main 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 relational table extract.
Where-Filter:

Operators:

  • Equal: (=)
  • Not equal: (<>)
  • Less than: (<)
  • Less than or equal: (<=)
  • Greater than: (>)
  • Greater than 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.
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: Ascending or descending order of the entered column.
Advanced Settings
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 relational table extracts with subsequent changes.

Examples:

  • JOIN
  • UNION
  • Nested SQL statements
image_pdfimage_print