12 Replies Latest reply: Apr 25, 2013 9:32 AM by Locke90210 RSS

    pivot report- 2 dynamic columns

    Locke90210
      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
          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
            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
              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
                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
                  Hmmm.
                  I don't think it matters by why the double parenthesis for the IN clause?
                  • 6. Re: pivot report- 2 dynamic columns
                    Locke90210
                    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
                      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
                        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
                          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
                            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
                              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
                                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