This is a feature of JBuilder Professional and Enterprise.
To open the SQL Builder, click the SQL Builder button from the Query dialog box. 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.
After you have finished building your query, click OK. The SQL Statement that was built with the SQL Builder is written to the SQL Statement field of the query
property editor.
The SQL Builder consists of the following pages:
The columns page is used for selecting columns to include in your query.
The Available Columns list displays a list of all tables in the database, and all of the columns in each table.
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.
The Definition page displays the properties of whatever is selected in the Available Columns list.
The Data page is available whenever a table is selected, and displays the data from the table in a grid.
This button copies a table from the list of available tables to the list of selected tables.
This button copies all the tables from the list of available tables to the list of selected tables.
This button displays the Aggregate Functions dialog box.
This button removes a table from the list of selected tables.
This button removes all the tables from the list of selected tables.
The Selected Columns list displays the columns that are currently selected to be included in the query.
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.
The Paste Column button is used to paste the name of the selected column into the where
clause.
The Paste Parameter button is used to paste the name of the selected column into the where
clause as a parameter.
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.
The Order By page is used to specify how rows of a query are sorted.
The Available Columns list shows the columns that are available for sorting the query. Select a column and click the move buttons to move the column to the Order By list.
The Order By list shows the columns that are currently selected for sorting the query. To remove a column from this list, use the move buttons to move it back to the Available Columns list.
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.
Select the sort order direction from the Selected Column Sort Direction options.
The Ascending radio button sorts the specified column from the smallest value to the greatest. For example, if the sort column is alphabetical, Ascending sorts the column in alphabetical order.
The Descending radio button sorts the specified column from the greatest value to the smallest. For example, if the sort column is alphabetical, Descending sorts it in reverse alphabetical order.
To add a Group By clause to your query, click the Group By tab to display the Group By page.
The Available Columns list shows the columns that are available for grouping the query. Select a column and click the move buttons to move the column to the Group By list.
The Group By list shows the columns that are currently selected for grouping the query. By default, the query is not grouped by any column until you specify one. To remove a column from this list, use the move buttons to move it back to the Available Columns list.
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 SQL Statement field.
The Test page is used to view the results of your query in the SQL Builder.
To test your query, click the Execute Query button. If the query is not parameterized, the results of the query are displayed.
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.