Home » SQL & PL/SQL » SQL & PL/SQL » REF TABLE
REF TABLE [message #193035] Thu, 14 September 2006 10:55 Go to next message
dshachar
Messages: 1
Registered: September 2006
Junior Member
I have a table like this
CREATE TABLE RM_DEBIT_CODES
(
SUBJ_CODE NUMBER(4),
SERV_CODE NUMBER(4),
DEBIT_CODE NUMBER(4),
DEBIT_TYPE NUMBER(4)
)
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,5,2); 
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,1,1); 
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,2,1); 
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,3,1); 
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,4,2); 
INSERT INTO RM_DEBIT_CODES(SUBJ_CODE,SERV_CODE,DEBIT_CODE,DEBIT_TYPE) VALUES(3,66,6,2); 
COMMIT;

I need to get the following:
1 --debit_type
1 --debit_code
2 --debit_code
3 --debit_code
2 --debit_type
4 --debit_code
5 --debit_code
6 --debit_code

for this I create the following package:
CREATE OR REPLACE PACKAGE building_pkg AS
TYPE debit_type_code_rec IS RECORD(
deb_debit_code rm_debit_codes.debit_code%type
);
type deb_cursor is table of debit_type_code_rec
 index by binary_integer;
PROCEDURE debit_refcur(p_subj_code IN rm_debit_codes.subj_code%TYPE,
  p_serv_code in  rm_debit_codes.serv_code%TYPE,
  debit_data IN OUT  deb_cursor);
END building_pkg;
/

CREATE OR REPLACE PACKAGE BODY building_pkg AS
PROCEDURE debit_refcur(p_subj_code IN rm_debit_codes.subj_code%TYPE,
  p_serv_code in  rm_debit_codes.serv_code%TYPE,
  debit_data IN OUT  deb_cursor) is
ii NUMBER;
v_debit_type rm_debit_types.debit_type%TYPE;
cursor c_debit_type is
select distinct a.debit_type
from rm_debit_codes a
where a.subj_code=p_subj_code
and a.serv_code=p_serv_code
and a.debit_type=b.debit_type;
cursor c_debit_code is
select c.debit_code
from rm_debit_codes c
where c.subj_code=p_subj_code
and c.serv_code =p_serv_code
and c.debit_type=v_debit_type;
BEGIN
ii := 1;
FOR c1rec IN c_debit_type LOOP
debit_data(ii).deb_debit_code := c1rec.debit_type;
v_debit_type:=c1rec.debit_type;
FOR c2rec IN c_debit_code LOOP
debit_data(ii).deb_debit_code := c2rec.debit_code;
END LOOP;
ii := ii + 1;
END LOOP;
END debit_refcur;
END building_pkg;
/

the result I got is the last debit_code of each debit_type
3 --debit_code
6 --debit_code

what is wrong, please help
Re: REF TABLE [message #193158 is a reply to message #193035] Fri, 15 September 2006 01:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try this:
  1  select nvl(debit_code,debit_type) col_1
  2        ,nvl2(debit_code,'DEBIT_CODE','DEBIT_TYPE') col_2
  3  from (select distinct debit_type,null debit_code from  rm_Debit_codes
  4       union all
  5        select debit_type,debit_code from rm_Debit_codes
  6*     order by debit_type,debit_code nulls first)
SQL> /

     COL_1 COL_2
---------- ----------
         1 DEBIT_TYPE
         1 DEBIT_CODE
         2 DEBIT_CODE
         3 DEBIT_CODE
         2 DEBIT_TYPE
         4 DEBIT_CODE
         5 DEBIT_CODE
         6 DEBIT_CODE


[Sorry, forgot to say thanks very much for posting create table and insert statements - makes it much easier]

[Updated on: Fri, 15 September 2006 01:57]

Report message to a moderator

Re: REF TABLE [message #193223 is a reply to message #193035] Fri, 15 September 2006 06:19 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to fix your code, you need to increment the variable ii inside the c2rec loop. As it stands, you are just overwriting the same table entry again and again.
Previous Topic: How to send a email alerter using PL/SQL?
Next Topic: Query Doubt
Goto Forum:
  


Current Time: Sat Dec 03 13:56:22 CST 2016

Total time taken to generate the page: 0.06168 seconds