query works in db platform but not in jasper


My query works in the database platform (SQL Developer is what I use.), but when I plug it in jasper, it complains about "Caused by: java.sql.SQLDataException: ORA-01861: literal does not match format string", and I can't figure out why. It was working fine before I put in the 2nd table, along with the station field. Any ideas?

select sample_date_time, 
       sum(case when lane_type = 'EL' then volume end) volume_el,
       sum(case when lane_type = 'EL' then speed end) speed_el,
       sum(case when lane_type = 'GP' then volume end) volume_gp,
       sum(case when lane_type = 'GP' then speed end) speed_gp,
  from (select rt.calendar_date || rt.time_1_hour sample_date_time,
               sum(rt.volume) volume,
               avg(rt.speed) speed,
          from dph_dashboard.raw_traffic_view rt,
           dph_dashboard.STATIONS S
         where s.station_id = rt.station_id
         and rt.station_id in (7,8,9,10,11,12,19,20,21,22,23,24,31,32,371,33,34,35,41,42,43,44,45,46,53,54,55,56,57,58,64,
           and rt.sample_date >= '01-feb-2017'
           and rt.sample_date <= '28-feb-2017' 
           and rt.day_of_week_number in (2,3,4,5,6)
           and to_date(rt.time_5_minute, 'hh24:mi') >=  to_date('05:00', 'hh24:mi')  --time from
           and to_date(rt.time_5_minute, 'hh24:mi') <=  to_date('20:00', 'hh24:mi')   --time to
         group by calendar_date || time_1_hour, lane_type, s.station_desc)
where to_date(sample_date_time, 'yyyymmddhh24:mi') >= '01-feb-2017'
   and to_date(sample_date_time, 'yyyymmddhh24:mi') < '28-feb-2017'
group by sample_date_time, station_desc
order by to_date(sample_date_time, 'yyyymmddhh24:mi')
ewallace's picture
Joined: Oct 7 2016 - 11:31am
Last seen: 2 years 3 months ago

I found the issue, it was something to do with the type of the field that was clashing with the parameter.


ewallace - 2 years 5 months ago

0 Answers:

No answers yet