Toad World® Forums

Constant in Where Clause

I am using Toad DP 5.1.0.142
In Query Builder I want to use a txt file to popoulate the where clause. I select In and then Constant and then hit the ellipse to get the file. It populates the box on the where clause tab almost instantly for small file (takes longer for large files) but when I hit OK it can take minutes (a big list can be 5 minutes or so) to populate in the query window so i can run the query. it didn't do this before in an older version. Do i have a setting incorrect?

This is a very powerful feature and I need it to be faster.

Any help would be appreciated.

Thanks

Hi, can you try the latest patch version of Toad Data Point? We are constantly doing changes to our Query Builder.

Hi Jim,

Can you give us an idea of how many entries are in a 'big' file?

Small file is less than 10-15 and this takes more than a minute to populate the query.
Big file could be up to 200 or more and takes more than 4-5 minutes
Never had this problem in version 3

I originally thought it was because the txt file was on our LAN and i am working remotely so i thought it was network delay. Moved it to laptop and have same issue.

NOTE: this is to populate the query not the field on the where clause builder screen.

Sorry – are you in the Visual Query Builder on the Query Tab or in the SQL Editor?

I am using the the Visual Query Builder not SQL Editor. In VQB I put the field in the grid. Then I click on ... to bring up the where clause window. Next I change operand to LIKE, select CONSTANT and hit the ... to pick the file. The field pops and then i hit OK to put it in the grid. It is the transfer to the grid that is very slow.

For what it's worth, I'm running TDP 5.1.7.245, and I can't reproduce the lag that Jim is seeing.
BTW, the ellipsis only appears for operators IN and NOT IN, at least in this version of TDP.

I did two test runs... In Run #1 I built a query with a WHERE clause that used a numbered list (txt) file with 200 numeric entries (separated by RETURNs, not commas) and the query was built almost immediately.

Run #2 had a WHERE clause that used a txt file with 200 string entries, and same thing... query was built quickly.

Gery is correct - ellipsis is for IN and NOT IN. I misstated that i was using LIKE.

Gary's test was great. I have contacted our helpdesk to see about getting an more current version to see if that fixes the issue.

Jim – is this a text file or Excel?

this is a txt file created in Notepad