In Part III of this series of blogs, I’ll discuss the Query Builder in Toad for Data Analysts. In my previous entry, I said that I would talk about both the Editor and the Query Builder in Part III. Both features are so rich that I feel that separating the topics would be the best approach. So, we’ll focus on the Query Builder for this topic. 

As you might have guessed, the Query Builder generates SQL syntax. It’s very similar to Toad for Oracle’s Query Builder, but there are some differences you’ll notice when using the TDA (Toad for Data Analysts) flavor. Let’s get started. 

 

 

The most direct way is to use the Tools menu and select Query Builder from the list. There are other methods available, but we’ll stick to using the menus. The Query Builder is a drag and drop type of interface. In order to drag and drop objects onto the Query Builder, you must use the Object Explorer window. Remember, this window is located in the bottom portion of the Navigation Manager window.   Activate the Navigation Manager by selecting the View -> Navigation Manager menus.

 

You can drag single objects or multiple objects from the Object Explorer and drop them onto the Query Builder. Use the “Ctrl” key, on your keyboard, to select multiple objects from the Object Explorer. Please note, where referential integrity is defined in the database, the joins between tables will be generated automatically. You can still create your own joins by simply dragging a column from one table to the column of another table. What’s really neat about this approach is the Query Builder allows me to grab tables from different data sources to use in a query. Remember to select the appropriate data source, from the Navigation Manager window, to refresh the Object Explorer’s list of objects. After that, the process is the same. Simply grab the objects from the appropriate data sources and drop them onto the Query Builder. You will need to create your own joins when working with multiple data sources. Depending on the data sources you’re working with, you may see something similar to the below screen shot.

 

 

 

If I need to alter the joins that are displayed in the Query Builder, all I have to do is double-click on the join lines. The join lines are highlighted by the red box in the below screenshot.

This loads a window that allows you to make appropriate adjustments to the join syntax.

 

 

Once you have the objects you wish to use in your query and have adjusted any join statements, simply check the box to the left columns you wish to include in your query. You’ll notice the bottom portion of the Query Builder will start to change.

 

I use the bottom half of the Query Builder to refine my syntax.  It works like a grid. The columns are the columns I’ve selected from the tables and the rows represent different approaches to refining the query. For example, I can create a WHERE condition, on a column, by simply choosing that row for the appropriate column. In this example, we’ll set a WHERE condition on the Balance column. All I have to do is click the Where Condition row in the Balance column.

If I click the ellipsis, I see the following:

For the WHERE condition, I can choose a very straight-forward approach and use the Form tab to build the condition. The Formula tab, while still a graphical approach, gives me access to much more functionality.

This is like switching to Expert mode in Toad for Oracle’s Query Builder! I’ll create my condition and our grid will update.

 

 

Hopefully, you’ve got the gist of refining your queries using this grid approach. Pay particular attention to working with DATE columns. Toad for Data Analysts gives me the ability to create Date Range hints to save myself the trouble of having to constantly update a query with a proper date range (the last 30 days, for example). Check it out.

To see the SQL syntax that’s being generated, you can select the Query tab on the bottom half of the Query Builder.

The Query Builder window now displays the syntax.

If I prefer to work in the Editor, I can right-mouse click (your best friend!) and send the syntax to the Editor by selecting the “Open in Editor” option. I’m not tied to a graphical approach if I like to get my hands dirty .

To execute your query, go to the Query Builder menu and select Execute SQL Statement. You may have noticed a shortcut key listed in this menu. Yes, it’s F9 and it’s the same shortcut key you’re used to in Toad for Oracle.

Our Query Builder window now displays our results.

 

Remember, right-click is your friend. If you right-click on your results data grid, you can see additional functionality, including the ability to Export your results to a variety of formats. 

 

The next topic will cover functionality in the Toad for Data Analysts Editor. I mean it, this time. Have fun!

0 comment(s) so far...

Login to Add Your Comment