How to create a SQL query to retrieve data from a JDBC data source
This procedure assumes you have already created the JDBC data source that this data set uses.
1 In Data Explorer, right-click Data Sets, then choose New Data Set.
2 In New Data Set, specify the following information:
1 In Data Source Selection, under JDBC Data Source, select the data source to use.
2 In Data Set Type, select SQL Select Query.
3 In Data Set Name, type a name for the data set.
4 Choose Next.
Query displays the information to help you create a SQL query. Available Items lists the items in the data source.
3 To see the tables in a database, expand the database, as shown in
Figure 4‑35.
Figure 4‑35 Viewing a schema
You can use the following filter options to display specific schemas or objects. Choose Apply Filter after specifying the filters.
In Schema, select an item to display only objects from that schema.
In Type, select the type of objects—tables, views, or stored procedures—to display.
In Filter, type one or more letters to display only the objects that have names beginning with those letter or letters. You can also use SQL filter characters for the database that you are using. For example, on some databases, an underscore (_) matches any single character, and the percent sign (%) matches any sequence of characters.
4 To display the columns in a table or view, click the plus sign (+) next to a table or view name.
5 In the text area, type a SQL statement that indicates what data to retrieve from the JDBC data source. Alternatively, drag tables, views, and columns from Available Items to the text area to insert their names in the SQL statement at the insertion point, as shown in
Figure 4‑36.
For some databases, if a table or column name contains spaces or SQL reserved words, you must enclose the name in quotation marks (" "). If you drag and drop tables and columns, and those items need to be enclosed in double quotation marks, select the Use identifier quoting option. When this option is selected, the data set editor inserts the quotation marks around a table or column name when you drop it in the text area.
Figure 4‑36 Adding a table to a SQL query
6 Choose Finish. Edit Data Set displays the columns specified in the query, and provides options for editing the data set.
