I’m new to TDA but I don’t see how this feature is provided in TOAD. IS there a way for TOAD to provide all the unique values in a column so they can be selected to fill in the criteria of an IN statement?
In AQT there was an option to retrieve unique values from a selected column. I could then browse and do a ctrl-left click to select those values I wanted to show up in my IN statement without having to type them all in.
Can this be done in TDA?
We’ve had the same issue with TDA. It appears to only allow you to select one item for the IN condition. You can manually type a comma-separated list of values, but that can be time-consuming, especially for time/date values.
We’ve brought this up with Quest Support and are hoping it will be in available in a future release.
-Alan
Okay, thanks for the reply. Let’s hope they add it in a future release.
In the Query Builder the Where condition window has a drop down that lists the unique values for a column.
Debbie
True, but you can only select one item from the drop-down list. This makes sense for = > < etc., but not for an IN or NOT IN condition where you want to choose multiple values.
I couldn’t tell if you knew about it for finding what the distinct values are.
Your are correct on building IN clauses, and as Alan noted we have this noted to handle. Thanks, for your comments.
If multi-select could be enabled for ALL picklists, esp in the codeinsight or DOTLOOKUP feature, I think that would be a huge productivity enhancement for sql coders.
For example, being able to select more than 1 column name for a SELECT clause, or being able to select more than one table/view for a FROM.
I’ve just re-tested this in TDA 2.5.0.813 beta and it looks like this hasn’t been incorporated yet – any update on this being available before the next release?
It is scheduled to be in TDA 3.0. The change request number is CR65,446. Sorry it didn’t make this release. So many good enhancements to choose from:)
can do with SQL
SELECT * FROM APL_M5B844
WHERE LINE_REC_PO_NBR IN (SELECT DISTINCT BASE_REC_PO_NBR FROM APB_M5B844)
That's unfortunately this won't make it into the next release -- I just spoke with our users about finally switching over from BrioQuery to TDA and this is one of the key problems that is preventing them from doing so. Looks like I'm back to looking at other solutions.
For the record, the first attached image is exactly what we're looking for. It's from Toad for Oracle's schema browser data grid filter.
By comparison, in TDA I can get a list of values and choose one value, then I have to type the rest of the values in the box. After which, the where clause looks something like this:
WHERE ("V_$SESSION".LOGON_TIME IN
('8/4/2009 3:21:21 AM', '8/4/2009 3:21:22 AM'))
When I execute the query I get:
Toad for Data Analysts Beta: ORA-01843: not a valid month
due to TDA not putting in the to_date function properly (I get the same results for only one value as well) so I have to manually put in to_date functions on all of the time/date values in the list (it works fine if I use the "=" operator instead of the "IN" operator.)
I'm not sure why this is considered an enhancement and not a bug fix -- seems like a lot of manual work to get the query to execute, I might as well just write the query manually.
Thanks,
Alan
That's unfortunately this won't make it into the next release -- I just spoke with our users about finally switching over from BrioQuery to TDA and this is one of the key problems that is preventing them from doing so. Looks like I'm back to looking at other solutions.
For the record, the first attached image is exactly what we're looking for. It's from Toad for Oracle's schema browser data grid filter.
By comparison, in TDA I can get a list of values and choose one value, then I have to type the rest of the values in the box. After which, the where clause looks something like this:
WHERE ("V_$SESSION".LOGON_TIME IN
('8/4/2009 3:21:21 AM', '8/4/2009 3:21:22 AM'))
When I execute the query I get:
Toad for Data Analysts Beta: ORA-01843: not a valid month
due to TDA not putting in the to_date function properly (I get the same results for only one value as well) so I have to manually put in to_date functions on all of the time/date values in the list (it works fine if I use the "=" operator instead of the "IN" operator.)
I'm not sure why this is considered an enhancement and not a bug fix -- seems like a lot of manual work to get the query to execute, I might as well just write the query manually.
Thanks,
Alan
I am sorry to hear your response. I really appreciate your screen shots as it really shows how easy it is.
On the other hand TDA has added the Date Range tab which offers date ranges using every day phrases. This is not available in Toad for Oracle and is quite handy for Analysts. Toad for Oracles users do want more detailed and finer grained access to queried data. This is just to explain to you where we have put our focus.
Your time has not be lost in vain and you will see these enhancements (bug fixes…) in TDA 3.0.
Thanks,
Debbie
