You must be in the JDBC Data Source window to follow this process. You should also
have configured and tested a JDBC data source connection.
Note: SQL Query Designer does
not work with Hadoop Hive data sources.
Follow this process to design an SQL query for your data source with SQL Query
Designer:
-
Select your data source in the Connections pane on the left, then click
the round green + icon above the Available Queries pane on the
right (this is the + button in the upper right corner of the window).
-
Type a concise yet sufficiently descriptive name for this query in the Query
Name field.
-
Click the pencil icon above the upper right corner of the Query
field.
The SQL Query Designer tool will come up.
-
In the lower left pane, click to select the first table you want to select data
from, then double-click it to move it to the query workspace.
The table you selected will appear in the blue workspace as a sub-window
containing all of the table's rows.
-
Check all of the rows you want to include in the query.
By default, all rows are selected. If you only want to select a few rows (or a
single row), click the table name at the top of the sub-window, then click deselect all in the popup menu, then check only the rows you want to
include in your query.
-
Repeat the previous step for other tables you want to work with.
-
You can create an SQL JOIN between tables by selecting a reference key in one
table, then dragging it to the appropriate row in another table. To modify the
JOIN, right-click its red square, then click edit in the popup
menu.
-
To add a condition or expression, right-click a row in the query workspace, and
select the appropriate action from the context menu.
-
To order or group by a particular row, drag a statement from the SELECT
category in the upper left pane down to the GROUP BY or ORDER BY
categories.
-
To edit the SQL syntax directly, click the syntax tab in the bottom left
corner of the SQL Query Designer window.
-
Click Preview to view the unformatted query results; click OK to
finish working on the query.
You now have a data source and at least one query that will return a data set that
you can use for reporting.