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

[kazeopeia joshi]

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]

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