commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Lance Semmens <lance.semm...@essential.com.au>
Subject RE: [dbutils] Sending large set of numbers
Date Tue, 01 Mar 2005 05:08:49 GMT
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 <norrisshelton@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


Mime
View raw message