Forum Stats

  • 3,817,369 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

Oracle Apex How to use Function returning sql query in IG

Vinipanda
Vinipanda Member Posts: 106 Red Ribbon
edited May 26, 2021 7:37AM in SQL & PL/SQL

Hi,

I had asked a question earlier in one of the threads.

The details are in thread here.

https://community.oracle.com/tech/developers/discussion/4484286/oracle-apex-modfiy-grid-query-to-fetch-previous-quarter-data-if-current-not-available#latest

Requirement was to write query for interactive grid that would return previous quarter data if current quarter was unavailable.

To resolve i created function in database returning sql which implements same logic. But i am not sure how to use it in apex application.

My current IG query is:

 SELECT DEPT_ID,DEPT_QUARTER_ID,
      ,KPI_1,KPI_2,KPI_3,KPI_4,KPI_5
  from DEPT_DATA
  WHERE  
    DEPT_QUARTER_ID=:DEPT_QUARTER_ID
  AND   PROJECT = :P1_PROJECT 


I need to modifiy this to use the function mentioned below retuning the sql .How can i do that?

As mentioned in thread, for project variable p_id, there is application item :PROJECT.

For year and quarter, i have app items A_YEAR AND A_QUARTER.

So function would be something like:

Q_VALUE(:A_YEAR,A_QUARTER, :PROJECT, :P1_DEPT --P1_DEPT is page item for departments

But how do i use this as source of IG?

Tagged:

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,405 Gold Trophy

    Do you really need that function?

    What if you change the query the following way:


    )with qtr as (
    SELECT DEPT_ID,DEPT_QUARTER_ID,
          ,KPI_1,KPI_2,KPI_3,KPI_4,KPI_5
      from DEPT_DATA
      WHERE  
        DEPT_QUARTER_ID=:DEPT_QUARTER_ID
      AND   PROJECT = :P1_PROJECT
    )
    select  DEPT_ID,DEPT_QUARTER_ID,
          ,KPI_1,KPI_2,KPI_3,KPI_4,KPI_5
    from qtr
    union all
    SELECT DEPT_ID,DEPT_QUARTER_ID,
          ,KPI_1,KPI_2,KPI_3,KPI_4,KPI_5
      from DEPT_DATA
      WHERE
        not exists (select 1 from qtr)  
      and  DEPT_QUARTER_ID=:DEPT_QUARTER_ID-1
      AND   PROJECT = :P1_PROJECT
      
    


  • Vinipanda
    Vinipanda Member Posts: 106 Red Ribbon

    Thanks, in case i need to add more logic like:

    The quarter id's are such that 1 is Q1,2 is Q2, 3 is Q3 and 4 is Q4.

    So if i need to add logic like, if the quarter is first quarter of year, ex: Q1, 2021. Then it should fetch data from Q4 2020.

    How can this also be included in this query?

     IF P_QUARTER =1 THEN

        V_YEAR :=P_YEAR-1;

        V_QUARTER :=4;

    I have added this to the function for better understanding.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,896 Red Diamond

    Analytics functions in SQL would be able to give you values from preceding "rows" under certain conditions if that's what you need.

    Using function code, especially dynamically generated SQL, is going to be less performant, difficult to debug and maintain and is open to SQL injection.

    BEDE
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,807 Red Diamond

    In APEX, bind variables are MANDATORY in order to guard against SQL injection.

    The PL/SQL function should return a dynamic source SQL statement, with a static SQL projection, and variable predicates using page item bind variables.

    Simplistic SQL function block in APEX for adding not null page items to the SQL query's where clause:

    declare
     reportSQL varchar2(4000) := 'select t.* from ip_traffic t &FILTER';
     filter    varchar2(1000);
    begin
     filter := 'where 1=1';
    
     if :P19_PROTOCOL is not null then
       filter := filter || ' and t.protocol = :P19_PROTOCOL';
     end if;
    
     if :P19_VOLUME is not null then
       if to_number(:P19_VOLUME) > 0 then
        filter := filter || ' and t.bytes >= :P19_VOLUME';
       else
        filter := filter || ' and t.bytes <= abs(:P19_VOLUME)';
       end if;
     end if;
    
     if :P19_IP_FILTER is not null then
       filter := filter || ' and t.source_ip like replace(:P19_IP_FILTER,''*'',''%'')';
     end if;
    
     reportSQL := replace( reportSQL, '&FILTER', filter );
    
     return( reportSQL );
    end;