Home » SQL & PL/SQL » SQL & PL/SQL » Implementation of PIPELINE table function using array within array
Implementation of PIPELINE table function using array within array [message #288623] Tue, 18 December 2007 03:44 Go to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Hi,
I have two table empmast and empdtl. in mast there is empno and empname and in detail table there is year wise salary details.
I want to convert this salary wise vertical data into horizontal row, I tried with array within array but getting error.
data
empmast
EmpNo EmpName
111 tom
121 Prem
131 John
EmpSalDtl
EmpNo year Sal EmpNo year Sal EmpNo year Sal
111 2007 6000 111 2006 5500 111 2005 5500
111 2004 5000 121 2007 6400 121 2006 5000
121 2005 5000 121 2004 4750 131 2007 7000
131 2007 6500 131 2006 6000
I want result like this
111 Tom 6000 5500 5500 5000
121 Prem 6400 5000 5000 4750
131 John 7000 6500 6000

select * from
table(gk_horiz_Emp_P.get_horiz_Emp(CURSOR(SELECT b.* FROM EmpMast a, EmpSalDtl b
where b.empno = a.empno)))

Please help me out for the same.



create or replace PACKAGE gk_horiz_Emp_P
AS
TYPE New_Sal IS TABLE OF EmpDtl.Salary%TYPE
INDEX BY BINARY_INTEGER;
NewSalTab New_Sal;
TYPE ai_Sal_horiz_row IS record (
EmpNo EmpMast.EmpNo%TYPE,
EmpName EmpMast.EmpName%TYPE,
NewQty New_Sal);
TYPE cur_EmpDtl IS REF CURSOR RETURN EmpDtl%ROWTYPE;
FUNCTION get_horiz_Emp (Emp_Row IN cur_EmpDtl)
RETURN ai_Emp_tbl
PIPELINED
END;
create or replace PACKAGE BODY gk_Horiz_Emp_P AS
FUNCTION get_horiz_Emp (Emp_Row IN cur_EmpDtl)
RETURN ai_fcst_tbl
PIPELINED
IS
rec_Emp_Dtl Emp_Dtl_tbl%ROWTYPE;

V_OldEmpNo Number(7);
V_OldEmpNm Varchar2(50);
v_QtyCnt Number;
v_output_row ai_Sal_horiz_row := ai_Sal_horiz_row(); Not working giving error
BEGIN
LOOP
FETCH Emp_Row INTO rec_Emp_Dtl;
Exit When Emp_Row%NotFound;
IF (Nvl(v_OldEmpNo,Rec_Emp_Dtl.EmpNo) = rec_Emp_Dtl.EmpNo) THEN
v_QtyCnt := Nvl(V_QtyCnt,0)+1;
NewSalTab(V_QtyCnt) := Rec_Emp_Dtl.Salary;
ELSE
-- -- populate the horizontal output row by getting from the Salary array
v_Output_row.EmpNo := v_OldEmpNo;
v_Output_row.EmpName := v_OldEmpName;
For i in 1..v_QtyCnt Loop
v_output_row.NewSalAdj(i) := NewSalTab(i);
End Loop;
PIPE ROW (v_output_row);
V_QtyCnt := 0;
END IF;
v_OldEmpNo := rec_emp_dtl.EmpNo;
v_OldEmpName := rec_emp_dtl.EmpName;
END LOOP;
v_Output_row.EmpNo := v_OldEmpNo;
For i in 1..v_QtyCnt Loop
v_output_row.NewSalAdj(i) := NewSalTab(i);
End Loop;
PIPE ROW (v_output_row);
V_QtyCnt := 0;
END get_horiz_Emp;
END gk_Horiz_Emp;
Re: Implementation of PIPELINE table function using array within array [message #288625 is a reply to message #288623] Tue, 18 December 2007 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

In addition, a simple decode on this classic PIVOT query should do the trick.

Regards
Michel

[Updated on: Tue, 18 December 2007 03:48]

Report message to a moderator

Re: Implementation of PIPELINE table function using array within array [message #288630 is a reply to message #288625] Tue, 18 December 2007 04:00 Go to previous messageGo to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Hi Michel Cadot,
I am really sorry if it is not formated as per standard , actually I tried to copy formated code , but did not check with preview.
regarding decode, I don't want to use it as data volume is quite high, and it is going to be almost 80 MILLION row, and I want to convert into 2 MILLION rows, that's why want to implement like this.

Thanks and Regards
Praveen
Re: Implementation of PIPELINE table function using array within array [message #288641 is a reply to message #288630] Tue, 18 December 2007 04:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ I can't non formated code
2/ Did you try to search for PIVOT? I maintain decode will do the trick.
A single SQL statement is faster than a PL/SQL function.

Regards
Michel
Re: Implementation of PIPELINE table function using array within array [message #288675 is a reply to message #288623] Tue, 18 December 2007 05:07 Go to previous messageGo to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Hi,
here is the formated code..

CREATE OR REPLACE PACKAGE gk_hOriz_emp_p
AS
TYPE New_sal IS TABLE OF empdtl.Salary%TYPE INDEX BY BINARY_INTEGER ;

NewsalTab NEW_SAL;

TYPE ai_sal_hOriz_Row IS RECORD(empNo empMast.empNo%TYPE,
empName empMast.empName%TYPE,
Newqty NEW_SAL);

TYPE Cur_empdtl IS REF CURSOR RETURN empdtl%ROWTYPE;

FUNCTION Get_hOriz_emp(emp_Row IN CUR_EMPDTL) RETURN AI_EMP_TBL;
END;
/


CREATE OR REPLACE PACKAGE BODY gk_hOriz_emp_p
AS
FUNCTION Get_hOriz_emp
(emp_Row IN CUR_EMPDTL)
RETURN AI_FCST_TBL
IS
rec_emp_dtl emp_dtl_Tbl%ROWTYPE;
v_OldempNo NUMBER(7);
v_Oldempnm VARCHAR2(50);
v_qtycnt NUMBER;
v_Output_Row AI_SAL_HORIZ_ROW := ai_sal_hOriz_Row(); -- Not working giving error

BEGIN
LOOP
FETCH emp_Row INTO rec_emp_dtl;

EXIT WHEN emp_Row%NOTFOUND;

IF (nvl(v_OldempNo,rec_emp_dtl.empNo) = rec_emp_dtl.empNo) THEN
v_qtycnt := nvl(v_qtycnt,0) + 1;

NewsalTab(v_qtycnt) := rec_emp_dtl.Salary;
ELSE
-- -- populate the horizontal output row by getting from the Salary array

v_Output_Row.empNo := v_OldempNo;

v_Output_Row.empName := v_OldempName;

FOR i IN 1.. v_qtycnt LOOP
v_Output_Row.NewsaLadj(i) := NewsalTab(i);
END LOOP;
--Getting error PIPE ROW (v_output_row);

v_qtycnt := 0;
END IF;

v_OldempNo := rec_emp_dtl.empNo;

v_OldempName := rec_emp_dtl.empName;
END LOOP;

v_Output_Row.empNo := v_OldempNo;

FOR i IN 1.. v_qtycnt LOOP
v_Output_Row.NewsaLadj(i) := NewsalTab(i);
END LOOP;
--Getting error PIPE ROW (v_output_row);

v_qtycnt := 0;
END Get_hOriz_emp;
END gk_hOriz_emp;
Re: Implementation of PIPELINE table function using array within array [message #288679 is a reply to message #288675] Tue, 18 December 2007 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 December 2007 10:46

Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.


Regards
Michel

[Updated on: Tue, 18 December 2007 05:16]

Report message to a moderator

Re: Implementation of PIPELINE table function using array within array [message #288740 is a reply to message #288623] Tue, 18 December 2007 08:47 Go to previous messageGo to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Hi,
I tried to format the same manually as well as using OracleFAQ forum Format tool, but when I tried preview it's not getting proper formatting .

Regards
Praveen
Re: Implementation of PIPELINE table function using array within array [message #288741 is a reply to message #288740] Tue, 18 December 2007 08:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the following sentence from Guide I posted?
Quote:

[code] Enter your code here.[/code]

Regards
Michel
Re: Implementation of PIPELINE table function using array within array [message #288742 is a reply to message #288623] Tue, 18 December 2007 08:57 Go to previous messageGo to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Thanks A lot, I really missed it and tried so many times.


Regards
Praveen

create or replace PACKAGE gk_horiz_Emp_P 
AS
TYPE New_Sal  IS TABLE OF EmpDtl.Salary%TYPE
                INDEX BY BINARY_INTEGER;
NewSalTab   New_Sal;
TYPE ai_Sal_horiz_row IS record (
EmpNo              EmpMast.EmpNo%TYPE,
EmpName            EmpMast.EmpName%TYPE,
NewQty New_Sal);
TYPE cur_EmpDtl IS REF CURSOR RETURN EmpDtl%ROWTYPE;
FUNCTION get_horiz_Emp (Emp_Row IN cur_EmpDtl)
      RETURN ai_Emp_tbl
      PIPELINED
END;
create or replace PACKAGE BODY gk_Horiz_Emp_P AS
FUNCTION get_horiz_Emp (Emp_Row IN cur_EmpDtl)
      RETURN ai_fcst_tbl
      PIPELINED
IS
rec_Emp_Dtl Emp_Dtl_tbl%ROWTYPE;

V_OldEmpNo   Number(7);
V_OldEmpNm    Varchar2(50);
v_QtyCnt	Number;              
v_output_row    ai_Sal_horiz_row := ai_Sal_horiz_row();  Not working giving error
BEGIN
    LOOP
        FETCH Emp_Row INTO rec_Emp_Dtl;
	Exit When Emp_Row%NotFound;
          IF (Nvl(v_OldEmpNo,Rec_Emp_Dtl.EmpNo) = rec_Emp_Dtl.EmpNo)  THEN
		v_QtyCnt := Nvl(V_QtyCnt,0)+1;
		NewSalTab(V_QtyCnt) := Rec_Emp_Dtl.Salary;		
         ELSE
--            -- populate the horizontal output row by getting from the Salary array
            v_Output_row.EmpNo    := v_OldEmpNo;
            v_Output_row.EmpName    := v_OldEmpName;
		For i in 1..v_QtyCnt Loop
	        v_output_row.NewSalAdj(i) := NewSalTab(i);  
		End Loop;
            PIPE ROW (v_output_row);  /** THIS IS NOT WORKING **/
            V_QtyCnt := 0;
         END IF;
            v_OldEmpNo := rec_emp_dtl.EmpNo;
            v_OldEmpName := rec_emp_dtl.EmpName;
    END LOOP;    
            v_Output_row.EmpNo    := v_OldEmpNo;
		For i in 1..v_QtyCnt Loop
	        v_output_row.NewSalAdj(i) := NewSalTab(i);  
		End Loop;
            PIPE ROW (v_output_row);
            V_QtyCnt := 0;
END get_horiz_Emp;
END gk_Horiz_Emp; 

[Updated on: Tue, 18 December 2007 10:15]

Report message to a moderator

Re: Implementation of PIPELINE table function using array within array [message #288744 is a reply to message #288742] Tue, 18 December 2007 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Now my function is not allowing me to return using PIPE ROW.

What is the error?

Regards
Michel
Re: Implementation of PIPELINE table function using array within array [message #288808 is a reply to message #288623] Tue, 18 December 2007 14:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
search OraFaq, you will find this:

http://www.orafaq.com/node/1871

It might help

Good luck, Kevin
Re: Implementation of PIPELINE table function using array within array [message #288967 is a reply to message #288808] Wed, 19 December 2007 02:59 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
-- test data:
SCOTT@orcl_11g> SELECT * FROM EmpMast
  2  /

     EMPNO EMPNAME
---------- -------
       111 tom
       121 Prem
       131 John

SCOTT@orcl_11g> SELECT * FROM EmpSalDtl
  2  /

     EMPNO       YEAR        SAL
---------- ---------- ----------
       111       2004       5000
       121       2004       4750
       111       2005       5500
       121       2005       5000
       111       2006       5500
       121       2006       5000
       131       2006       6000
       111       2007       6000
       121       2007       6400
       131       2007       7000
       131       2007       6500

11 rows selected.


-- code (requires 11g):
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_years	VARCHAR2 (32767);
  3    v_sql	VARCHAR2 (32767);
  4  BEGIN
  5    -- Wm_concat is an undocumented function,
  6    -- so you may want to substitute another
  7    -- function like Tom Kyte's stragg.
  8    SELECT wm_concat (year)
  9    INTO   v_years
 10    FROM   (SELECT DISTINCT year
 11  	       FROM   EmpSalDtl
 12  	       ORDER  BY year DESC);
 13    v_sql :=
 14    'SELECT * FROM
 15  	 (SELECT a.EmpNo, a.EmpName, b.year, b.Sal
 16  	  FROM	 EmpMast a, EmpSalDtl b
 17  	  WHERE  a.EmpNo = b.EmpNo
 18  	 ) PIVOT (MAX (Sal)
 19  	 FOR year IN (' || v_years || ')) ORDER BY EmpNo';
 20    OPEN :g_ref FOR v_sql;
 21  END;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

     EMPNO EMPNAME       2007       2006       2005       2004
---------- ------- ---------- ---------- ---------- ----------
       111 tom           6000       5500       5500       5000
       121 Prem          6400       5000       5000       4750
       131 John          7000       6000

SCOTT@orcl_11g>


Re: Implementation of PIPELINE table function using array within array [message #288975 is a reply to message #288967] Wed, 19 December 2007 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To replace wm_concat:
SQL> with years as (select distinct extract(year from hiredate) year from emp)
  2  select max(substr(sys_connect_by_path(year,','),2)) years
  3  from years
  4  connect by prior year = year-1
  5  start with year = (select min(year) from years)
  6  /
YEARS
-------------------------------------------------------------------------------
1980,1981,1982

1 row selected.

Or if years are not contiguous:
SQL> with 
  2    years as (
  3      select distinct 
  4             extract(year from hiredate) year,
  5             dense_rank () over (order by extract(year from hiredate)) rk
  6      from emp
  7    )
  8  select max(substr(sys_connect_by_path(year,','),2)) years
  9  from years
 10  connect by prior rk = rk-1
 11  start with rk = 1
 12  /
YEARS
----------------------------------------------------------------------------
1980,1981,1982,1987

1 row selected.

Regards
Michel

[Updated on: Wed, 19 December 2007 03:45]

Report message to a moderator

Re: Implementation of PIPELINE table function using array within array [message #288982 is a reply to message #288975] Wed, 19 December 2007 03:37 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Yes, that would work nicely:

SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_years	VARCHAR2 (32767);
  3    v_sql	VARCHAR2 (32767);
  4  BEGIN
  5    select max(substr(sys_connect_by_path(year,','),2))
  6    into   v_years
  7    from   (select distinct year
  8  	       from   empsaldtl)
  9    connect by prior year = year+1
 10    start with year = (select max(year) from empsaldtl);
 11    v_sql :=
 12    'SELECT * FROM
 13  	 (SELECT a.EmpNo, a.EmpName, b.year, b.Sal
 14  	  FROM	 EmpMast a, EmpSalDtl b
 15  	  WHERE  a.EmpNo = b.EmpNo
 16  	 ) PIVOT (MAX (Sal)
 17  	 FOR year IN (' || v_years || ')) ORDER BY EmpNo';
 18    OPEN :g_ref FOR v_sql;
 19  END;
 20  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

     EMPNO EMPNAME       2007       2006       2005       2004
---------- ------- ---------- ---------- ---------- ----------
       111 tom           6000       5500       5500       5000
       121 Prem          6400       5000       5000       4750
       131 John          7000       6000

SCOTT@orcl_11g> 


Re: Implementation of PIPELINE table function using array within array [message #288983 is a reply to message #288982] Wed, 19 December 2007 03:39 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sigh, I'm still waiting for "11g XE" that runs on a small box like mine. Any strange bugs/features in 11g?

MHE
Re: Implementation of PIPELINE table function using array within array [message #288984 is a reply to message #288975] Wed, 19 December 2007 03:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I see that you edited your post since I applied that method, so applying that variation, it would work well too:

SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_years	VARCHAR2 (32767);
  3    v_sql	VARCHAR2 (32767);
  4  BEGIN
  5    select max(substr(sys_connect_by_path(year,','),2))
  6    into   v_years
  7    from   (select distinct year,
  8  		      dense_rank () over (order by year desc) rk
  9  	       from   empsaldtl)
 10    connect by prior rk = rk - 1
 11    start with rk = 1;
 12    v_sql :=
 13    'SELECT * FROM
 14  	 (SELECT a.EmpNo, a.EmpName, b.year, b.Sal
 15  	  FROM	 EmpMast a, EmpSalDtl b
 16  	  WHERE  a.EmpNo = b.EmpNo
 17  	 ) PIVOT (MAX (Sal)
 18  	 FOR year IN (' || v_years || ')) ORDER BY EmpNo';
 19    OPEN :g_ref FOR v_sql;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

     EMPNO EMPNAME       2007       2006       2005       2004
---------- ------- ---------- ---------- ---------- ----------
       111 tom           6000       5500       5500       5000
       121 Prem          6400       5000       5000       4750
       131 John          7000       6000

SCOTT@orcl_11g> 


Re: Implementation of PIPELINE table function using array within array [message #288986 is a reply to message #288982] Wed, 19 December 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Barbara,

I changed the initial query to fit the case where years may not be contiguous.

As you used the first one I repost it in my previous post.

Regards
Michel

[Edit: we should think to not post at the same time. Smile ]

[Updated on: Wed, 19 December 2007 03:46]

Report message to a moderator

Re: Implementation of PIPELINE table function using array within array [message #288988 is a reply to message #288983] Wed, 19 December 2007 03:47 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Maaher wrote on Wed, 19 December 2007 01:39

Sigh, I'm still waiting for "11g XE" that runs on a small box like mine. Any strange bugs/features in 11g?

MHE



I haven't noticed any strange bugs yet, but I haven't been experimenting with the new features for very long yet. I was limited to 10g XE on my old computer, but the new one can handle 11g EE. With 10g, there was no companion cd available, hence no English/French knowledge base for themes and ctx_doc and such. So, I can use all of that stuff now.

Re: Implementation of PIPELINE table function using array within array [message #288990 is a reply to message #288986] Wed, 19 December 2007 03:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
I hadn't even considered non-contiguous years. I suppose that could happen. Good point.


Re: Implementation of PIPELINE table function using array within array [message #289047 is a reply to message #288623] Wed, 19 December 2007 06:06 Go to previous messageGo to next message
pbarve101
Messages: 6
Registered: December 2007
Location: India
Junior Member
Hi,
I am really sorry that I am as a New Member and Newbies.
but this is not working giving error..
getting error at open :g_ref as V_SQL is not working
even checked the same at SQL prompt using V_YEARS output in query. I am using oracle 9i.


 11    v_sql :=
 12    'SELECT * FROM
 13  	 (SELECT a.EmpNo, a.EmpName, b.year, b.Sal
 14  	  FROM	 EmpMast a, EmpSalDtl b
 15  	  WHERE  a.EmpNo = b.EmpNo
 16  	 ) PIVOT (MAX (Sal)
 17  	 FOR year IN (' || v_years || ')) ORDER BY EmpNo';
 18    OPEN :g_ref FOR v_sql;

Re: Implementation of PIPELINE table function using array within array [message #289050 is a reply to message #289047] Wed, 19 December 2007 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I bet you missed this part:
VARIABLE g_ref REFCURSOR

But you didn't copy and paste the error. This should always be done.

Regards
Michel
Re: Implementation of PIPELINE table function using array within array [message #289094 is a reply to message #289047] Wed, 19 December 2007 11:30 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
As noted in my post, the code requires Oracle 11g. You did not originally state your version as suggested in the forum guide that you were directed to. Since you have 9i, this won't work for you. What you should use is the decode method previously suggested by others. It is an efficient method. The following shows first the static code that you will need to generate, followed by the code to dyanmically create and execute it.


-- static:
SCOTT@orcl_11g> SELECT a.EmpNo, a.EmpName
  2  	    , MAX (DECODE (b.year, 2007, b.sal)) "2007"
  3  	    , MAX (DECODE (b.year, 2007, b.sal)) "2006"
  4  	    , MAX (DECODE (b.year, 2007, b.sal)) "2005"
  5  	    , MAX (DECODE (b.year, 2007, b.sal)) "2004"
  6  	    , MAX (DECODE (b.year, 2007, b.sal)) "2003"
  7  FROM   EmpMast a, EmpSalDtl b
  8  WHERE  a.EmpNo = b.Empno
  9  GROUP  BY a.EmpNo, a.EmpName
 10  ORDER  BY a.EmpNo
 11  /

     EMPNO EMPNAME       2007       2006       2005       2004       2003
---------- ------- ---------- ---------- ---------- ---------- ----------
       111 tom           6000       6000       6000       6000       6000
       121 Prem          6400       6400       6400       6400       6400
       131 John          7000       7000       7000       7000       7000



-- dynamic:
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    v_sql	VARCHAR2 (32767);
  3  BEGIN
  4    v_sql :=
  5  	 'SELECT a.EmpNo,a.EmpName';
  6    FOR r IN (SELECT DISTINCT year FROM EmpSalDtl ORDER BY year DESC) LOOP
  7  	 v_sql := v_sql ||
  8  	 ',MAX(DECODE(b.year,' || r.year || ',B.sal)) "' || r.year || '"';
  9    END LOOP;
 10    v_sql := v_sql ||
 11  	 'FROM EmpMast a,EmpSalDtl b WHERE a.EmpNo=b.Empno GROUP BY a.EmpNo,a.EmpName ORDER BY a.EmpNo';
 12    OPEN :g_ref FOR v_sql;
 13  END;
 14  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

     EMPNO EMPNAME       2007       2006       2005       2004
---------- ------- ---------- ---------- ---------- ----------
       111 tom           6000       5500       5500       5000
       121 Prem          6400       5000       5000       4750
       131 John          7000       6000

SCOTT@orcl_11g> 
 


[Updated on: Wed, 19 December 2007 11:32]

Report message to a moderator

Previous Topic: Dollar Formatting in PL/SQL
Next Topic: Procedure undefined-ORA-06550 error
Goto Forum:
  


Current Time: Sat Dec 10 03:11:49 CST 2016

Total time taken to generate the page: 0.09444 seconds