Decode on Where clause (Oralce) give empty report

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'))
Dimas's picture
Joined: Aug 8 2007 - 6:21pm
Last seen: 10 years 1 month ago

2 Answers:

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.

swood's picture
Joined: Jun 21 2006 - 12:48pm
Last seen: 4 years 11 months ago
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).

SeanDay's picture
Joined: Jan 16 2008 - 11:27pm
Last seen: 8 months 1 week ago