This discussion is archived
12 Replies Latest reply: Apr 25, 2013 7:32 AM by Locke90210 RSS

pivot report- 2 dynamic columns

Locke90210 Newbie
Currently Being Moderated
Hello all,
maybe someone can help me with my code. Can't find my fault:

DECLARE
     lv_sql VARCHAR(32767);
     lv_pivot_cols VARCHAR2(1000);
lv_pivot_cols2 VARCHAR2(1000);
BEGIN
     FOR i IN (SELECT DISTINCT pro_name FROM  cat_attributes)
     LOOP
          lv_pivot_cols := lv_pivot_cols || '''' || i.pro_name || ''',';
     END LOOP;
     lv_pivot_cols := RTRIM(lv_pivot_cols,',');

FOR r IN (SELECT DISTINCT comp_name FROM  cat_attributes)
     LOOP
          lv_pivot_cols2 := lv_pivot_cols2 || '''' || r.comp_name || ''',';
     END LOOP;
     lv_pivot_cols2 := RTRIM(lv_pivot_cols2,',');

     lv_sql := 'SELECT * FROM
     *(SELECT position,cat_id, att_name as ATTRIBUTE, pro_name, value,competitor,comp_name*
     FROM   cat_attributes)
PIVOT (
                    max (value)
                    FOR (comp_name,pro_name) IN ((' ||lv_pivot_cols2|| ',' ||lv_pivot_cols|| '))) where cat_id *=:P1100_CHOOSE_CATEGORY ORDER BY POSITION';*

     RETURN lv_sql;                    
     --DBMS_OUTPUT.PUT_LINE(lv_sql);*
END;

1 error has occurred

Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic columns'' checkbox below the region source to proceed without parsing. ORA-00907: missing right parenthesis
  • 1. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    The builder is unable to run the SQL and obtain the name of your columns.
    Because the columns may be different every time due to the dynamic pivot I think you do need to check the Generic Columns option.

    -Jorge
  • 2. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    thanks for your suggestion jorge,
    but it doesn't work :-/

    The builder can parse the code now, but while running page I get

    failed to parse SQL query:
    ORA-00907: missing right parenthesis
  • 3. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    And if you generate the SQL outside of APEX does it look correct? Does it parse?
    This just sounds like a typo somewhere.
    I'm not familiar with the pivot syntax, I know it looks odd, but seems correct.

    -Jorge
  • 4. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    worked well with static values in sql_developer.
    Also can parse in apex till the the pivot-statement.

    PIVOT (
                        max (value)
                        FOR (comp_name,pro_name) IN ((' ||lv_pivot_cols2|| ',' ||lv_pivot_cols|| '))) where cat_id *=:P1100_CHOOSE_CATEGORY ORDER BY POSITION';*

    here has to be something wrong...
    :-/
  • 5. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    Hmmm.
    I don't think it matters by why the double parenthesis for the IN clause?
  • 6. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    syntax of pivot in "IN"- clause with connected values.
    in SQL-developer it works fine with static values.
  • 7. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    and the are not apostrophes in your lv_pivot_cols and lv_pivot_cols2 values?

    Sorry... I'm at a loss. Perhaps the problem is the PL/SQL context switch from PL/SQL to SQL and the PIVOT command is not recognized.
  • 8. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    the report works well with this code.


    DECLARE
         lv_sql VARCHAR(32767);
         lv_pivot_cols VARCHAR2(1000);
    BEGIN
         FOR i IN (SELECT DISTINCT pro_name FROM  cat_attributes)
         LOOP
              lv_pivot_cols := lv_pivot_cols || '''' || i.pro_name || ''',';
         END LOOP;
         lv_pivot_cols := RTRIM(lv_pivot_cols,',');

         lv_sql := 'SELECT * FROM
         *(SELECT position,cat_id, att_name as ATTRIBUTE, pro_name, value,competitor*
         FROM   cat_attributes)
         PIVOT (
                        max (value)
                        FOR pro_name IN (' ||lv_pivot_cols|| ')) where cat_id =:P1100_CHOOSE_CATEGORY ORDER BY POSITION';

         RETURN lv_sql;                    
         --DBMS_OUTPUT.PUT_LINE(lv_sql);*
    END;


    but if want to add a second parameter in the "IN" statement, it failed.
  • 9. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    Well that's some progress/success that proves the pivot will work just fine.

    Then there's definitely something wrong with the syntax for specifying the two columns (comp_name,pro_name). Are you sure the IN clause is correct when you have two FOR columns?

    Also, what if lv_pivot_cols or lv_pivot_cols2 is empty? The you would have an extra comma.
  • 10. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    jeah...sould be correct, at least if I'm working with static values.
    what do you mean? empty values ?
  • 11. Re: pivot report- 2 dynamic columns
    jrimblas Expert
    Currently Being Moderated
    Locke90210 wrote:
    jeah...sould be correct, at least if I'm working with static values.
    what do you mean? empty values ?
    What I mean is what if one of the comp_name or pro_name columns is null?
    If it was your IN clause would look like IN ('val1',,) with two comas side by side.

    So maybe you want to do something like
    DECLARE
    lv_sql VARCHAR(32767);
    lv_pivot_cols VARCHAR2(1000);
    lv_pivot_cols2 VARCHAR2(1000);
    BEGIN
    
    lv_pivot_cols := null;
    lv_pivot_cols2 := null;
    
    FOR i IN (SELECT DISTINCT pro_name FROM cat_attributes where pro_name is not null)
    LOOP
      lv_pivot_cols := lv_pivot_cols || '''' || i.pro_name || ''',';
    END LOOP;
    lv_pivot_cols := RTRIM(lv_pivot_cols,',');
    
    FOR r IN (SELECT DISTINCT comp_name FROM cat_attributes where comp_name is not null)
    LOOP
      lv_pivot_cols2 := lv_pivot_cols2 || '''' || r.comp_name || ''',';
    END LOOP;
    lv_pivot_cols2 := RTRIM(lv_pivot_cols2,',');
    
    ...etc...
  • 12. Re: pivot report- 2 dynamic columns
    Locke90210 Newbie
    Currently Being Moderated
    hey jorge,
    found a solution...thanks for all your help.

    DECLARE
         lv_sql VARCHAR(32767);
         lv_pivot_cols VARCHAR2(1000);
    BEGIN
         FOR i IN (SELECT DISTINCT comp_name, pro_name FROM  cat_attributes where cat_id= Item *)*
         LOOP
              lv_pivot_cols := lv_pivot_cols ||'('||''''||i.comp_name||''''||','|| '''' || i.pro_name ||''''||')'||',';
         END LOOP;
         lv_pivot_cols := RTRIM(lv_pivot_cols,',');

         lv_sql := 'SELECT * FROM
         *(SELECT position,cat_id, att_name as ATTRIBUTE, pro_name, value,comp_name*
         FROM   cat_attributes )
    PIVOT (
                        max (value)
                        FOR (comp_name,pro_name) IN (' ||lv_pivot_cols||')) where cat_id=:P1100_CHOOSE_CATEGORY order by position';

         RETURN lv_sql;                    
         --DBMS_OUTPUT.PUT_LINE(lv_sql);*
    END;

    Now I have an other problem. I can't set where-clause with an Item condition in the "select distinct"- Statement.
    If I try it with static values...it worked well. for example, if I set where-condition cat_id=1

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points