Feb 8, 2022 8:00:00 AM by Dan Hotka
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:
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
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
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.
Try Toad Data Point for free
Learn how Toad Data Point can help you access and prepare data faster. Seamlessly access more than 50 data sources—both on premises and in the cloud—and switch between these data source with near zero transition times.
Get started with our free 30-day trial.
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.
Written by Dan Hotka
Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari.
Dan's most recent book is Toad for Oracle Unleashed