How Data Analysts Use Toad for Writing SQL

Where to begin:

  • Every statement you execute gets recorded.
  • By default we remember the last 100 executed.
  • SHIFT + F8 opens the SQL Statement Recall panel (also available under the View menu).
  • ALT + PgUp / Dn will cycle through the most/least recently executed statements
2. Code Snippets
 
In Toad, when making a call to a database provided function I suggest you start using CTRL+SPACEBAR which activates Toad’s Code Snippets.

Toad will allow you to progressively drill down into a category of database function, complete with documentation on how to use them!

Using Favorites:
We can take a SQL query we’ve stored to the SQL Recall and convert it to a “Favorite” code snippet. Or, you can choose to hand code your collection of frequently used snippets of Code. Just use CTRL+N to pull up your list of favorite snippets of code.

3. Code Insight (Dot Lookups)
 

When you want to write a SQL query or make a PL/SQL call, you probably spend a bit of time manually looking up the column names or command arguments required in the Database Browser. Stop doing this right now! From now on, let Toad figure this out for you automatically in the editor.

The basics:

  • schema. – this will pop up a list of all objects in the current schema you may want to type
  • schema.table. – this will pop up a list of columns for a given table. You don’t need to prefix the schema name if the table is in your current schema.
  • schema[A-Z]+CTRL. – If you start typing your object name and hit CTRL. (CTRL key with a period), Toad will pop up a list of objects matching that pattern.

  • Toad waits 0.5 seconds after seeing the ‘.’ or ‘(‘ character before popping up the list of items available for that object. This is configurable down to the millisecond for optimal usage. You can also configure Toad to list the column names in the order they appear in the table, or alphabetize them

  • You can use aliases for your view/table names and the code insight feature will automatically include the alias when bringing back a list of columns. Toad will show you the column comments so you know exactly what you’re looking at.

4. F4 DESC

DESC, or Describe, is a Oracle command that will display the table or view structure, e.g. a list of columns and their datatypes with size and precision. Toad for Data Analysis has its own DESC command available for all of the supported database vendors (Oracle, SQL Server, MySQL, DB2, and for v2.0 – Sybase!) You can invoke it immediately for ANY object in the database by typing that object in the editor, putting you cursor on it, and hitting ‘F4’. You can type away in the editor with the DESC dialogs available to you. Ever find yourself working on a query only to find out you need to consult the existing columns or indexes? Or maybe you just need to see the existing data or even update a row to get your report query to work properly? Don’t go to the Database Browser anymore, just use ‘F4’ for instant gratification.



Get instant access to any database object with the power of the
Database Browser immediately available.

5. Make/Strip Code

If a developer gives you a jumbled mess of Java of Perl scripts that contains the SQL  you need just let Toad for Data Analysis do this work for you. Take any embedded SQL statement and strip away the non-SQL syntax for instant execution in the Editor. This is one of the most over-looked features in Toad. No more REGEX search and replace to get your well-crafted SQL statement ready for your Eclipse or Visual Studio application.

To activate the feature, just use the ‘Editor’ toolbar menu:

Your SQL is now ready to be put into your custom report or spreadsheet.

Configure Toad to use the programming language of your choice

6. Reverse Engineer your SQL to a Query Builder Model
 
This feature has been available in Toad for Oracle for awhile now, and has been frequently requested as a feature in Toad for Data Analysis. Toad for Data Analysis users will be able to take advantage of this feature in v2.0 of the product which is due in a few weeks.
Conclusion
 
Writing SQL by hand can be difficult, whether you’re using a text editor or a visual query tool application like Toad for Data Analysis. I’m planning a blog topic on how to develop your SQL more efficiently using the visual Query Builder. There are other SQL query tools out there but I recommend Toad for any enterprise Data Analyst.