SQL interpreter issues with Oracle

[2006 IR Open Dicussion]

By: Matthew Richardson - richmlpdx

SQL interpreter issues with Oracle

2006-05-10 11:45

I am trying to run use the following SQL Query, however the iReports is doing something bad to the SQL. If I copy the SQL to the clipboard and run the query manually, everything runs fine.

 

SELECT DISTINCT x, y, z

FROM transactions, users

WHERE users.userid = transactions.userid

AND customer=$P{customer}

AND TRUNC(cdate) >= TO_DATE($P{startDate})

AND TRUNC(cdate) <= TO_DATE($P{endDate})

 

Inclusion of two "TRUNC(cdate)" clauses generates an "SQL Problems: ORA-01858: a non-numeric character was found where a numeric was expected."

error. This SQL runs fine in the Oracle SQL Plus Worksheet.

 

 

 

 

By: develito - devel2k5

RE: SQL interpreter issues with Oracle

2006-05-10 14:46

Hello ..

I´m not expert in Jasperreport but maybe I can Help You..

 

I don't think that the clauses "TRUNC" generate the error.. The problem (in my point of view) is the parameter "$P{endDate}"..

You try to use :

to_date( $P{fecha}, 'yyyy/mm/dd')

The parameter $P{fecha} is data type "String".. and look the 'yyyy/mm/dd' field.. this is the format date..

 

I reproduced your error and I believe that the problem is the data types of the parameters ..