SQL Builder

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:

Columns page

The columns page is used for selecting columns to include in your query.

Available Columns

The Available Columns list displays a list of all tables in the database, and all of the columns in each table.

Distinct

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.

Definition page

The Definition page displays the properties of whatever is selected in the Available Columns list.

Data page

The Data page is available whenever a table is selected, and displays the data from the table in a grid.

Copy

This button copies a table from the list of available tables to the list of selected tables.

Copy All

This button copies all the tables from the list of available tables to the list of selected tables.

Aggregate

This button displays the Aggregate Functions dialog box.

Remove

This button removes a table from the list of selected tables.

Remove All

This button removes all the tables from the list of selected tables.

Selected Columns

The Selected Columns list displays the columns that are currently selected to be included in the query.

Where page

To add a where clause to your SQL query, click the Where tab to display the Where page.

Columns

The Columns list on the left contains the columns of tables in the currently selected query.

Paste Column

The Paste Column button is used to paste the name of the selected column into the where clause.

Paste Parameter

The Paste Parameter button is used to paste the name of the selected column into the where clause as a parameter.

Operators

Select the operator you need in the Operators drop-down list and click the Paste button. Every Where clause requires at least one operator.

Functions

If your query requires a function, select the function you need in the Functions drop-down list and click the Paste button.

Where Clause

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'.

Apply

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 page

The Order By page is used to specify how rows of a query are sorted.

Available Columns

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.

Order By

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.

Selected Column Sort Direction

Select the sort order direction from the Selected Column Sort Direction options.

Ascending

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.

Descending

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.

Group By page

To add a Group By clause to your query, click the Group By tab to display the Group By page.

Available Columns

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.

Group By

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.

SQL page

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.

Test page

The Test page is used to view the results of your query in the SQL Builder.

Execute Query

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.