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	If caching is activated, the complete output of the extract is temporarily stored during the first call of the extract, using an internal H2 database. Subsequent calls of the extract read directly from the cache without connecting to the underlying source system of the extract. If the extract or the underlying connection contains variables, a separate cache is build for different values of these variables. See Caching in Extracts and Transforms 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 May 12, 2025