Combining workflow data using Toad Views for Toad Data Point

We get questions from our users of Toad Data Point workbook of when they should use output to local storage, output to Intelligence Central, or output to Toad Views. And in this video, we're going to cover output to Toad Views. So let me first point out what I'm talking about. When you have queries, results sets in your workflow-- so here I've got a workflow one that's showing customer and contact data, and a workflow two, I have a query that showing order data-- as a child step of this query, you can choose an output. Meaning where would I like to put this data so I can use it later in other workflows within this or other workbooks? And in this case, because I'm coming straight off of the query, I have three choices. I have local storage, publish, and Toad View. Now, if I were to do this off of a transformation and cleanse step, I would not have the Toad View option. I would just have local storage and publish. And the same thing is if I was going off of a pivot step. Again, I have local storage and publish. I don't have that Toad View. But if I'm coming straight off of an SQL query, whether that query was designed in the Query Editor or the Query Builder, I have the option of using a Toad View. So let's take a look at this Toad View and see what it means. So first of all, the reason I want to create an output step at all is usually so that I can take the data that's being generated in a particular workflow and use it in another workflow. So for my example here, I'm going to use it within the same workbook, but I've got customer and contact data, and I have order data. And so if I wanted to create a third workflow that combines my customer and contact data with my order data to get a full customer plus order data stream result set how would I do that? Well, one of the ways that you can do that is by going straight off this initial query. If I don't need the customer cleanup steps, I can come straight off this query and I can take the output here and create a Toad View. So this is going to create a Toad View here. I'm going to name this view A Customer View so I know what's in that data. And I'm going to go ahead and generate that view right now. And so what that does is it creates a saved query within my Toad environment that I could use in another step. And so I can go ahead and do the same thing right here off of Workflow 2. I can do it off of the order data. I can add a step here, go to my output and create another Toad View here, and we'll call this Orders. And then we'll go ahead and generate that output. And so now I have two Toad views, I have Customers and Orders. Now, if I wanted to, I could create a whole new workflow here that is Building Result set. And we'll pull it from MySQL server system. Select that. And now that I've got this here, I can grab these views from here. So rather than grabbing tables from MySQL server database, I can just grab these already constructed views, pop them into this environment, draw my connection, pick my fields, and I can execute this query. And now I have a combined data set that's pulling both my customers and my orders together without having to include every single table that I had originally used. I've created views of each one of these queries and created a third query from it. This is Customers and Orders. So that's one way of doing it. Now what made Toad Views work here in this scenario is that both pieces of data, both data items, both customers and contacts and order data, were coming from the same data source. These are both being sourced. I can switch here from SQL servers. So this is coming from MySQL Server system, and this one also is coming from MySQL Server system. So when all of the data sets that you're going to be combining are coming from the same source, that's really the best opportunity to use Toad Views. And also keep in mind that the other qualification you need for Toad Views to really be effective is that you're not doing any post-SQL processing. So in both of these cases I just went through I wasn't using the transforming cleanse output. I was using the result query output, both on my customer and contact data, and my order data. Because remember, when you get to the cleansing routines and you go look at what the outputs are, Toad Views isn't even available there. So Toad Views are great when you're doing SQL straight, meaning you're coming off of your result set and you're pulling it from the same source as the data that you want to combine it with. Then it's a great option.