Forum Stats

  • 3,851,566 Users
  • 2,263,999 Discussions
  • 7,904,781 Comments

Discussions

Dynamic region by function body returning query didn't work

Yong Huang
Yong Huang Member Posts: 109
edited Feb 20, 2014 12:09AM in APEX Discussions

APEX 4.1

Oracle 11g

I have a dynamic fields report as per different item value, set region type is pl/sql function body returning query, see the below code on region source.

declare

v_sql varchar2(2000);

begin

IF :P2_BY='MONTH' THEN

v_sql := 'SELECT ' || :P2_TYPE || ',SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC  FROM RPT_HOTEL GROUP BY ' || :P2_TYPE;

ELSE

v_sql := 'SELECT ' || :P2_TYPE || ',SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL GROUP BY ' || :P2_TYPE;

return v_sql;

end;

But incur error 'Missing expression' when save the region source.

I checked the sql statement is valid in sql command, and :P2_TYPE get the correct value.

If choose the 'use generic column name' then get the correct report but the column name is random.

please help to solve.

Thanks so mcuh

Best Answer

  • Yong Huang
    Yong Huang Member Posts: 109

    Hi fac586,

    I couldn't thank you any more. Obviously you read my code carefully, and found the design of table is not reasonable. actually I just design that for demonstration of prototype, so didn't consider the architecture.

    What I want to say is the issue has gone when I create a new page like this one, not know how it happened. anyway what I need has been realized, thanks so much for what you have done.

    Here I paste my code for reference somebody could need.

    declare
    v_sql varchar2(2000);
    v_time varchar2(50);
    v_for varchar2(30);
    begin
    IF :P4_TIME='MONTH' THEN
    v_time := 'to_char(lodging_date,''MM'') MONTH';
    v_for := 'to_char(lodging_date,''MM'')';
    ELSE
    v_time := 'to_char(lodging_date,''YYYY'') YEAR';
    v_for := 'to_char(lodging_date,''YYYY'')';
    END IF;
    IF :P4_MEASURE='NIGHTS' THEN
    v_sql := 'SELECT * from (SELECT ' || :P4_BY || ',VENDOR_NAME,' || v_time
    || ',count(entry_legacy_key) ROOM_NIGHTS
    FROM HOTEL_EXPENSE GROUP BY ' || :P4_BY || ',VENDOR_NAME,' || v_for || ')
    PIVOT (SUM(ROOM_NIGHTS) FOR ';
    ELSE
    v_sql := 'SELECT * from (SELECT ' || :P4_BY || ',VENDOR_NAME,' || v_time
    || ',SUM(APPROVED_AMOUNT) SPENDING
    FROM HOTEL_EXPENSE GROUP BY ' || :P4_BY || ',VENDOR_NAME,' || v_for || ')
    PIVOT (SUM(SPENDING) FOR ';
    END IF;
    IF :P4_TIME='MONTH' THEN
    v_sql := v_sql || ' MONTH IN (''01'',''02'',''03'',''04'',''05'',''06'',''07'',''08'',''09'',''10'',''11'',''12''))';
    ELSE
    v_sql := v_sql || ' YEAR IN (''2011'',''2012'',''2013'',''2014''))';
    END IF;
    return v_sql;
    end;
    

    and checked

    'Use Generic Column Names (parse query at runtime only)'
    

    the report column name used PL/QL

    declare
    v_sql varchar2(2000);
    begin
    v_sql :=  :P4_BY || ':VENDOR_NAME';
    IF :P4_TIME='MONTH' THEN
    v_sql := v_sql || ' :Jan:Feb:Mar:Apr:May:Jun:Jul:Aug:Sep:Oct:Nov:Dec';
    ELSE
    v_sql := v_sql || ':2011:2012:2013:2014';
    END IF;
    return v_sql;
    end;
    

Answers

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    Yong Huang wrote:
    
    APEX 4.1
    Oracle 11g
    I have a dynamic fields report as per different item value, set region type is pl/sql function body returning query, see the below code on region source.
    
    declare
    v_sql varchar2(2000);
    begin
    IF :P2_BY='MONTH' THEN
    v_sql := 'SELECT ' || :P2_TYPE || ',SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC  FROM RPT_HOTEL GROUP BY ' || :P2_TYPE;
    ELSE
    v_sql := 'SELECT ' || :P2_TYPE || ',SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL GROUP BY ' || :P2_TYPE;
    return v_sql;
    end;
    
    But incur error 'Missing expression' when save the region source.
    
    I checked the sql statement is valid in sql command, and :P2_TYPE get the correct value.
    
    

    P2_BY and P2_TYPE don't exist in Application Builder session state when saving the region source. The region source evaluated by the builder to determine fixed column headers is therefore:

    SELECT ,SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL GROUP BY

    which is clearly syntactically incorrect.

    You could try something like this (not tested) to supply a dummy value for P2_TYPE at build time:

    declare
    
      v_sql varchar2(2000);
      v_type varchar2(30);
    
    begin
    
      v_type := coalesce(:p2_type, '''X''');
    
      if :p2_by='MONTH'
      then
        v_sql := 'SELECT ' || v_type || ' P2_TYPE,SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC  FROM RPT_HOTEL GROUP BY ' || v_type;
      else
        v_sql := 'SELECT ' || v_type || ' P2_TYPE,SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL GROUP BY ' || v_type;
      end if;
    
      return v_sql;
    
    end;
    

    However it looks like your dodgy data model is at the root of this problem. See for an example of how to create a pivot report for different time periods using a standard data model.

    fac586
  • Stewed
    Stewed Member Posts: 41

    You also have added the end if;

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    Stewed wrote:
    
    You also have added the end if;
    

    Don't think that would cause a missing expression error, so I assume that it was present in the original region source.

  • Yong Huang
    Yong Huang Member Posts: 109
    edited Feb 14, 2014 1:10AM

    Hi fac586, thanks a lot for your help.

    But seems it didn't work that using dummy value, see the below error log

    • (ORA-06550: line 5, column 29: PLS-00103: Encountered the symbol "X" when expecting one of the following: ) , * & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ", was inserted before "X" to continue.)

    my code as below:

    declare

    v_sql varchar2(2000);

    v_type varchar2(10);

    begin

    v_type := coalesce(:P2_BY,''X'');

    IF coalesce(:P2_TIME_BY,'MONTH')='MONTH' THEN

    v_sql := 'SELECT ' || v_type || 'P2_BY,SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC  FROM RPT_HOTEL   GROUP BY ' || v_type;

    ELSE

    v_sql := 'SELECT ' || v_type || 'P2_BY,SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL   GROUP BY ' || v_type;

    end if;

    return v_sql;

    end;

    I tried an another way to approach that, please see the code below:

    declare

    v_sql varchar2(2000);

    begin

    if :P2_TIME_BY='YEAR' THEN

    v_sql := 'SELECT COUNTRY,SUM(Y2011) Y2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014) Y2014 FROM RPT_HOTEL WHERE MEASURE=:P2_VALUES  GROUP BY COUNTRY';

    ELSE

    v_sql := 'SELECT HOTEL_TYPE,SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC FROM RPT_HOTEL WHERE MEASURE=:P2_VALUES GROUP BY HOTEL_TYPE';

    END IF;

    return v_sql;

    end;

    it can be saved successfully, seems item value can be gotten. but if chose P2_TIME_BY=YEAR , then error

    ORA-20001: Error fetching column value ORA-01403: no data returned

    if chose P2_time_by=MONTH, then show the correct result.

    made my crazy, please help me out. thanks

    btw, how to mark the content as code?

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond
    edited Feb 14, 2014 2:41AM
    Yong Huang wrote:
    
    Hi fac586, thanks a lot for your help.
    
    But seems it didn't work that using dummy value, see the below error log
    
    
    (ORA-06550: line 5, column 29: PLS-00103: Encountered the symbol "X" when expecting one of the following: ) , * & = - + < / > at in is mod remainder not rem => <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset The symbol ", was inserted before "X" to continue.)
    
    
    my code as below:
    declare
    v_sql varchar2(2000);
    v_type varchar2(10);
    begin
    v_type := coalesce(:P2_BY,''X'');
    IF coalesce(:P2_TIME_BY,'MONTH')='MONTH' THEN
    v_sql := 'SELECT ' || v_type || 'P2_BY,SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC  FROM RPT_HOTEL   GROUP BY ' || v_type;
    ELSE
    v_sql := 'SELECT ' || v_type || 'P2_BY,SUM(Y2011) 2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014)2014 FROM RPT_HOTEL   GROUP BY ' || v_type;
    end if;
    return v_sql;
    end;
    

    That's not the code I posted. Why have you changed it? Cutting and pasting is very simple.

    I tried an another way to approach that, please see the code below:
    declare
    v_sql varchar2(2000);
    begin
    if :P2_TIME_BY='YEAR' THEN
    v_sql := 'SELECT COUNTRY,SUM(Y2011) Y2011,SUM(Y2012) Y2012,SUM(Y2013) Y2013,SUM(Y2014) Y2014 FROM RPT_HOTEL WHERE MEASURE=:P2_VALUES  GROUP BY COUNTRY';
    ELSE
    v_sql := 'SELECT HOTEL_TYPE,SUM(JAN) JAN,SUM(FEB) FEB,SUM(MAR) MAR,SUM(APR) APR,SUM(MAY) MAY,SUM(JUN) JUN,SUM(JUL) JUL,SUM(AUG) AUG,SUM(SEP) SEP,SUM(OCT) OCT,SUM(NOV) NOV,SUM(DEC) DEC FROM RPT_HOTEL WHERE MEASURE=:P2_VALUES GROUP BY HOTEL_TYPE';
    END IF;
    return v_sql;
    
    
    end;
    
    it can be saved successfully, seems item value can be gotten. but if chose P2_TIME_BY=YEAR , then error
    ORA-20001: Error fetching column value ORA-01403: no data returned
    if chose P2_time_by=MONTH, then show the correct result.
    

    It looks like since your queries have totally different projections that you must use the Use Generic Column Names (parse query at runtime only) option. Use the Headings Type: PL/SQL report attribute to get the column headings required when using this option.

    Alternatively (and probably easier), create two separate report regions and display them conditionally based on the P2_BY value.

    btw, how to mark the content as code?
    

    Switch to the Advanced Editor, click the >> button, select Syntax Highlighting, and choose the required language.

  • Yong Huang
    Yong Huang Member Posts: 109

    I created demo application on oracle apex,http://apex.oracle.com/pls/apex/f?p=4550:1

    workspace: susanhuang

    username:TURTOR

    password: turtor

    application:37073

  • Yong Huang
    Yong Huang Member Posts: 109

    HI fac586

    actually, I did copy and paste your code, just added some required fields in it.

    as I said, I had created the application on oracle.apex.com, could you kindly help me to check what's wrong?

    Thanks so much

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,197 Red Diamond

    Now I have seen the table I like the data model even less...Why are you using this table structure? Isn't it hugely maintenance intensive?

    Looking at the report and the options provided, it also looks like since all of the data is in one table that it would be appropriate to use an interactive report here rather than trying to create a dynamic data source. Have you considered using an IR?

  • Yong Huang
    Yong Huang Member Posts: 109

    Hi fac586,

    I couldn't thank you any more. Obviously you read my code carefully, and found the design of table is not reasonable. actually I just design that for demonstration of prototype, so didn't consider the architecture.

    What I want to say is the issue has gone when I create a new page like this one, not know how it happened. anyway what I need has been realized, thanks so much for what you have done.

    Here I paste my code for reference somebody could need.

    declare
    v_sql varchar2(2000);
    v_time varchar2(50);
    v_for varchar2(30);
    begin
    IF :P4_TIME='MONTH' THEN
    v_time := 'to_char(lodging_date,''MM'') MONTH';
    v_for := 'to_char(lodging_date,''MM'')';
    ELSE
    v_time := 'to_char(lodging_date,''YYYY'') YEAR';
    v_for := 'to_char(lodging_date,''YYYY'')';
    END IF;
    IF :P4_MEASURE='NIGHTS' THEN
    v_sql := 'SELECT * from (SELECT ' || :P4_BY || ',VENDOR_NAME,' || v_time
    || ',count(entry_legacy_key) ROOM_NIGHTS
    FROM HOTEL_EXPENSE GROUP BY ' || :P4_BY || ',VENDOR_NAME,' || v_for || ')
    PIVOT (SUM(ROOM_NIGHTS) FOR ';
    ELSE
    v_sql := 'SELECT * from (SELECT ' || :P4_BY || ',VENDOR_NAME,' || v_time
    || ',SUM(APPROVED_AMOUNT) SPENDING
    FROM HOTEL_EXPENSE GROUP BY ' || :P4_BY || ',VENDOR_NAME,' || v_for || ')
    PIVOT (SUM(SPENDING) FOR ';
    END IF;
    IF :P4_TIME='MONTH' THEN
    v_sql := v_sql || ' MONTH IN (''01'',''02'',''03'',''04'',''05'',''06'',''07'',''08'',''09'',''10'',''11'',''12''))';
    ELSE
    v_sql := v_sql || ' YEAR IN (''2011'',''2012'',''2013'',''2014''))';
    END IF;
    return v_sql;
    end;
    

    and checked

    'Use Generic Column Names (parse query at runtime only)'
    

    the report column name used PL/QL

    declare
    v_sql varchar2(2000);
    begin
    v_sql :=  :P4_BY || ':VENDOR_NAME';
    IF :P4_TIME='MONTH' THEN
    v_sql := v_sql || ' :Jan:Feb:Mar:Apr:May:Jun:Jul:Aug:Sep:Oct:Nov:Dec';
    ELSE
    v_sql := v_sql || ':2011:2012:2013:2014';
    END IF;
    return v_sql;
    end;
    
This discussion has been closed.