Filter sql query results using static values from “Select list” page item in Oracle APEX 5.0

Question

I'm looking for a way to filter and display records using static values from a "Select list" page item. I have created a bar chart in APEX 5.0 using the following query:

  select to_char(to_date(time_stamp,'YYYY-MM-DD-HH24:MI:SS'),'YYYY-MM-DD-HH24:MI:SS') as label, col2 as value from table1 where :P5_NEW_1 = col1 ;

The time_stamp column of table1 is of datatype varchar2 in my database and contains the date values in the format YYYY-MM-DD-HH24:MI:SS

e.g., values like below are stored in the time_stamp column

2015-08-26-10:17:15
2015-08-26-13:17:15
2015-09-17-12:45:54
2015-09-17-14:12:32
2015-10-06-10:01:42
2015-10-06-11:01:28
2015-10-06-05:01:28

and so on...

I have added a "Select list" item named "interval" on my form that contains a pre-populated list of values like 1hr, 6hrs.

Now I want to modify the above query such that following should happen:

For the value of 1hr selected from the drop down list, the query should check the time_stamp column and display the records for the last one hour

(i.e., records which fall in range to_char(sysdate - 1/24 ,'YYYY-MM-DD-HH24:MI:SS') to to_char(sysdate ,'YYYY-MM-DD-HH24:MI:SS')
And for the value of 6hrs selected from the drop-down list, the query should display records for the last 6 hours.

(i.e., records which fall in range to_char(sysdate - 6/24 ,'YYYY-MM-DD-HH24:MI:SS' to to_char(sysdate ,'YYYY-MM-DD-HH24:MI:SS') )

How do I modify my SQL query to add the above condition in my current SQL query, using static values from page item select list?

time_stamp can be (sysdate-1/24), and will depend on which interval is selected right? — brenners1302
I want to modify the sql query to cover above conditions. Based on the input selected from drop down list the query should display records with those particular date ranges. — Mukul Sharma
can i ask whats the return of your static values?if 1hr is selected, whats the return value?and whats the value of P5_NEW_1?what is its significance to the query? — brenners1302

brenners1302 brenners1302 · Accepted Answer · 2015-10-06T07:22:06

Hi you can try this:

SELECT to_char(to_date(time_stamp,'YYYY-MM-DD-HH24:MI:SS'),'YYYY-MM-DD-HH24:MI:SS') AS label, col2 AS VALUE
FROM table1 
WHERE :P5_NEW_1 = col1 AND
to_date(time_stamp,'yyyy-mm-dd hh24:mi:ss') BETWEEN
       (SYSDATE - (to_number(:SELECT_LIST_ID,'99')/24)) AND SYSDATE;

Just make sure that the static values has a return value equal to hr itself

For example, if static value is equal to 1HR then return value should be 1

:SELECT_LIST_ID is the select list id which holds the static values for the interval.

Hope this helps.

Filter sql query results using static values from “Select list” page item in Oracle APEX 5.0

1 Answers