Add a Table to a Dataset Using a SQL Statement

You can write a source-specific SQL SELECT statement to query a connection's data and create a table in a dataset. This manual query option is available for most connection types.

Use the manual query option to create tables when you don't want to use a connection's tables in the dataset but instead want to target specific information. Or use the manual query option when the data source connection doesn't provide a schema listing. The SQL statement you use is determined by the data source connection type.

1. On the Home page, click Navigator and then click Data.
2. Click the Datasets tab.
3. Locate the dataset that you want to open, click Actions, and then click Open.
   Make sure that you open a dataset that's based on database tables. These are displayed with the database icon .
4. Make sure you're in the Join Diagram before you proceed by selecting the Join Diagram tab.
5. In the Data panel, Connections pane, locate and expand the connection that you want to query, and locate the Manual Query option.
   
   
   Description of the illustration data_set_editor_manual_query.png
   
   
6. Drag and drop Manual Query to the Join Diagram to create a table shell.
7. Double-click the Manual Query table.
8. Confirm that Enter SQL is selected.
9. In the Statement field, enter the SQL statement.

   In the SQL query, specify the alias for the columns when you use aggregate functions such as MAX and MIN to avoid query errors. For example, specify SELECT MAX(SYS_BOOKING_DATE) <Alias_Name> FROM <table_name>; instead of using SELECT MAX(SYS_BOOKING_DATE) FROM <table_name>;.

10. Optional: Click Get Preview Data to verify that you're querying the correct data.
11. Click OK.
12. Optional: To modify the SQL statement, go to the Table Page Tabs, and select the tab for the table you created. Click Edit Definition and use the Statement field to edit the SQL statement.