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
0 Answers:
No answers yet
Any log(Studio) would be of helpful to identify the issue.