How to create and utilize parameters in queries in Toad Data Point

This video shows how to create and utilize parameters in queries in Toad Data Point. Solicit inputs by utilizing these parameters when posting views to Toad Intelligence Central, and reduce the amount of data that needs to be shared.

Hello, folks. My name is Robert Pound. And I am a Product Technologist for Quest. This video will illustrate the use of parameters in queries in Toad Data Point. I will show how these parameters can be used when publishing to Toad Intelligence Central. So on screen, I have a filter that's being filtered by population, in this case. And as you can see, when it's run, it returns 192 rows.
So if I wanted to have a user input for this query similar to a stored procedure, if I remove the filter and simply put colon and then my variable, in this case, My Pop, my population, when I run this query in the Editor, it will prompt me for a user input. And since I've read it in the past, it's giving me the default of 100 million. But let's just go with the 100,000 that I had in the original query.
And of course, it will return the same 192 rows. So you can also use this in the Query Builder. So looking at the same table, if we choose to filter the same population, we can choose our greater than or equal to. And by default, it is put in its own variable, Population. This is where we could put in a constant, but I'm going to leave this population variable as is just to show you that you can name the variable whatever you want.
And if we look in the query real quick, you can see that it's updated it to the variable name. So if I go ahead and execute this, it will again prompt me and we'll put a few extra zeros in here. Looking for countries above 100 million. And click OK.
And it's going to, of course, bring back a significantly smaller list of countries. So from here, regardless of whether it's Query Builder or in the Editor, you right click in the data grid itself if you want to publish this. So assuming you have a connection to Toad Intelligence Central Server, you can right click, choose Send To, and Publish Data. We're going to go ahead and choose a view. And we're going to name this Demo Country Data.
And we could do something like Input Pop just to let us know that it does have an input. And we'll drop this into the Demo folder. And we could put a description if we want.
I'm going to go ahead and leave sharing with everyone checked. And let's go ahead and set the authentication. In this case, you don't have to, but that's what I'm doing here.
I'm sharing out my authentication with the database. And if we preview the SQL, we can see the variable has changed. And that's due to how Toad Intelligence Central Server handles variables, so don't be alarmed. But everything should be good to go. So let me go ahead and publish this Demo Country Data. And it's been published.
So from here, I could simply click on this. And it will give me a view of the data. And it's asking me for first time connection whether I want to use the shared authentication or use my own authentication. I'll use the shared authentication. So we can see the information about this query and the sample data.
So if I wanted to use this view now in my data, I could right click and Generate SQL and, in this case, Select Statement. So we don't see a variable here, but the underlying code is going to prompt me for an input. So now I can enter in my value and let's, in this case, just put 10 countries that have a population of 10 or greater, which is going to be all of them I assume. And now we get back different results.
So that's how you access the view that has been parameterized in Toad Intelligence Central Server. You can also use the web service. So for those non-Toad users, let me go ahead and switch to a different system.
And you can see my No Toad or non-Toad user. Let me go ahead and log in. And we can see our world population input. So we can see the column names here. And if we wanted to look at the query, we could do that. And if we want to download the data as a non-Toad user, we can simply download. And it will again prompt us for our input. And we can modify it however we want, 10,000, and download the data.
And it will ask us if we want to actually download it now that it's ready for download. And we click Yes. And I'll go ahead and open it. And it's a list of many countries because we limited it to 10,000.
And that's all I had to show you for today. Hopefully, this video helped you understand a little bit more about parameters in Toad Intelligence Central Server and Toad Data Point. For more information on this product, you can visit www.Quest.com/products/Toad-Data-Point. You can also check out the community-driven innovation going on at www.ToadWorld.com. Thank you and have a great day.