This extract is used to read data from a relational database using SQL.
|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.|
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:
|Use Caching:||Memory, disk, or none (default). See Caching in Extracts for more information.|
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.
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.
|For MS SQL Server:||EXEC proc_name ‘paramValue1’ ‘paramValue2’|
|For MySQL:||call MyProcedure1
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.