Forum Stats

  • 3,782,334 Users
  • 2,254,638 Discussions
  • 7,880,046 Comments

Discussions

Is there a way to "share" SQL where clause accross charts and tables?

gama_thais
gama_thais Member Posts: 2 Green Ribbon

Hi all!

I have developed a App with several charts (ca. 9) and tables (ca. 3). All of them share a great portion of the where clause in their SQL queries, which are based on filters on the App. The thing is I have to copy the where clause from my SQL query everywhere. And as nearly each filter is a multiselect, those clauses are somewhat complicated.

Here is an example of a very shortened version of a where clause (here only 4 filters are accounted for, when in reality there are 10):

WHERE to_date(item_date,'dd-mm-yy')   between   to_date(:P92_FROM,'dd-mm-yy') and  last_day(to_date(:P92_TO,'dd-mm-yy') )

 AND ((INSTR(':'||:P92_SELECT_DEPT||':', ':'||'All'||':') > 0 AND DEPT LIKE '%-%') OR INSTR(':'||:P92_SELECT_DEPT||':', ':'||DEPT||':') > 0)

  AND ((INSTR(','||:P92_SELECT_GROUP||',', ','||'All'||',') > 0 AND

CUSTOMER_GROUP IN ('All', 'Customer'))  OR INSTR(','||:P92_SELECT_GROUP||',', ','||GROUP||',') > 0)

Is there a way in which i could write these filters just once and it accross charts and tables? Any tips would be of great help!

Sorry if it is a silly question, but it hasn't been long since i started using SQL or ORACLE APEX.

Thank you in advance!

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 20,345 Red Diamond
    edited Nov 26, 2021 11:45AM

    Is there a way in which i could write these filters just once and it across charts and tables?

    Yes there is. Region data sources can be defined with a Type of Function Body Returning SQL Query. This allows you to create a PL/SQL package containing functions that generate and return the SQL required in your charts and reports.

    In doing so you will need to provide support for filter values being set on multiple pages. There are two ways to do this:

    1. If the filters are the same (or very similar) on all pages, you can create a reusable filter region on the application's global page and conditionally display this on the required pages.
    2. Alternatively, the data source functions can be parameterised so that the filter items can be identified on each invocation.

    Additionally, do not implement the filters in this way:

    AND ((INSTR(':'||:P92_SELECT_DEPT||':', ':'||'All'||':') > 0 AND DEPT LIKE '%-%') OR INSTR(':'||:P92_SELECT_DEPT||':', ':'||DEPT||':') > 0)
     AND ((INSTR(','||:P92_SELECT_GROUP||',', ','||'All'||',') > 0 AND
    CUSTOMER_GROUP IN ('All', 'Customer')) OR INSTR(','||:P92_SELECT_GROUP||',', ','||GROUP||',') > 0)
    

    This is both inefficient and insecure. The instr predicates preclude the optimizer from choosing an efficient query execution plan utilising indexes or partitioning. Concatenating literal page item values into the query increases hard parsing, floods the shared pool, and creates a vulnerability to SQL injection attacks.

    Table expressions should be used instead:

    ...
    and dept in (select column_value from table(apex_string.split(:pxx_select_dept, ':'))
    ...
    

    We'd need more information about the application, requirements, and the possible filters and parameters to cover all of the bases. If you want more detailed assistance then create a demonstration on apex.oracle.com and share guest developer credentials for the workspace.