mapping parameter

In the database, the field rt.calendar_date is VARCHAR2. The alias/field, 'hours' seems to work with the parameter, but the alias/field, 'datess' does not. I haven't been able to figure out why. I have tried so many different things and different ways, changing data type of the field and parameters, and so on, but nothing works. I've tried to do $X{BETWEEN, datess, StartDate, EndDate}, but no success. Anyone know why one works, but other doesn't?

Query below:

to_date(rt.calendar_date||rt.TIME_5_MINUTE, 'yyyymmddhh24:mi') sample_date_time
, rt.lane_type
, sum(rt.volume) volume
, avg(rt.speed) speed
, s.direction
, s.segment_desc
, s.station_desc
, rt.calendar_date
, to_date(rt.calendar_date,  'yyyy/mm/dd hh24:mi:ss') datess
, to_char(to_date(rt.calendar_date,  'yyyy/mm/dd hh24:mi:ss'), 'HH:MI AM')  hours
, to_char(TO_DATE(rt.calendar_date, 'yyyy/mm/dd') , 'DAY') calender_date_day
from dph_dashboard.raw_traffic_view rt
where s.station_id = rt.station_id
and rt.day_of_week_number  = 2
AND to_date(rt.CALENDAR_DATE, 'yyyymmddhh24:mi') >= '01-feb-2017'
and to_date(rt.CALENDAR_DATE, 'yyyymmddhh24:mi') < '28-feb-2017'
and ( $P{StartDate} is null or  $P{StartDate} = datess)
   and ($X{BETWEEN, hours, TimeFrom, TimeTo})
   and ($X{IN, calender_date_day, DaysOfWeek} ) 
   and ($X{IN, direction, Direction})
   and ($X{IN, segment_desc, Segment})
   and ($X{IN, station_desc, Station})
group by  to_date(rt.calendar_date||rt.TIME_5_MINUTE, 'yyyymmddhh24:mi'), 
to_char(to_date(rt.calendar_date,  'yyyy/mm/dd hh24:mi:ss'), 'HH:MI AM'),
to_char(TO_DATE(rt.calendar_date, 'yyyy/mm/dd') , 'DAY'),
rt.lane_type, s.station_desc, s.direction, s.segment_desc, rt.calendar_date,
to_date(rt.calendar_date,  'yyyy/mm/dd hh24:mi:ss')
ewallace's picture
Joined: Oct 7 2016 - 11:31am
Last seen: 6 years 5 months ago

0 Answers:

No answers yet