Tips for Using Toad for SQL Query Generation

Toad has always had Query Builder with SQL query generation functionality. This feature is more for the novice to the SQL language and allows people to build robust SQL using just drag and drop operations, check boxes, and selection drop downs. This SQL can be selected and used in other applications and the resulting data grid has all the same features of any data grid in Toad.

This blog will focus on Toad Query Builder and:

• • SQL Query Generation
  • Converting from Oracle SQL to ANSI SQL
  • Calculations
  • Reverse Engineering SQL

SQL Query Generation

The Query Builder is started using this tool bar button.

The above panel shows the Query Builder in action. In the center is the canvas. This is where you drag and drop tables from the Object Palette on the right side. This Object Palette opens automatically when you start the Query Builder. This Object Palette can also be accessed using the menu item View > Object Palette.

The section on the left is the Query Builder SQL options. Some fill in automatically as you drag and drop table or view names to the canvas and make your column selections (check boxes in the table box on the canvas).

You can right click on columns in the table box or on the left-hand side and assign an Alias (think of this as a new column name). If this number field contains a null value, then input the value you would like to show and use for math, such as 0.

Column Alias can be used to give a more descriptive name to a column. For example, we could easily spell out ‘Commission’ for this COMM field, and then the Commission name would appear in the output row set and be exported to any spreadsheets that the data might be exported to.

This illustration shows various EMP and DEPT columns being selected with the check boxes. These will paint into the left side and the SQL in the 'Generated Query' tab in the order in which they were selected.

This illustration shows the SQL in the Generated Query tab as ANSI SQL. Most relational databases support the ANSI (American National Standards Institute) SQL language standards. These examples are being generated from an Oracle database, so Oracle also supports the "old school" syntax.

This illustration appears if you click on the line between the two tables (the actual join indicator). This panel shows the join to be a simple equi-join. Notice the join condition can easily be changed to the other supported join types, such as outer joins, etc.

Executing the SQL will produce a result set in the Query Results tab. If you right click on this row set, you get the standard Toad options for the data, such as 'Send to'. This is where you can start the Report Writer, the Export option where this data can be sent to various Excel spreadsheet options, and more.

The Messages tab shows that the SQL successfully executed and how long the SQL took to return the row set. Had there been an error, the error message would appear in this Messages tab.

Convert from Oracle SQL to ANSI SQL Browser

The ANSI button is now in the up position. Notice the SQL is the standard Oracle join syntax using the equal sign for equi-joins. A "(+)" will signify an outer join condition. No “where” clause at all will cause a Cartesian join, where one row is selected from one table and all the rows from the other, then the next row from one table and again all the rows from another, repeating this sequence until all the rows are returned from the first table.

Calculations

You can add additional columns to your SQL via the Query Builder. These columns can be reformatted dates, date calculations, math calculations and even string manipulation using SQL functions.

To add a new column, right click on the SELECT key word in the left Query properties area. This brings up the next 'Column Properties' palette. If you right click on any of the columns, you can change the column name as it appears in the output (using the Alias), and you can assign substitutions for null values in number fields (as seen above).

This column will be called TOTAL_COMP, as indicated in the Alias field, and will contain the simple math of adding the SAL and COMM fields together.

You can use your mouse to build this entire operation! Start by double clicking on the STUDENT.EMP.SAL field in the Available Columns box. Then, double click on the '+' in the Operations box. Now, select the NVL function (standing for null value, this function substitutes a value if the indicated column contains a null value) out of the Functions box, again, using a double click operation. This NVL feature will then prompt you for the two items it will need: the column that might have a null value, and the value to use if a null value is found. Now, with the ‘Fieldname’ highlighted, double click on the STUDENT.EMP.COMM field in the ‘Available Columns’ box. The highlight will shift to the 2nd field in this NVL function. Enter 0 for this value.

The above panel shows the results of adding our calculated column. Notice the syntax is all correct and this SQL should execute just fine.

Reverse Engineer SQL

Sometimes, it can be easier to make the changes in the Generated SQL tab rather than to hunt down the feature in the left Query Builder tree structure. This is easy to do using the Generated SQL tab and the ability to synchronize the changes to the SQL back into the Diagram and the diagram canvas.

You can make your changes right in the SQL. Notice the line under the tool bar will then appear, telling you the Diagram and SQL Code are not the same anymore. Click on that same bar and the Query Builder will push your changes back into the Diagram!

In this example, I added aliases to each of the columns. Yes, I could have clicked on each column on the left side of the diagram and made the same changes, but I wanted to show that any change you make to the SQL can be easily reflected in the Diagram. Notice the EMP and DEPT table boxes in the canvas now show the new table aliases.

Let's take this ability to change the SQL one step further. Let's say you are rather new to SQL, but you have a SQL statement you would like to modify. Copy that SQL from its original application and paste it into the Generated SQL tab. Then, click on the 'Diagram and SQL Code are not Synchronized' bar to take your SQL and attempt to create all the Diagram and canvas palette items.

In most cases, you can easily do this, and now you can easily make changes.

For novices, SQL query generation via the Toad Query Builder makes it easy to put statements together without manually writing them.

Related Links

Blog: Code analysis: Why PL/SQL code quality matters

Blog: SQL query optimization: Level up your SQL performance tuning

Blog: Supported Oracle 21c new features in Toad for Oracle 15.1

Blog: Best developer tool: An Oracle Ace's favorite things in Toad for Oracle Base

Blog: Dark mode has arrived for Toad for Oracle 15.1

Blog: SQL tools – 8 ways you can’t live (or work) without them

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!