This chapter is from the book
This chapter is from the book
This chapter is from the book
Query Builder
Toad has a nice Query Builder feature. This feature enables SQL code to be built using mouse operations. This feature is excellent for power users, analysts, and managers using Toad to create reports and access data but who have little knowledge of the SQL language.
The Query Modeler button is on the standard toolbar (the top menu bar, see Figure 3.32). You can also access it via Database → Report → Query Builder, and the button also appears on the ER Diagrammer panel.
FIGURE 3.32 Query Builder button
The object palette appears on the right (if it doesn’t automatically appear, click View → Object Palette). The various items that can be included in a select statement appear on the left. Figure 3.33 shows the canvas with both the EMP table and the DEPT table. These were added using either a double-click operation on each in the object palette or a drag and drop mouse operation from the object palette to the canvas. Notice how Toad built the SQL in the bottom Generated Query tab. As you click on the column names, Query Builder will adjust the SQL! The query can be executed from here, saved, and even moved to a tab in the SQL Editor!
)
FIGURE 3.33 Query Builder in action
To add WHERE, GROUP BY, HAVING, UNION, and the other clauses, simply double-click the syntax item of interest and use mouse operations to draw in the options you desire. Figure 3.34 shows the Where Conditions clause panel, accessed by double-clicking the Where in the left Query Builder panel (titled Query Browser). Buttons on the toolbar control and bring up these same wizards. You can also build WHERE clauses by simply dragging a column of interest from the canvas object and drop it on the WHERE clause. This feature works for the other items in the left panel as well. If the operation is a valid request, a wizard will appear to finish the syntax.
)
FIGURE 3.34 Query Builder using the Where item
Notice in Figure 3.35 that some columns were selected in the EMP and DEPT tables in the canvas and that the syntax was adjusted automatically. Also notice that the SQL syntax is using ANSI joins. Query Builder is an excellent tool for converting from either ANSI SQL to standard Oracle SQL or vice versa! Click the Ansi Join Syntax button, shown by the cursor in Figure 3.35, and you get the SQL in Figure 3.36!
)
FIGURE 3.35 Query Builder ANSI SQL
)
FIGURE 3.36 Query Builder Oracle SQL
Figure 3.37 shows the Generated Query panel control buttons. The first two buttons enable the generated SQL to be copied to either the editor (a new tab) or to the clipboard. The SQL Optimize button is a purchased feature of Toad (it enables the SQL to be automatically tuned). The Execute Query button will run the SQL from the Query Builder interface and populate the other two tabs present. The Add Table name button enables all the columns to be qualified. The next two buttons make it possible for either an * or the distinct clause to be added to the generated SQL.
)
FIGURE 3.37 Generated Query Panel control buttons
The Update Diagram and Update SQL can be useful buttons. You can make changes to the SQL via typing or you can paste a SQL statement into this generated SQL area and click the Update Diagram button, and the diagram in the canvas will be updated. Likewise, you can make changes to the diagram in the canvas and click the Update SQL button, and the changes will be made to your generated SQL. The Ansi button is a toggle; it will rewrite your SQL using ANSI join syntax if clicked. The Add Schema Name to Tables and Add Table Names to Column buttons are also toggle buttons. If clicked down, the SQL text will be adjusted accordingly. The final two buttons (Run Query in Threads and Allow Modify Query Results) pertain to how you would like to execute the SQL and possibly have the ability to change results from within the Query Builder environment.
You can add your own subqueries and calculations. There are right-click options under each object on the canvas, different options when right-clicking the object header on the canvas, and others. Calculations are easy to add by either
Right-clicking the select line on the left Query Builder panel and selecting Add New Expression Column as shown in Figure 3.38.
FIGURE 3.38 Adding calculations using Query Builder
- Adjusting the text in the Generated Query and clicking the Update Diagram button.
)
){kind=link}
Options for each of the other items appear in this left panel as well. Just right-click and check out the various options. Right-clicking the columns on the canvas enables column aliases to be added. Right-clicking the table name enables an alias to be defined. Remember that a button on the Generated Query panel can add the table name or alias to all the columns in the SQL (a good idea).
There isn’t any SQL that cannot be built using Query Builder. Figure 3.38 shows a calculated column, sal * 1.1, being added to the query.