Query Builder date ranges and bind variables in Toad Data Point

This is Shannon Moore with Quest Software. Today, I'm going to show you some of the new features and enhancements that we have added to the Query Builder and this release of Toad for Data Analysts. We introduced the ability to add data ranges to a query a couple of releases ago, but there wasn't a way to define when a quarter or period began. In this release, we've added customizable calendar types for date ranges. This includes fiscal, academic, or custom calendars so that you can modify the state ranges to meet your business needs. So let's take a look at the fiscal calendar. I had already have an Order Date column selected. So I click the Where Condition for that column. Then under calendar, I select Fiscal and click the Edit Calendar button. And you'll notice that my current start month is March. My fiscal calendar type is normal. Fiscal mode, first day of the month, and week starts on Sunday. Let's preview the calendar as it currently is. OK, if I change my start month to January, notice that the calendar changes. And my fiscal calendar type, I use a 445 calendar. So I'll select that. So if I click Apply, now my fiscal calendar is the default. And then if I select the last quarter, this will be based on the new values. You can select similar options for the academic calendar as well. Let's check that out. So go back to where condition. Select academic. Edit the calendar. You can see we have periods, we four periods currently. Beginning with January, starting the week on Sunday. And you can change that to, say, six periods and it will update as well. If for some reason the other calendar formats do not suit your needs, you can also define a custom calendar. And for that you select Custom. Click Edit Calendar. And right now, it has one period. Let's say we had three periods, you can rename it to My Calendar. And then you would just select the date from and to. And you can set it up however you need to set it up for your [INAUDIBLE]. Now, one of those challenges with the date range format in the past was there wasn't a way to use the date range syntax in other applications, such a SQL Plus. And I'll show you that we still have our original fiscal where condition date range. And in the past, what you would see would just be ORDERS.ORDER_DATE equals calendar fiscal last quarter, but that's all, you could see with that information. So there was no way to tell what the underlying syntax was for it. So for this release, what we've done is we've added a link. Now, if you click this link, you can hover over it and see the value. But if you actually click it, a dialog box opens and you can actually copy and paste that into another location. Now, another great enhancement that we've added to the Where Condition window is to automatically load the bind variable name in the form tab for you. So let's look at that now. If you click the Where Condition. Select the Form tab. And notice the colon ORDER_DATE is selected automatically. In the past, you would have had to enter that in that field. So if we click OK and then run the query, the Bind Variables window pops up. Now, the great thing with this is we've also added an enhancement in this field. In the previous release, you had to manually enter the date time stamp values in the value field. And sometimes that can be a little tricky to remember, especially if you can't recall what your system format is. Well, now you have a pop up calendar that you can use. And let's look at that now. Now, you would think this is a normal calendar, but the really great thing in addition to just selecting something and using the arrows or the time, you can also zoom in or zoom out. In this case, if you click October, and we'll zoom out and let you see all of the months. If you click the 2010, and it will zoom out again and then let you select the year. This is a really fast way to look for a year. Let's say you need something from 2009 or 2000. It's much easier than to browse to and find that date and time. You click OK. And there you go. And there was no value for that, but normally there would be. Now, note that this Bind Variable Calendar feature is also available in the Editor window. For Oracle users, we fixed a bug for the last value for a date or date time data type was not remembered in the Bind Variable window. And that was especially frustrating when you were having to manually type in the date. So let's look at that. So if I have my bind variable value already set that we set in the calendar, if I run the query again, notice it's already selected. Much easier than having to retype that. So that's another great enhancement that I think everybody's going to really appreciate for Oracle users. Now, another great enhancement that we've added-- let me get back to the Diagram window. So if you select Add All Columns, and let's select Add All Columns for this. Now, in previous releases, if you want to group by and a group by clause, what you would have to do is manually select each one that you wanted to group. And sometimes, you want to group all of the columns that you've selected. So for this release, and you'll notice that you'll have a little Group By symbol there. For this release, what we've added if you right-click in the Diagram pane, not on the table itself. And you select Add Group By. What it's going to do