Decode on Where clause (Oralce) give empty report

[Dimas]

I've detected that using decode inside a where clause JasperServer don't recognize it and returns an empty report. I can run the same select with Toad and iReports and they give me results, but not with JasperServer. It's a recognized bug?

 

If I cut this code from the sql JasperServer works ok:

 

where dcp072 = decode(to_char(sysdate,'D'),'5',to_date(to_char(sysdate + 3,'dd/mm/yyyy'),'dd/mm/yyyy'),to_date(to_char(sysdate + 1,'dd/mm/yyyy'),'dd/mm/yyyy'))

[swood]

The decode statement should not be an issue. JasperServer and JasperReports do not change the query, except if you parameterize the query in your report.

 

This might seem basic, but I have to ask...

 

Is the JDBC or JNDI data source for the report in JasperServer set up and does the "test connection" work?

 

Are there any error messages in the log?

 

Are you relying on any parameters for the query? Are they set up on the report unit? As a test, you can print the parameters in the report.

 

 

Sherman

JasperSoft

[SeanDay]

If you say the query works without the decode then this decode must be causing no data to be returned. Are you sure you have data in the resultset that matches the decode statemtent? Do you need to truncate the dcp072 field (it may have a time value as well as date whereas the decode will have a time of 00:00:00).

 

 

Sean