Oracle Apex How to use Function returning sql in IG

Vinipanda

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?

create or replace FUNCTION Q_VALUE (P_YEAR   IN NUMBER, P_QUARTER  IN NUMBER,P_COLUMN IN VARCHAR2, P_ID    IN NUMBER, P_DEPT   IN VARCHAR2    )
RETURN VARCHAR2
IS
  V_YEAR  NUMBER;
  V_QUARTER NUMBER;
  V_PK   NUMBER;
  sql_stmt VARCHAR2(10000);
  V_VAL   VARCHAR2(1000);
BEGIN
IF ​P_QUARTER =1 THEN
        V_YEAR :=P_YEAR-1;
        V_QUARTER :=4
    BEGIN      SELECT QUARTER_PK
       INTO V_PK
       FROM DEPT_QUARTER_DATA
       WHERE PROJECT = P_ID
        AND QUARTER   = TO_CHAR(V_QUARTER)
        AND YEAR = V_YEAR;
    EXCEPTION WHEN OTHERS THEN
      V_PK := NULL;
    END;
    IF V_PK IS NOT NULL THEN
      sql_stmt := ' SELECT '||P_COL|| ' FROM DEPT_DATA WHERE DEPT_QUARTER_ID= '''||V_PK||''' AND  DEPT_ID='''||P_DEPT||''' ;
      BEGIN
        EXECUTE IMMEDIATE sql_stmt INTO V_VAL ;
      EXCEPTION WHEN OTHERS THEN
        V_VAL := NULL;
      END;   
    END IF;
  ELSE
    V_QUARTER :=P_QUARTER-1; //fetches from prev quarter
    BEGIN
 SELECT QUARTER_PK
       INTO V_PK
       FROM DEPT_QUARTER_DATA
       WHERE PROJECT = P_ID
        AND QUARTER   = TO_CHAR(V_QUARTER)
        AND YEAR = V_YEAR;
    EXCEPTION WHEN OTHERS THEN
      V_PK := NULL;
    END;
    IF V_PK IS NOT NULL THEN sql_stmt := ' SELECT '||P_COL|| ' FROM DEPT_DATA WHERE DEPT_QUARTER_ID= '''||V_PK||''' AND  DEPT_ID='''||P_DEPT||''' ;
      BEGIN
        EXECUTE IMMEDIATE sql_stmt INTO V_VAL ;
      EXCEPTION WHEN OTHERS THEN
        V_VAL := NULL;
      END;   
    END IF;
  RETURN V_VAL;
END;