You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Lance Semmens <la...@essential.com.au> on 2005/03/01 06:08:49 UTC
RE: [dbutils] Sending large set of numbers
I have had cases (on non Oracle DBMS's) where I have exceeded the maximum
number of values in an "IN" clause.
In these cases I have side stepped the problem by unioning multiple "IN"
clauses.
ie
SELECT * FROM foo_vw WHERE sid IN (1111, 2222, 3333)
UNION
SELECT * FROM foo_vw WHERE sid IN (4444, 5555, 6666)
UNION
SELECT * FROM foo_vw WHERE sid IN (7777, 8888, 9999)
NB. I only have 3 members in each "IN" clause (you should be able to have
about 255 or so)
There is a maximum for the number of unions you can use (around 255 or so
under the dbms's i've used)
If you want an infinite number in your "IN" clause, you may need to insert
into a temp table and join to it.
-----Original Message-----
From: David Graham [mailto:grahamdavid1980@yahoo.com]
Sent: Tuesday, 1 March 2005 6:44 AM
To: Jakarta Commons Users List
Subject: Re: [dbutils] Sending large set of numbers
Importance: Low
Possibilities I can think of:
1. SQL statement is too long
2. One of the entries isn't a number
3. Comma in the wrong place
4. The NUMBER column is too small for one of the numbers
David
--- Norris Shelton <no...@yahoo.com> wrote:
> I have the following query
> SELECT *
> FROM foo_vw
> WHERE sid IN
> (1302352,
> ...,
> 1302359)
>
>
> I am getting the following java.sql.SQLException: ORA-01722:
> invalid number
>
> =====
>
> Norris Shelton
> Software Engineer
> Sun Certified Java 1.1 Programmer
> Appriss, Inc.
> ICQ# 26487421
> AIM NorrisEShelton
> YIM norrisshelton
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org