Jump to content
Changes to the Jaspersoft community edition download ×

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


ewallace

Recommended Posts

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  )

Link to comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...