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,
station_desc
from (select rt.calendar_date || rt.time_1_hour sample_date_time,
rt.lane_type,
sum(rt.volume) volume,
avg(rt.speed) speed,
s.station_desc
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,
65,66,67,68,69,75,76,77,78,79,80,81,87,88,89,90,91,92,99,100,374,101,102,103,109,110,111,112,113,114,121,122,375,
123,124,125,131,132,376,133,134,135,141,142,143,144,145,151,152,153,154,155,161,162,163,164,165)
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')
0 Answers:
No answers yet
I found the issue, it was something to do with the type of the field that was clashing with the parameter.