Information links are created using the Information Designer. However, sometimes there may be situations where complex SQL queries are needed that cannot be generated by Information Designer and the elements in the information model. An example might be to retrieve information from relational databases or databases with complex schemas, where queries generated by Information Designer are not fast enough and need tuning to reach an acceptable performance. For those purposes the possibility to manipulate the generated SQL of an information link has been introduced.
To modify the SQL of an information link:
Make sure the information link of interest is opened on an Information Link tab.
Click SQL....
Response: The Edit SQL dialog is displayed.
Select the Data source that you want to work with from the drop-down list.
Comment: Complex information links may contain elements from several different data sources. You can only edit the SQL part of the query that belongs to a single data source at a time. After you have saved your first changes you can switch to another data source to edit the rest of the SQL.
To edit Pre-Updates, Query or Post-Updates, click the corresponding radio button.
Comment: Use Query to modify the SELECT statement of the information link. Use Pre-Updates or Post-Updates to add new statements or scripts to be run before or after the data retrieval.
Edit the SQL statement (or add Pre- or Post-Updates) in the Modified SQL text box.
Comment: You can compare the changes that you have made in the Modified SQL to the Original SQL at all times. Multiple SQL statements are allowed as long as they are separated with a semicolon and new line (except the last statement in sequence).
Comment: You can use parameters in your SQL statements (and in your Pre- and Post-updates).
Note: If you alter parameters in the SQL code, the changes will override the parameter settings made in, for example, the filter part of the information link tab.
Click OK when you are finished.
Comment: If you change your mind and want to start over with the original SQL, click Reset to Original.
Note: The modified SQL is not validated before execution. For this reason, you should not:
alter the name of a column (the AS-part of SELECT xxx AS yyy)
alter the number of columns returned
alter the datatype of columns returned
alter the order of columns returned
remove <temporary_result_#> from the FROM-clause in information links that go against multiple data sources
remove the trailing WHERE <conditions>, as it will be replaced by any conditions applied at runtime
See also: