sqlException : "Invalid scale size. Cannot be less than zero." when executing script with UNION
Joined: Jul 07, 2011
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
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
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 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!)