This extract is used to read data from a relational database using SQL. First, select a connection to a relational database. 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.
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 “Preview data” button. A preview window should show that the SQL statement filtered out customer numbers that are larger than 1000.
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:
|FieldName||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||This defines a first basic mapping. A blank, several blanks, or a null value will be mapped to this default value|
In the Relational Extract, it is also possible to retrieve data by calling a Stored Procedure (SP).
Note: to use Stored Procedures that change data, you should use the load of type RelationSQL.
|For MS SQL Server:||EXEC proc_name ‘paramValue1’ ‘paramValue2’|
|For MySQL:||call MyProcedure1
Under Advanced settings you have the option to use caching for none (default), memory, or disk. See Caching in Extracts for more information.