<< Click to Display Table of Contents >>

SQL Query Builder

Top Previous Next

This facility is provided from the SQL Scratchpad and the following dialogs that permit editing of source code:

Create/Modify View

Create/ Modify Materialized View

Packages, Procedures, Functions Source Code

Tables |Triggers code window

Database | Database Triggers code window

Java | Source Code window

It can be used for creating SELECT, INSERT, and UPDATE  statements, as well as for generating cursor for loops. It can be started from Query on the context menu or from the Toolbar, and is represented by the icon SqlBuilder. When the dialog opens, all tables in the current schema are listed, and the workarea to the right of the table list is empty.

SELECT statement and General Observations

By default, SQL Query Builder opens expecting a SELECT statement (see the DML Generation box in the lower left corner). You can choose one or more tables or views from the list at the left, either by highlighting the table, and pressing Include; or by dragging the table name into the workarea at the right, or by double-clicking the table name. If you choose more than one table, foreign-key relationships between tables are automatically drawn. You may want to change the relative position of the tables by dragging them so that they appear in a logical order.

Next, choose the columns that you want to display.  In this example, we have selected three related tables, and several columns from each table:

QB1

 

Any column that is selected in one of the selected tables will automatically be placed in the SELECT statement. If you just want to use it to formulate a WHERE condition, you can clear the Select check box. Each selected table is automatically placed in the bottom window.

At the bottom of the lower window are three optional settings:

Automatic Table Aliasing generates a unique alias for each table

Select Distinct inserts the DISTINCT keyword in a SELECT statement

Expand select list places each selected column on a separate line.

Note that there is a context menu made available by right-clicking on any table or view, below the table or view name:

Add related table

Presents a list of any tables related by foreign key, from which you may select one.

Alias

Allows you to specify an alias for the table/view that will be used in the generated SQL.

Select All

Checks all column names.

Unselect All

Unchecks all column names.

Sort Alphabetically

Sorts the columns in alphabetical order.

Default Order

Sorts the columns in DDL Order.

Remove Table

Removes the table from the workarea.

The Select and Sort options will stay in effect for each table or view each time that you re-enter the Query Builder.

In the above example, a Where clause has been specified for Company Name, and an ascending sort for Company ID and Customer last name.

When we press OK, this dialog closes, and the resulting code is placed at the current insertion point in the code window. Remember that building a statement with Query Builder can be an iterative process. If the statement turns out not to be quite correct, the next time that we enter Query Builder, the dialog appears just as we left it.

Once the statement is built, it can be executed by pressing Ctrl + E or the Execute Query button.

QB2

Notice that an outer join was created between C.ID and CR.COMPANY. In this example, a foreign key constraint is defined between CUSTOMER and COMPANY, but CUSTOMER COMPANY is not a required column. This results in the automatic creation of an outer join in the query.

Cursor "for" loop

You can also build a cursor "for" loop with the Query Builder. This is useful for processing all results produced by a SELECT statement in a PL/SQL block or stored PL/SQL object. Going back to the previous example, let's suppose we wanted to review every FIRSTNAME and LASTNAME in the CUSTOMER table where the where the customer's COMPANY ID was less than or equal to 3: we would then drag the two tables into main window, select the two columns from the CUSTOMER table, and let Hora generate the WHERE clause by checking the ID column of the COMPANY table. Then, since we don't want to include COMPANY_ID in the for loop, we deselect it. Here is the result

 

QB3

 

And here is the resulting PL/SQL code:

QB4

INSERT Statement

The INSERT statement in Query Builder works only with a single table. You choose the table into which you want to insert data and the columns that you want to populate. Most of the columns in the lower window are not used with INSERT.

After selecting the columns to be populated, press OK, and an INSERT like the following one will be generated in the Scratchpad's code window:

QB7

When you execute the statement using Execute Query, a dialog that prompts you for the value of each of the bind variables will be shown.

 

UPDATE Statement

Query Builder can also construct an UPDATE statement. Here is an example of when this might be useful: Many rows in a COMPANY table contain several different variations of a province abbreviation that now needs to be updated to "QC" to conform to new standards. These rows are scattered all over the table; so a one-by-one update would not be effective. The country code for Canada in our tables is 22. We need some WHERE clauses in the UPDATE statement; so we use Query Builder as follows:

QB5

 

This produces the following result:

QB6

When we execute the query, we are prompted for a replacement string for the bind variable :STATE_PROV, and we then enter the new value "QC".