ewallace Posted March 29, 2017 Share Posted March 29, 2017 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:selectto_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_dayfrom dph_dashboard.raw_traffic_view rtJOIN dph_dashboard.STATIONS S ON rt.STATION_ID = s.STATION_IDwhere s.station_id = rt.station_idand rt.day_of_week_number = 2AND 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') Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now