Hello,
I would like to know if you can with some "Find" command in latest Toad 16.x, somehow look for specific value in current returned record-s of returned recordset, if you don't know the field name?
For example I have tables with more then 100k+ records with 30+ fields and I'm looking for specific value for which I'm not sure if exist in current table or is maybe in some other table which is behind some EBS form.
Right now I drag slider left/right and looking for that value/key, switching to Single Record Viewer and go Down/Up or paste recordset to MS Excel and search there where I can actually find it, if exist, but it's time consuming. Is possible to search also here wih Ctrl+F like in SQL Editor?
Althought Ctrl+F open dialog Grid Columns/Data Find, but I can't find values in fields if I don't select approriate column name on the left. So it doesn't help me.
Thanks for any suggestions about that,
Blacksmith
I don't think even Oracle allows for this, let alone Toad. Readers, correct me if I'm wrong.
For one thing, columns (can) have different data types, so searching for a value (string? date? decimal?) amongst a mix of types is a challenge.
Couple things you might want to try:
- Export the table to a CSV or similar file and search the flat file.
- If you have only 30+ columns, it may not be too unwieldy to do something like:
SELECT *
FROM <\yourtable>
WHERE 'value' IN (col1, col2, ... col31);
Hope this helps.
That SQL in your 2nd step is a good trick Gary.
Here's how you can quickly get all of the VARCHAR2 column names to search through:
- F4 on your table name in the editor
- In the describe dialog, column the "Data Type" column header twice to sort it descending.
- Select all of the columns with VARCHAR2
- Click here:
Now you can paste those column names into the editor to make your query like Gary's.
Fantastic, John, thanks for this awesome time-saver!
Just a quick note, that in most data grids in Toad (not just in the F4 Describe panel), an alternative is to tap on the column chooser, select your desired columns, then right-click to copy the column names to the clipboard.
The snap below shows this for the RHS data tab in the Schema Browser, but can be done in the Editor results, and a number of other places.
Yeah. That's a great way to go from "select *" to "select each column".
Only problem with that is that there's no way to limit it to a particular datatype from there.
Yes, and thanks for mentioning that, John. When I said "desired columns" in my previous response, I was assuming that users would have selected the columns with the desired/like-type datatypes.
The nice thing about John's suggestion (using F4 to bring up the Describe panel) is that it does show the datatypes, so users can sort/filter by datatype to quickly choose the columns desired.
LOVE this forum!
1 Like
Thanks to your all suggestions. Gary your 2nd suggestion with Select is good one.
I think that this could also be "upgraded" in already present Grid Columns/Data Find which know how to search, but only in active selected column on left side.
If there would be for example virtual column like "*" above first field name, then that search could be ran amongst current recordset not only active selected field using option Find Data.
Thanks for the suggestion.
I agree. I've been thinking about how to improve the search for multiple columns too.
Yes, that would be great. Actually only that part is missing in that dialog. And I think we look (at least me) for such values/FK keys quite often and now is time consuming.
Going back to your SCOTT.EMP example.
With FK's - Are you saying, for example, you are looking at SCOTT.EMP, which has DEPTNO, but you want to search by the DNAME value which is in SCOTT.DEPT? Or are you saying you just search for DEPNO values a lot?
That was only simple example to easier explain and test.
Actualy I'm talking about EBS tables or views which have for example 80 fields and often field name behind is not necessary same like label on the form. So it's could be Attribute1, Attribute2, etc, especially in FlexFields where you cannot check that over Diagnostic. And first you have to find field name behind the form so I can use it in my query. And for that purpose I look for that value in Toad Recordset of that source object in Data Grid. So generally I don't know yet if is actually FK or not.
I hope I explained now more clearly, why such dialog with this capability is important. Now is like you can search in MS Word document only for text in Normal Style or specific Headings but not in general. And then you paste everything in free Notepad++ which can do that.
1 Like
I've been looking at this today and it's actually pretty easy to add this functionality.
Normally I don't like to add enhancements this close to release, but this is easy to turn on/off with an option and the code is isolated, so... on Monday's beta and in version 17.0 (due out at the end of October), you'll be able to search any column like this:
The current behavior is to only locate rows in which all specified columns match the search value. There was actually a bug in there (invalid variant operation) on repeated searches, which has been fixed.
And this is even easier...put it on "FIlter Data". Select all columns and just type what you want to search for.
If I remember correctly this used to be copy selected.
Can you please add back that option, so I can select some columns, right click and do a copy selected.
To me the fact that I must do several steps is annoying
- uncheck all
- check the columns to copy
- right click to copy
- check all again
Hi Esko,
I can add back a rt-click -> copy selected, but you don't need it. You can do a "Copy Selected" with just a CTRL+C.
-John
Hi John,
IN my mind it would be much more intuitive to have a rt-click option as you are already doing the selections with the mouse, I being an experienced developer did not realize that CTRL+c would do that
I agree, it's easy to miss that. I'll add it back in.
1 Like
Thanks John. That functionality you've add here for Filter Data and Find data, for would be great. It could be also option just to press Ctrl+A in column list to select all columns. Alas I would not be able to use it or test it until we will get at work new version, because I cannot update my PC w/o admin rights, we don't have, nor I don't have any test PC.
When you mention new beta: at home I don't have TOAD. Can I for example install beta at home for free? And if yes, how long I can use it or will work and where I can download it? Is beta treated like Trial version? Final release is per seat, not per person, as I know, so you cannot have that also for example on home PC.
Hi Blacksmith,
Yes, CTRL+A will work in that dialog to select all.
The beta does not work like a trial. It will only run if you also have the latest non-beta (right now that's 16.3) installed and licensed on your PC. Some people keep the latest version installed and only run the beta.
-John
