Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL table Searching logic (Oracle 9i)
PL/SQL table Searching logic [message #275899] 
Tue, 23 October 2007 04:56 
deepayan
Messages: 51 Registered: December 2005

Member 


Hi All,
I need your help in writing a code logic. Let me explain my requirement.
suppose I have a Pl/sql table which is having the following structure .
TYPE T_X IS RECORD(
COMP VARCHAR2(20),
CUST VARCHAR2(20),
E_NUM VARCHAR2(20));
TYPE T_DETAILS IS TABLE OF T_X INDEX BY BINARY INTEGER;
V_DETAILS T_DETAILS;
suppose v_details contains data in the following fashion(The header I have used is just for understanding purpose.):
comp cust e_num
==== ==== ======
ABCS ASSL Acc:
ABCS ASSL 31184
ABCS ASSL 31270
ABCS ASSL 31321
ABCS ASSL 31356
ASI BC&M Acc:
ASSL BC&M 15360
ASSL BC&M 15362
ASSL BC&M 18202
INC BC&M Acc:
ASSL PANNON Acc:
Hungary PANNON Acc:
Hungary PANNON 11794
UK PANNON Acc:
UK PANNON 11232
UK PANNON 11321
Now in output I need a pl/sql table which will contain all distinct cust & the corresponding comp whenever e_num = 'Acc:'
i.e it will contain data in the following fashion.
comp cust
====== =====
ABCS ASSL
ASI BC&M
ASSL PANNON
If there is more than one record for a particular cust in which e_num = 'Acc:' then any one of them can be taken.
For ex : in the above table, we have three different comp for cust = 'PANNON' & e_num = 'Acc:'. So we can take any
one of 'ASSL' , 'Hungary' ,'UK' .
That is , whenever we will get an e_num = 'Acc:' then loop should terminate for that cust & store the corresponding
comp & cust in the output table.
Please help. I need the solution urgently.
Thanks.





Re: PL/SQL table Searching logic [message #275914 is a reply to message #275908] 
Tue, 23 October 2007 06:17 

Maaher
Messages: 7062 Registered: December 2001

Senior Member 


If you'd make some SQL types you could do it like this:
set serverout on
set scan off
CREATE TYPE T_X IS OBJECT(
COMP VARCHAR2(20)
, CUST VARCHAR2(20)
, E_NUM VARCHAR2(20)
);
/
CREATE TYPE T_DETAILS IS TABLE OF T_X;
/
DECLARE
v_tab T_DETAILS := T_DETAILS( T_X('ABCS' , 'ASSL' , 'Acc:' )
, T_X('ABCS' , 'ASSL' , '31184')
, T_X('ABCS' , 'ASSL' , '31270')
, T_X('ABCS' , 'ASSL' , '31321')
, T_X('ABCS' , 'ASSL' , '31356')
, T_X('ASI' , 'BC&M' , 'Acc:' )
, T_X('ASSL' , 'BC&M' , '15360')
, T_X('ASSL' , 'BC&M' , '15362')
, T_X('ASSL' , 'BC&M' , '18202')
, T_X('INC' , 'BC&M' , 'Acc:' )
, T_X('ASSL' , 'PANNON', 'Acc:' )
, T_X('Hungary', 'PANNON', 'Acc:' )
, T_X('Hungary', 'PANNON', '11794')
, T_X('UK' , 'PANNON', 'Acc: ')
, T_X('UK' , 'PANNON', '11232')
, T_X('UK' , 'PANNON', '11321')
);
BEGIN
FOR t_rec IN ( SELECT DISTINCT MIN(comp) comp, cust FROM TABLE(v_tab) WHERE e_num = 'Acc:' GROUP BY cust)
LOOP
dbms_output.put_line(t_rec.comp'/'t_rec.cust);
END LOOP;
END;
/
DROP TYPE T_DETAILS
/
DROP TYPE T_X
/
set scan on Just an idea.
MHE








Goto Forum:
Current Time: Tue Mar 28 22:27:45 CDT 2017
Total time taken to generate the page: 0.13742 seconds
