Home » SQL & PL/SQL » SQL & PL/SQL » REF TABLE
REF TABLE [message #193035] |
Thu, 14 September 2006 10:55  |
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   |
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  |
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.
|
|
|
Goto Forum:
Current Time: Tue Feb 18 23:33:23 CST 2025
|