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:20:37 GMT
On 2nd thought.. I googled the answer

ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed
because the character string was not a valid numeric literal. Only numeric
fields or character fields containing numeric data may be used in arithmetic
functions or expressions. Only numeric fields may be added to or subtracted
from dates. 

Action: Check the character strings in the function or expression. Check
that they contain only numbers, a sign, a decimal point, and the character
"E" or "e" and retry the operation.

-----Original Message-----
From: Lance Semmens [mailto:lance.semmens@essential.com.au]
Sent: Tuesday, 1 March 2005 3:39 PM
To: Jakarta Commons Users List
Subject: RE: [dbutils] Sending large set of numbers
Importance: Low


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

---------------------------------------------------------------------
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