Save time when you query data

Data analysts often use the same criteria as they query data. Whether it’s a monthly, weekly or quarterly report, the data may change but the overall criteria won’t. To easily access and query data, Quest® Toad® Data Point can save the SQL used for any query with the Toad Views functionality. This feature allows you to reuse the SQL and easily share it with others.

This blog will help you get started with Toad Views to access and query data by demonstrating how to save a query and use it to refresh the data upon demand. We’ll cover:

• How do I refresh my data?
• How to I repeat something I did last week?
• How do I share this code with others?

How do I refresh my data?

Toad Views allow you to easily refresh and query data, but first, you must build out your SQL. It could be complex, maybe containing join conditions and calculations across several databases. You need the data to be regularly refreshed with those exact same conditions and calculations. You may also want to share this bit of code with the department, or with others who have a similar need. What you don’t want is to create the SQL again each time you need it.

SQL with conditions and calculations to query data

The above illustration shows some SQL with a join condition and a simple calculation. Your overall need is to check on any fresh data in the future using the exact same requirements.

Within the editor, right click on the SQL and select 'Add to Toad Views' from this menu.

Creating a Toad View

Give it a name. The Toad View manager is what allows you to re-access this code. This panel is also available under the menu item 'View > Toad Views'.

Toad Views allows you to save and rerun this SQL whenever you need to query data.

Name a Toad View

How to I repeat something I did last week?

To reuse a Toad View, simply include it in your SQL statement. The Toad View acts like another table in your database but it is actually a stored SQL statement. You can blend it in with other code, and even make a Toad View out of the new mix.

Using a Toad View to query data

How do I share this code with others?

Currently, there isn't a straightforward way to share this SQL as a Toad View. However, it is easy enough to share the SQL and publish it to Toad Intelligence Central for the rest of the department to pull, share and query data.

Accessing the SQL to query data in a Toad View

First, you need to get the actual SQL into an editor window. Using the Toad Views Manager, you can copy/paste the SQL from the lower area or click on Edit Toad View and copy/paste the SQL out of that area to another SQL Editor window.

Save the SQL to a file

Save the SQL to a file on your workstation. Use the menu selection File>Save As. Name the file something useful and place it in a location where you can easily find it.

Publish to Toad Intelligence Central

Then, publish this file to Toad Intelligence Central. Use the menu options File>Send to>Publish File.

Notice in this illustration that you can also push the saved SQL to the Shared Toad folder. You can also simply save the SQL (using 'Save As' to the Shared Toad Folder).

Define how to publish the SQL to Toad Intelligence Central

Place it in a folder for common SQL, and make sure the Sharing is set the way you desire. Another upside to using Toad Intelligence Central versus the Toad Shared folder is that all users will be notified there is some new code in Toad Intelligence Central.

Closing

Toad Views are a great way to save any kind of SQL with complex calculations and join conditions so you can easily query data to use again and share with others. Being able to save complex SQL greatly increases your productivity in the future as well as eliminates the possibilities of returning data that differs in selection from the prior pull.

Dan Hotka has several course offerings that use Toad and Toad Data Point.

Related Links:

Blog: Publish Toad Data Point Automation Scripts to Intelligence Central

Blog: How to use Quest® Toad® Data Point to cleanse, transform, save and retrieve data

Blog: Three data preparation challenges that Quest® Toad® Data Point can solve

Blog: How to use Quest® Toad® Data Point to improve data integrity

Blog: What is data preparation and why is it key to the business analyst?

Blog: Publish Toad Data Point Automation Scripts to Intelligence Central