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 having-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

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
Was this post helpful?
NoYes (0 rating, 2 votes)
Loading...