ewallace Posted March 20, 2017 Share Posted March 20, 2017 I've been stuck on this for awhile. I've tried so many different things, and the report keeps breaking. I'm trying to retrieve the time from S.START_TIME and S.END_TIME, and put it in the report as a range that returns all data between S.START_TIME and S.END_TIME. Query is below - everything seems to work, until this line in the parameters: and (START_TIME between $P{startTime} AND $P{endTime} ). It keeps complaining that START_TIME is an invalid identifier. I've checked the types, and I was told to put the parameters in as String, because the fields are converted using TO_CHAR.. Any help would be GREATLY appreciated! SELECT /* HOT LANE TRAFFIC REPORT */t.agency, t.facility,t.direction,S.START_DATE, S.END_DATE,TO_CHAR(S.START_DATE, 'HH24:MI') START_TIME,TO_CHAR(S.END_DATE, 'HH24:MI') END_TIME,TO_CHAR(S.START_DATE, 'DAY') DAYOFWK,SEG.DESCRIPTION SEGMENT,SE.DESCRIPTION SECTION,SEG.DISPLAY_ORDER DISPLAY_ORDER,S.EL_TOTAL_VOLUME,NVL(T.GP_TOTAL_VOLUME, 0) GP_TOTAL_VOLUME,S.EL_TOTAL_VOLUME + NVL(T.GP_TOTAL_VOLUME, 0) TOTAL_VOLUME,NVL(T.EL_AVG_SPEED, 0) EL_AVG_SPEED,NVL(T.GP_AVG_SPEED, 0) GP_AVG_SPEED,NVL(T.EL_TOTAL_SPEED, 0) EL_TOTAL_SPEED,NVL(T.GP_TOTAL_SPEED, 0) GP_TOTAL_SPEED,NVL(T.GP_TRAF_RECORD, 0) GP_TRAF_RECORD,NVL(T.EL_TRAF_RECORD, 0) EL_TRAF_RECORD,S.EL_VALID_AVI_COUNT,S.EL_INVALID_AVI_COUNT,S.EL_NON_AVI_VOLUME FROM (SELECT a.DISPLAY_DESCRIPTION as agency, F.DISPLAY_DESCRIPTION as facility,d.dire_desc as direction, utl_time.ROUND_TIME(START_DATE, 30) START_DATE, utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE, SEGMENT_ID, SUM(TXN_COUNT) EL_TOTAL_VOLUME, SUM(VALID_AVI_COUNT) EL_VALID_AVI_COUNT, SUM(INVALID_AVI_COUNT) EL_INVALID_AVI_COUNT, SUM(NON_AVI_COUNT) EL_NON_AVI_VOLUME FROM DP_OWNER.HOT_LANE_TRAFFIC_SUMMARY hlts ,rite_common.AGENCIES a ,rite_common.FACILITIES f ,rite_common.PLAZAS p ,rite_common.LANES l ,rite_common.directions d WHERE HLTS.FACILITY_ID = F.FACS_ID and a.AGCY_ID = f.AGCY_ID and f.FACS_ID = p.FACS_ID and l.PLAZ_ID = p.PLAZ_ID and l.dire_id = d.dire_id and trunc(START_DATE) BETWEEN TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT) AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT) GROUP BY utl_time.ROUND_TIME(START_DATE, 30), SEGMENT_ID, a.DISPLAY_DESCRIPTION, F.DISPLAY_DESCRIPTION, d.DIRE_DESC) S LEFT OUTER JOIN (SELECT ag.DISPLAY_DESCRIPTION as agency, FC.DISPLAY_DESCRIPTION as facility, di.dire_desc as direction, utl_time.ROUND_TIME(START_DATE, 30) START_DATE, utl_time.ROUND_TIME(START_DATE, 30) + 1799 / 86400 END_DATE, SEGMENT_ID, SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN AVG_VOLUME ELSE 0 END) GP_TOTAL_VOLUME, SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED END) / SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT END) EL_AVG_SPEED, SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED END) / SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT END) GP_AVG_SPEED, SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TRAF_RECORD_CNT ELSE 0 END) EL_TRAF_RECORD, SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TRAF_RECORD_CNT ELSE 0 END) GP_TRAF_RECORD, SUM(CASE WHEN TMS.IS_MANAGED = 'Y' THEN TOTAL_SPEED ELSE 0 END) EL_TOTAL_SPEED, SUM(CASE WHEN TMS.IS_MANAGED = 'N' THEN TOTAL_SPEED ELSE 0 END) GP_TOTAL_SPEED FROM DP_OWNER.TMS_TRAFFIC_SUMMARY TMS ,rite_common.AGENCIES ag ,rite_common.FACILITIES fc ,rite_common.PLAZAS pl ,rite_common.LANES la ,rite_common.directions di WHERE trunc(START_DATE) BETWEEN TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') AND TO_TIMESTAMP('01/09/2017', 'MM/DD/YYYY HH24:MI:SS.FF3') and TMS.FACILITY_ID = FC.FACS_ID and ag.AGCY_ID = fc.AGCY_ID and fc.FACS_ID = pl.FACS_ID and la.PLAZ_ID = pl.PLAZ_ID and la.DIRE_ID = di.DIRE_ID AND TO_CHAR(START_DATE, 'HH24') >= CAST(REPLACE('ALL', 'ALL', '0') AS INT) AND TO_CHAR(START_DATE, 'HH24') <= CAST(REPLACE('ALL', 'ALL', '23') AS INT) GROUP BY utl_time.ROUND_TIME(START_DATE, 30), ag.DISPLAY_DESCRIPTION, FC.DISPLAY_DESCRIPTION, SEGMENT_ID, DI.DIRE_DESC) T ON S.START_DATE = T.START_DATE AND S.SEGMENT_ID = T.SEGMENT_ID JOIN DP_OWNER.SEGMENTS SEG ON SEG.SEGMENT_ID = S.SEGMENT_ID JOIN DP_OWNER.SECTION_SEGMENT_XREF SSX ON SSX.SEGMENT_ID = SEG.SEGMENT_ID JOIN DP_OWNER.SECTIONS SE ON SE.SECTION_ID = SSX.SECTION_ID where ($X{IN, SEG.DESCRIPTION, segment}) and ( $X{IN, T.AGENCY, agencyList}) and ( $X{IN, T.DISPLAY_DESCRIPTION, facility} ) and ($X{IN, SE.DESCRIPTION, section}) and ($X{IN, T.DIRECTION, direction}) and ( $P{startDate} is null or $P{startDate} <= S.START_DATE) and ( $P{endDate} is null or $P{endDate} >= S.END_DATE ) and ($X{IN, DAYOFWK, days}) and (START_TIME between $P{startTime} AND $P{endTime} ) ORDER BY S.START_DATE, SEG.DISPLAY_ORDER Link to comment Share on other sites More sharing options...
sadakar Posted March 21, 2017 Share Posted March 21, 2017 Any log(Studio) would be of helpful to identify the issue. 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