Adding an SQL Query

If you need to retrieve data based on a more complex query than just selecting the field from a single table, you will need this feature. The SQL Query can be as much complicated as the database provider can support. This means that you could join tables, do summarization and grouping without any issues.

To add an SQL query:

  1. From the Tables window, in the upper left hand corner, click Add and select Add SQL Query.

  2. In the Destination Table Name field, enter a name for the table destination.

  3. In the SQL Expression text box, enter the SQL statement and click Save.

Important

Using of the schema name in the SQL statement is highly recommended.

Tip

Variables can also be used to create queries in an SQL statement.

Example:

SELECT * FROM TABLE WHERE YEAR = @YEAR
Note

The QUERY will be added in the list.

Once in the list, you can:

  • Do a mouse over to see the whole SQL statement,
  • Click on the QUERY hyperlink in the Table Source column to change the statement any time,
  • Apply a filter directly in the query statement (ex: select field1, sum(qty) from schema.table1 group by field1 where field2 = 2018) or in the row of the Filter column as a regular table If you are looking for more clarity.
Tip

You can also get access to the Tables windows by clicking on the hyperlink within the Tables column on the Extraction page.