OraFAQ Forum: SQL & PL/SQL » Pivot concept in procedures

Home » SQL & PL/SQL » SQL & PL/SQL » Pivot concept in procedures (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pivot concept in procedures [message #610474]

Thu, 20 March 2014 08:00

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi ,

I am able to run the following pivot query successfully.

SQL> 
SQL>  SELECT *
  2        FROM (SELECT job, deptno, sum(sal) sal
  3                FROM SCOTT.emp
  4               GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
  5                                                                  20,
  6                                                                  30,
  7                                                                  40))
  8  ;
 
JOB               10         20         30         40
--------- ---------- ---------- ---------- ----------
CLERK           1300       1900        950 
SALESMAN                              5600 
PRESIDENT       5000                       
MANAGER         2450       2975       2850 
ANALYST                    6000

My requirement was that pivot elements should be dynamic in the procedure .
I had snippet with dept_no as parameter.But block was not allowing the non-constant values in pivot

Example :

declare
  deptno  constant  number := 10;
  cursor emp_pivot_valid  is
    SELECT *
      FROM (SELECT job, deptno, sum(sal) sal
              FROM SCOTT.emp
             GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
                                                                20,
                                                                30,
                                                                40));

  cursor emp_pivot_invalid  is
    SELECT *
      FROM (SELECT job, deptno, sum(sal) sal
              FROM SCOTT.emp
             GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(deptno,
                                                                20,
                                                                30,
                                                                40));

begin
  null;
end;
 
ORA-06550: line 17, column 65:
PL/SQL: ORA-56901: non-constant expression is not allowed for pivot|unpivot values
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored

IS there any alternate to use the dynamic values in pivot ?

Please help me

Thanks
Sai Pradyumn

Report message to a moderator

Re: Pivot concept in procedures [message #610477 is a reply to message #610474]

Thu, 20 March 2014 08:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Short answer, no.

Report message to a moderator

Re: Pivot concept in procedures [message #610478 is a reply to message #610477]

Thu, 20 March 2014 08:16

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks Michel

Report message to a moderator

Re: Pivot concept in procedures [message #613647 is a reply to message #610474]

Thu, 08 May 2014 19:25

lifubm
Messages: 1
Registered: May 2014
Location: KOREA

Junior Member

IS there any alternate to use the dynamic values in pivot ?
Yes, You can..

You must use Dynamic SQL.

Follow :

-----------------------------------------------------------------------------
cursor emp_pivot_invalid is
SELECT *
FROM (SELECT job, deptno, sum(sal) sal
FROM SCOTT.emp
GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(deptno,
20,
30,
40));
-----------------------------------------------------------------------------

v_sql = ' ';
deptno:= deptno + 10;

v_sql := v_sql ||'cursor emp_pivot_invalid is';
v_sql := v_sql ||' SELECT *';
v_sql := v_sql ||' FROM (SELECT job, deptno, sum(sal) sal';
v_sql := v_sql ||' FROM SCOTT.emp';
v_sql := v_sql ||' GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(';
v_sql := v_sql ||deptno;
v_sql := v_sql ||',';
v_sql := v_sql ||' 20,';
v_sql := v_sql ||' 30,';
v_sql := v_sql ||' 40))';

EXECUTE IMMEDIATE v_sql ;

Bye~! Cool

Report message to a moderator

Re: Pivot concept in procedures [message #613652 is a reply to message #613647]

Fri, 09 May 2014 00:19

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Just a remark regarding PL/SQL code posted in the first message: naming a constant (DEPTNO) with the same name as a table column might not be the best idea. Here's why:

SQL> declare
  2    deptno constant number := 10;
  3    l_cnt  number;
  4  begin
  5    select count(*)
  6      into l_cnt
  7      from emp
  8      where deptno = deptno;    --> which DEPTNO is the constant?
  9
 10    dbms_output.put_line(l_cnt);
 11  end;
 12  /
14

PL/SQL procedure successfully completed.

On the other hand, distinguishing your local variables (a constant, in this case) seem to be a better idea:

SQL> declare
  2    l_deptno constant number := 10;
  3    l_cnt  number;
  4  begin
  5    select count(*)
  6      into l_cnt
  7      from emp
  8      where deptno = l_deptno;    --> no doubt, L_DEPTNO is the constant
  9
 10    dbms_output.put_line(l_cnt);
 11  end;
 12  /
3

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Pivot concept in procedures [message #613670 is a reply to message #613647]

Fri, 09 May 2014 01:32

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

lifubm wrote on Fri, 09 May 2014 05:55

v_sql := v_sql ||'cursor emp_pivot_invalid is';
v_sql := v_sql ||' SELECT *';
v_sql := v_sql ||' FROM (SELECT job, deptno, sum(sal) sal';
v_sql := v_sql ||' FROM SCOTT.emp';
v_sql := v_sql ||' GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(';
v_sql := v_sql ||deptno;
v_sql := v_sql ||',';
v_sql := v_sql ||' 20,';
v_sql := v_sql ||' 30,';
v_sql := v_sql ||' 40))';

All that code could be written in short using string literal technique:

v_sql := q'[cursor emp_pivot_invalid is SELECT * FROM (
            SELECT job, deptno, sum(sal) sal FROM SCOTT.emp 
            GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN (]' 
            || var_deptno || 
         q'[, 20, 30, 40)]';

[Updated on: Fri, 09 May 2014 01:39]

Report message to a moderator

Previous Topic:	1 Statement Runs other Doesn't
Next Topic:	A Query to Display Sequence of Numbers in a Order

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:33:52 CDT 2024