how to extract time from a field and tie it to a parameter as a range

0

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

 

ewallace's picture
Joined: Oct 7 2016 - 11:31am
Last seen: 2 days 8 hours ago

Any log(Studio) would be of helpful to identify the issue.

sadakar - 5 days 19 hours ago

0 Answers:

No answers yet
Feedback
randomness