In Toad for Oracle 17.0.341.1977, the filter capability in Schema Browser -> Users -> Role Grants is unfriendly. There is no filter button in the tab. One must right-click in the data grid and select Filter Data. (Just a minor point.) But, the filter dialog and operation is rather strange.
One would like to have the ability to specify the filter in the same way as other things in SB can be filtered by constructing a where clause. Instead, we are subjected to this unhelpful dialog:
Yes, we can save the filter, but the resulting file is not in the form of SQL but a Toad filter file that is merely a representation of the filter choices in a binary file.
Spool SQL does not give us the resulting where clause either. On refreshing the filtered tab, Spool SQL just gives us the original queries to populate the page, and even though the filter is still applied, the spooled SQL does not reflect this.
Now, I am not such a moron that I could not just write up a where clause that corresponds to the filter specs, but it is a mystery why this filter widget should work so differently from others in Toad. Why should such a method even exist for any filter? Is there some special reason that it is so different here or why it could not be the same as the filter tool for table data?
Am I overlooking something? Is there some place where I can retrieve the corresponding SQL--the query WITH the where clause resulting from the filter?
This is not a filter that I typically use, but a developer on our team brought it to my attention. She wanted to capture the SQL where clause for an elaborate filter on this tab just as a shortcut and found, as I have since, that it did not appear to be possible.
Cheers,
Russ
Hi Russ.
One would like to have the ability to specify the filter in the same way as other things in SB can be filtered by constructing a where clause
Ok, but that's usually for lists of objects in the LHS. We rarely have such filters for RHS schema browser tabs. I'm not saying we shouldn't, just that we aren't being inconsistent here.
That dialog in your screen shot is a generic grid filter. It's in most every grid in Toad. It is not supposed to be aware of SQL. It just checks for values in the underlying dataset based on filter conditions and then shows or hides them.
I guess I could add a view/edit query button, or even a filter button with a GUI, but the "how granted" column of the grid is constructed in our code by processing the results, so I'm not sure you'll be able to do everything you want here. User may just find that tab more usable in tree mode. Then you can ignore DBA or whatever by not expanding that node.
Well, the grid was chosen because of the filter tool which is not available in the tree.
I am not asking you to change anything here. It is not like this is a big thing for us. I just found it curious that it would be impossible to pull the SQL for that. The whole point for us was to write a query based on a where clause derived from the filter. It was not a matter of "what we see" but "what our where clause would be".
Cheers,
Russ
I understand.
When you use the grid filtering, the SQL is not changed. In fact, SQL is not even re-executed (unless you refresh or reload the window). The grid just hides rows which don't pass the filter criteria.
The same filter exists, for example, in the login window. Obviously, that data doesn't come from query results. So the grid filtering just works on the data that's already present in the grid. It's the same filtering mechanism in both places.
Well, bummer. I will call your first reply the solution although it isn't really satisfying. It just means we cannot use the tool in the way that we had hoped.
I understand why the login dialog would work differently since there is no connection to Oracle backing it. That is just filtering Toad's own data. It is just strange that a filter of data derived from an actual query would not work as a where clause.
Anyway...Cheers,
Russ
1 Like
There is this, however. When attempting to pull a "record count" from a filtered grid, the count returned by right-click->Record Count is that of the result set of the underlying hierarchical query. There is no way to get a count of the rows in a filtered grid except by manually counting them, which is subject to error and requires multiple goes to be sure.
Cheers,
Russ
Hi Russ,
I'd call that a bug. I'll fix.
Workaround: Copy/Paste filtered grid to Excel, scroll down to the end of the list to see record count. It's not pretty but better than manual counting.
-John
LOLs. That is what I finally did.
