wood burning stoves 2.0*
The moose likes JDBC and the fly likes sqlException : Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION" Watch "sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION" New topic
Author

sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION

kazeopeia joshi
Greenhorn

Joined: Jul 07, 2011
Posts: 12
Hi everyone,
I really need your help.. please..

I received this sqlException while debugging : Invalid scale size. Cannot be less than zero.
I was executing this script :

select trunc(A.ft_dt) - trunc(sysdate) from table_name A
UNION
select trunc(A.bp_dt) - trunc(sysdate) from other_table_name A

note : FT_DT and BP_DT are both of type DATE
The exception occurs on
CachedRowSet.populate(ResultSet)

The script works fine when ran on pl/sql but not when I'm debugging in netbeans.

I tried the following for work around:
1. added ojdbc14.jar in my library
2. (CASE When trunc(A.ft_dt) - trunc(sysdate) < 0 Then 0 Else trunc(A.ft_dt) - trunc(sysdate) END) -> I did
this because I thought it's because of the negative number that I'm getting when subtracting dates.
3. Also tried decode()
4. I wanted to try
FilteredRowSet frs = new OracleFilteredRowSet()
but i can't find OracleFilteredRowSet in ojdbc14.jar

But the weird and frustrating thing is when I tried running the statements separately... without the UNION,
they both worked so the statements are actually working, but when I put them in UNION the above
sqlException would happen.

I am using Oracle 10g
jdk 1.5
netbeans ide 6.9
Madhan Sundararajan Devaki
Ranch Hand

Joined: Mar 18, 2011
Posts: 312

I believe, the table name alias' are identical (in this case A), which is preventing them to be executed when Union is used, try giving a different table name alias (say B, in the second query - obviously you need to modify the query to use B!)


S.D. MADHAN
Not many get the right opportunity !
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION
 
Similar Threads
JPA Native Query
Merging two query results into one in Oracle
Code to trigger nightly batch job works .. but I don't know why it works
Please Help: CachedRowSetImpl (JSR-114) with Oracle Type4 Driver
returning a cursor from a stored funct