Error executing SQL statement for :

[sionut1984]

Hy!
when i try to run a report that contain this query :

select
     ann.id ID,
     ann.name PROKYRIXI

from
     announcements ann    
where
    to_char(ann.deadline,'YYYY')<= $P{endYear} and
    to_char(ann.begin_date,'YYYY')>= $P{startYear}

i receive this type of error:

Error filling print... Error executing SQL statement for : approvedAmount

.....

Caused by: java.sql.SQLException: ORA-01722: invalid number

...

Interesting is if a run this report on Db that is local it works fine but when I try on DB that is in the network I received this error

Thanks

 

[lukus]

Hi sionut1984,

 

Your SQL will fail due to the way you are using your parameters.  Defining parameters in the 'Report Query' window as $P{my_parameter} will cause the parameter to be written with '"' wrapped round the content.  To avoid this you need to use the '!' in the definition.  Simply defing your query as follows should solve the problem.

 

select
     ann.id ID,
     ann.name PROKYRIXI

from
     announcements ann    
where
    to_char(ann.deadline,'YYYY')<= $P!{endYear} and
    to_char(ann.begin_date,'YYYY')>= $P!{startYear}

 

Cheers

Lukus

[mdahlman]

Lukus,

 

Using the syntax $P{endYear} causes the parameter to be send as a bound variable. But it does not cause the value to be enclosed in quotes.

 

sionut1984,

My guess is that $P{endYear} is an integer. But this is a string: to_char(ann.begin_date,'YYYY').

 

Oracle tends to be very forgiving about implicit type conversions. But for some reason it doesn't like it in this case. Rewrite the query and/or param to be careful about the data types and it ought to work correctly.

 

Regards,
Matt
Jaspersoft