SQL Builder
This is a feature of JBuilder Professional and Enterprise.
To open the SQL Builder, click the SQL Builder button from the QueryDescriptor dialog. For the SQL Builder button to be active, you must be connected to a database. To learn how to create a database application that includes connecting to a database and adding a QueryDescriptor, see "Querying a database" in Database Application Developer's Guide.
Columns tab
Begin building a query by selecting columns you want to add to the query, or by selecting an aggregate function that operates on a specific column. The Available Columns list displays a list of all tables in the database, and all of the columns in each table. The Definitions tab displays the properties of whatever is selected in the Available Columns list. The Data tab is available whenever a table is selected, and displays the data from the table in a grid.
- To add one or more columns,
- Click the expand glyph to view all tables.
- Select a table.
- Select a column you want to view from the table you want to access.
- Click Copy.
The name of the selected column appears in the Selected Columns box. Continue selecting columns until you have all you want from that table. If you want to select all columns, click the Copy All button.
- To add an aggregate function to the query,
- Click the Aggregate > button to display a dialog box.
- Click the column whose data values you want aggregated in the Available Columns list.
- Click the function you want to use on that column from the Aggregate Functions list.
- If you want the function to operate on only unique values of the selected column, check the Distinct check box.
- Choose Add Aggregate to add the function to your query.
- Add a Group By clause (when you aggregate data, you must include a GROUP BY clause).
As you select columns and add functions, your SQL SELECT statement is being built. To view it, click the SQL tab.
Distinct checkbox: Selecting rows with unique column values
You might want to see only those rows that contain unique column values. If you add the DISTINCT keyword to the SELECT statement, only rows with unique values are returned. DISTINCT affects all columns in the SELECT statement.
To add the DISTINCT keyword, check the Distinct option on the Columns page.
Where tab: Adding a Where clause
To add a Where clause to your SQL query, click the Where tab to display the Where page.
The Columns list on the left contains the columns of tables in the currently selected query in the Queries panel of the SQL Builder. Use the Columns, Operators, and Functions lists to build the query in the Where Clause box:
- To transfer a column as a column name to the Where Clause box, select a column in the Columns list and click the Paste Column button.
- To transfer a column as a parameter as in a parameterized query, select a column in the Columns list and click the Paste Parameter button.
- Select the operator you need in the Operators drop-down list and click the Paste button. Every Where clause requires at least one operator.
- If your query requires a function, select the function you need in the Functions drop-down list and click the Paste button.
By pasting selections, you are building a Where clause. You can also directly edit the text in the Where Clause box to complete your query. For example, suppose you are building a Where clause like this:
WHERE COUNTRY='USA'
You would select and paste the COUNTRY column and the = operator. To complete the query, you would type in the data value directly, which in this case is 'USA'.
When you are satisfied with your Where clause, click the Apply button. The Where clause is added to the entire SQL SELECT statement. To view it, click the SQL tab.
Order By tab: Adding an Order By clause
To specify how rows of a query are sorted,
- Click the Order By tab.
- Select the column you want the query sorted by in the Available Columns box and click the button with the > symbol on it to transfer that column to the Order By box.
- Select the sort order direction from the Selected Column Sort Direction options.
The Ascending option sorts the specified column from the smallest value to the greatest, while the Descending option sorts the specified column from the greatest value to the smallest. For example, if the sort column is alphabetical, Ascending sorts the column in alphabetical order and Descending sorts it in reverse alphabetical order.
You can sort the query by multiple columns by transferring more than one column to the Order By box. Select the primary sort column first, then select the second, and so on. For example, if your query includes a Country column and a Customer column and you want to see all the customers from one country together in your query, you would first transfer the Country column to the Order By box, then transfer the Customer column.
Group By tab: Adding a Group By clause
To add a Group By clause to your query, click the Group By tab to display the Group By page.
The Available Columns displays the columns of the currently selected query. The Group By box contains the column names the query will be grouped by. By default, the query is not grouped by any column until you specify one.
To add a Group By clause to your query,
- Select the column you want the query grouped by from the Available Columns list.
- Click the > button to transfer the column name to the Group By box.
A Group By clause is then added to your SQL SELECT statement. To view it, click the SQL tab.
SQL tab: Editing the query directly
At any time while you are using the SQL Builder to create your query, you can view the SQL SELECT statement and edit it directly.
To view the SELECT statement, click the SQL tab. To edit it, make your changes directly to the SELECT Statement field.
Test tab: Testing your query
You can view the results of your query in the SQL Builder.
To see the results of the query you are building,
- Click the Test tab.
- Click the Execute Query button.
If your query is a parameterized query, a Specify Parameter Values dialog box appears so you may enter the values for each parameter. When you choose OK, the query executes and you can see the resulting display of data.
OK
When you click OK, the SQL Statement that was built with the SQL Builder is written to the SQL Statement field of the query property editor.