Relational Extract

This extract is used to read data from a relational database using SQL.

Settings

Connection Requires a connection to a relational database.
SQL query With an arbitrary SQL Select Statement, data can be read from the database. All options of the SQL syntax (e.g. for filtering, sorting, and joining) can be used. The specific SQL syntax of the connected relational database must be used, although in general, all relational databases should allow the ANSI SQL standard. See example below.
Field structure The resulting field structure of the Relational Extract can be generated with the option Update Field Structure. The number and the (original) name of all columns are displayed. For each column, you can optionally define:
  • Field Name: an alternative name to the original name of the column. The field will be referenced with this name for the rest of the Jedox Integrator process.
  • Default: defines a first basic mapping. A blank, several blanks, or a null value will be mapped to this default value.
Use caching Memory, disk, or none (default). See Caching in Extracts for more information.

Example

Below is an example of a SQL statement that filters all customers larger than 1000:

SELECT id, description, customer, weight, status from crm.opportunity
WHERE customer > ‘1000’.

To check whether the correct data has been extracted using the SQL statement, click on the Data Preview button. A preview window should show that the SQL statement filtered out customer numbers that are larger than 1000.

Stored Procedures

In the Relational Extract, it is also possible to retrieve data by calling a Stored Procedure (SP). To use Stored Procedures that change data, you should use the load of type RelationSQL.

Examples:

For MS SQL Server EXEC proc_name 'paramValue1' 'paramValue2'
For MySQL call MyProcedure1
call MyProcedure2('paramValue1','paramValue2')

Note: for a multi-statement, table-valued function, the count(*) function does not give the correct count in the data preview. This is a known issue.

Updated November 4, 2024