Querying Data Result Sets Internally in Toad Data Point

This should be a quick question I hope. I’m just trying to see if there’s a way to query results of a data set internally in toad data point? For example a query modeller result set, querying that to break out data from it, as opposed to exporting that to local storage or sheet/db, whatever and then linking back to that to query that result set? This is a major question that we’ve been asked from newer people in our company using the product and at this point, I dont know of any other way to query data to result sets outside of exporting and then linking and querying that exported data.

Last thing, I notice that when a query modeller file is created (.tsm) it prompts you to save the results. Therefore, I’m not sure where those results are being saved, if they are stored in the .tsm or stored elsewhere, but maybe there’s some way to query the results of the .tsm file somehow?

I just wanted to get confirmation on this before telling others, as well as building lots of processes without knowing if data can be manipulated in toad result sets to run extra operations or queries against that result set that has not been exported.

Chad King

To Chad.King:

Thank you for posting in Toad World again!

It sounds like you're attempting to construct a really complex query and you're currently using two passes in order to get the job done. I will require some additional information to see if such a request could be constructed through our Query Builder with one pass. If you could give us the SQL Query that is generated for each pass, we will have a better idea of what you'd like to accomplish. You can find the generated query in the Query Builder's "Query" bottom tab:

4401.queryTab.png

The .tsm file prompts to save the results. When you save it, it embeds the result set in the "Results" tab. When you open the .tsm file, you can view the results again without having to execute the query!

Regrettably, there is no feature currently which supports querying from the saved result set.

I look forward to your response,

-Joshua Liong

Joshua, I think you again for the response :). I believe the very end of your response answered my question pretty clearly. I was trying to confirm that Data Point essentially is a shell program that requires everything to be essentially saved outside of the application and not inside. Therefore, I was just wondering if there was any way for storage and re-querying of information already pulled, such as like we discussed, result sets of a query. I can imagine a simple example existing of a list of customers that have certain equipment. The business need would be to pull a distinct list of the customers and a separate distinct listing of their equipment. Therefore, without having to pull two separate queries/exports against the database, my thought is to pull one query with the customers and their equipment, then would like to pull one query/export of that result set for distinct account numbers, then one query/export of that result set for distinct equipment information. At this point, i just have the data exporting to an access db for storage, then link to that db, then query the table created for each individual report i need based on that result set. Is that the ideal way you would think to do this, or can you think of something else that could be used to hold that data set for the separate inside of the datapoint application?

Thanks again for your time.

Chad

Chad, we don’t have anyway to capture a result set and requery except to save to local storage. We do however, have support for reusable queries. These are Toad views. Here you can save a query in Toad and then drag it onto the query builder as a table. In this manner you can reuse common result sets, but without retaining the data.

Debbie

Thank you Debbie for the confirmation. I did play with the toad views and they are a great option, but to me they are essentially and “in line” query, whereas even though the query is saved, the results like you say, are not saved. So essentially when querying against that toad view, the toad view is still querying the database on the backend from what I can tell, which essentially still uses the same querying resources as running the query against whatever db multiple times. Am I correct in saying that? Regardless, this is extremely useful information and its what we wanted to confirm before making major processes and ensuring there is no storage for query results in toad, but rather through local storage/db’s/excel, etc. Thanks!

Chad

Yes, you have correct idea. Toad Views query the data base and are used for inline subqueries or joins