aspose file tools*
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
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