Skip to main content
Pentaho Documentation

Create Queries With SQL Query Designer

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:

  1. 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).
  2. Type a concise yet sufficiently descriptive name for this query in the Query Name field.
  3. Click the pencil icon above the upper right corner of the Query field. The SQL Query Designer tool will come up.
  4. 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.
  5. 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.
  6. Repeat the previous step for other tables you want to work with.
  7. 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.
  8. To add a condition or expression, right-click a row in the query workspace, and select the appropriate action from the context menu.
  9. 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.
  10. To edit the SQL syntax directly, click the syntax tab in the bottom left corner of the SQL Query Designer window.
  11. 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.