Hi,
I’m new to this forum. I use Toad For Oracle Base Edition. I use it to query security info in our database. It allows me to query only up to 5000 securities at a time using Or statements. Is there a way to increase that limit? Are there more advanced versions of Toad that have higher limits?
Thanks.
There is no limit on rows of data that are returned - so not sure what problem or issue you are having …
Sorry, not data returned. I can only query up to 5000 securities at a time. The results can be thousands and thousands of rows, no issue there. The input data only takes 5k.
Here’s an example of my query:
SELECT a., b.
FROM table1 a, table2 b
WHERE a.type=b.type
AND ((A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c = ‘NA’)
OR (A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c = ‘NB’)
OR (A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c = 'NC)
OR (A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c = ‘ND’)
OR (A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c = ‘NE’)
.
.
.
.
)
wow - not the best way to write SQL - here’s an exmaple of one potential cleanup:
SELECT a., b.
FROM table1 a, table2 b
WHERE a.type=b.type
AND ((A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’))
…
Another question -if the values are in another table then you’re hard coding a join in syntax - why not use a join instead???
I’m only a novice user I guess because I’m not sure what you mean.
I can’t use “IN” statement because the input limit on those is 1,000.
combine techniques to shorten the SQL - this is really a SQL question and should be on our SQL forum and not really toad per se …
SELECT a., b.
FROM table1 a, table2 b
WHERE a.type=b.type
AND ((A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)
OR (A.issuer_id_c = ‘XXXXX’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’))
Does that list of issuer_id_c’s and issue_alpha_c’s come from another table? If so, you can do, then assuming the table with all those codes is “Table3”, and the column names are the same, you can do something like this. you can add more where clause conditions on table3 if you need to.
select a., b.
from table1 a, table2 b, table3 c
where a.type = b.type
and a.issuer_id_c = c.issuer_id_c
and a.issue_alpha_c = c.issue_alpha_c
that’s exactly what I asked/said in my first reply - seems to me that SQL code is hard coding a possible join …
I know, I was just spelling it out…
From: Bert Scalzo [mailto:bounce-Bert_Scalzo@toadworld.com]
Sent: Thursday, March 13, 2014 10:19 AM
Subject: RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition
RE: Toad for Oracle Base Edition
Reply by Bert Scalzo
that’s exactly what I asked/said in my first reply - seems to me that SQL code is hard coding a possible join …
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
Bert,
I have done that in the past.
SELECT a., b.
FROM table1 a, table2 b
WHERE a.type=b.type
AND ((A.issuer_id_c = ‘62931Z’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’, … up to 1000)
OR (A.issuer_id_c = ‘145258’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)
OR (A.issuer_id_c = ‘685774’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)
OR (A.issuer_id_c = ‘469807’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’)
OR (A.issuer_id_c = ‘778910’ and A.issue_alpha_c IN (‘NA’,‘NB’,‘NC’,‘ND’,‘NE’))
And it only lets me do 5 IN statements (each with up to 1000 id’s) for a total of 5,000. Is this a Toad limit or is my company’s system preventing me from doing this? I would like to be able to query up to 20k.
Thanks for the help.
John,
The issuer_id_c’s and issue_alpha’s are in table1, along with other info.
Thanks for the help
When I try to add more than 1,000 id’s into an IN statement, I get an error about 1,000 being the max.
But when I try to query more than 5,000 id’s (ets say 6 IN statements of 1,000 each) my Toad crashes. I don’t get an error messages, just crashes.
Right, they are in table1, but how do you come up with the list of the thousands that you want to select?
Bert and I are trying to get you to think of a way to make the creation of that list as part of the query (either from data in another table, or a different
where clause condition that doesn’t involve listing out thousands of values).
The 1000 “In list” limit is Oracle’s. I am not sure offhand where the 5000 character limit is coming from.
From: orange99 [mailto:bounce-orange99@toadworld.com]
Sent: Thursday, March 13, 2014 10:27 AM
Subject: RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition
RE: Toad for Oracle Base Edition
Reply by orange99
John,
The issuer_id_c’s and issue_alpha’s are in table1, along with other info.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
I thought of a parser limit, but that’s not the case: 15 times 1100 entries in the IN( ) parse and format well. So that canbe excluded.
I just made a very long query (about a half million characters) trying to get an error, but never got one. It did start to slow down at that point but it still worked. So it doesn’t seem like there is any kind of hard coded limit anywhere in Toad.
When you say “it crashes”, can you be more specific? Toad does disappear? Does it show an error dialog of any kind? If the error dialog says “to see what the error report contains, click here”, then click on the words “click here”, go to the “call stack” tab in the next dialog, check “Copy to Clipboard”, and then click OK. Then post a message with the contents of your clipboard.
John,
No error dialg window. The application just shuts off. One minute it’s running the query, next minute application is closed.
Are you using Toad Base Edition or Pro?
thanks
Pro, but the license just turns on and off certain windows or features within windows. We don’t limit stuff like this.
When an application disappears like that, it usually means that there is a stack overflow somewhere.
What numeric version of Toad are you using?
From: orange99 [mailto:bounce-orange99@toadworld.com]
Sent: Thursday, March 13, 2014 1:08 PM
Subject: RE: [Toad for Oracle - Discussion Forum] Toad for Oracle Base Edition
RE: Toad for Oracle Base Edition
Reply by orange99
John,
No error dialg window. The application just shuts off. One minute it’s running the query, next minute application is closed.
Are you using Toad Base Edition or Pro?
thanks
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
@orange,
I’m curious, but how come such a query in real praxis … what is the business philosophy under that? Could you explain.
Thx
Damir
Toad for Oracle Base
11.0.0.116
Hi Damir,
I’m not sure what you mean.