CODEWITHSUNDEEP
oracle-databaseoracle-apex-5
Franco
Franco

Reputation: 25

Oracle Apex - Change SQL Query when button is pressed

Is it possible to change the date range (between date1 and date2) of a query using a button? Basically I have one Card that shows the total sales depending on which of these buttons, Today, This Week, This Month is selected. I'd like to use ONLY 1 card to display the result. Not the dirty way of hiding the other cards. This applies to Oracle Apex 5.x

Thanks.

Upvotes: 0

Views: 1436

Answers (2)

Franco
Franco

Reputation: 25

Okay so I tried this with a select list instead of a radio button group. This is now my current query

TRUNC(SS.EODATE) BETWEEN
    (CASE
        WHEN :P1_CO_SELECT_LIST = 1 THEN TRUNC(SYSDATE) -1
        WHEN :P1_CO_SELECT_LIST = 2 THEN TRUNC(SYSDATE) -8
        WHEN :P1_CO_SELECT_LIST = 3 THEN TRUNC(SYSDATE, 'MM')
        WHEN :P1_CO_SELECT_LIST = 4 THEN TRUNC(SYSDATE, 'YY')
      END)

    AND SYSDATE

And this is what my what select list static values

STATIC:Yesterday;1,This Week;2,This Month;3,This Year;4

I am getting correct values when trying each line and not using a case statement so I guess the modification I did from your comment is working.

My problem is refreshing my Classic Report (Card template) without refreshing the whole page. I created a Dynamic Action on the Change event of the select list, then added a Refresh action with below settings...

Action: Refresh
Selection Type: Region
Region: My Report Region

I have set my Select List a Default value = 3 which works. My report queries the This Month date. Now, when I try to change my selection in the select list, the report region changes data but not the expected data I need. And when I try to change again, it reloads the report region but the values doesn't change.

I'm sure I'm missing something here.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142733

You could create two hidden items (suppose it is page 1), P1_DATE_FROM and P1_DATE_TO and set their values by pressing those buttons (for example, via a dynamic action, using "Set Value").

Then the query would be

where date_column between :P1_DATE_FROM and :P1_DATE_TO

Alternatively, if you switch from 3 buttons to a radio button group with 3 radio buttons (whose values are 1 = today, 2 = this week, 3 = this month), then you could rewrite it as

where date_column between 
  case when :P1_RADIO = 1 then trunc(sysdate)
       when :P1_RADIO = 2 then trunc(sysdate) - 7
       when :P1_RADIO = 3 then trunc(sysdate, 'mm')
  end
  --
  and
  --
  case when :P1_RADIO = 1 then trunc(sysdate) + 1
       when :P1_RADIO = 2 then trunc(sysdate) + 1
       when :P1_RADIO = 3 then add_months(trunc(sysdate, 'mm'), 1)
  end

Those values might need to be changed, depending on what your DATE_COLUMN really contains, and what would "today" or "this week" or "this month" really be.

Upvotes: 2

Related Questions