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

[ewallace]

I have been having trouble extracting the time from a date field, and tying it to a parameter. I have tried TO_CHAR, EXTRACT, TRUNC, and nested ones, but they all break when it comes to the parameter. Below is a snippet of the code with the parameter. I need to set up Time Period, between two times from S.START_DATE and S.END_DATE.

SELECT (TO_NUMBER(TO_CHAR(S.START_DATE, 'HH24'))) DISPLAY_START_TIME

FROM TABLE_NAME

WHERE ( $P{startTime} is null or  $P{startTime} <= DISPLAY_START_TIME  )

[mlopez_1]

What I do is TO_CHAR(date_field, 'HH:mm') AS ALIAS_FIELD

And setup two parameters of String type and then ALIAS_FIELD BETWEEN param_1 AND param_2 and works perfectly for me.

Mariano

[mlopez_1]

Sorry TO_CHAR(date_field, 'HH24:MI') I meant, for oracle database.

[ewallace]

Thank you for that tip! I tried it, but it kept breaking.. :( 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. The entire 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

[mlopez_1]

Hi again, I don't use ranges, only two parameters with their input controls of String type.

If you get 'START_TIME is an invalid identifier' error you are getting a SQL error, NO Report Unit error.

Something is wrong in SQL Statement.

You have to review carefully to look for it.

Mariano