Error Invalid Number with Oracle 12c

[hilaquita]

Hi,

In the following SQL:

SELECT 

CAST(CASE WHEN F.ANTIGUEDAD < (SELECT PARLIMITE FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 4) 

    THEN (SELECT TO_NUMBER(PARVALOR) FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 4) 

    ELSE 

        CASE WHEN F.ANTIGUEDAD < (SELECT PARLIMITE FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 3) 

            THEN (SELECT TO_NUMBER(PARVALOR) FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 3) 

            ELSE 

                CASE WHEN F.ANTIGUEDAD < (SELECT PARLIMITE FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 2) 

                    THEN (SELECT TO_NUMBER(PARVALOR) FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 2) 

                    ELSE (SELECT TO_NUMBER(PARVALOR) FROM PARAMETROS WHERE PARGRUPO = 'COND' AND PARORDEN = 1) 

                END    

            END

    END AS FLOAT) POBTENIDO

FROM FORMCOBERTURA F 

JOIN BALANCENTN BN ON BN.FORMCOBERTURAID = F.ACBID AND BN.BCBAREA = 'R'

WHERE F.ACBID = '61001000091'

 

I want to get the data in decimal, use TO_NUMBER and CAST (XX AS FLOAT) but it does not work.

 

This query runs in ORACLE 12c and works normal, but when I execute it in JasperStudio 6.3, I get error ORA-01722: invalid number.

 

I really have no idea where the error is

[mlopez_1]

You don't need to cast fields, Jaspersoft Studio automatically maps these field to BigDecimal and treat it like a decimal number.

Regards