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 Go to next message
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 #275903 is a reply to message #275899] Tue, 23 October 2007 05:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Something like:
select distinct comp, cust from table(v_details) where e_num='Acc:'

Regards
Michel
Re: PL/SQL table Searching logic [message #275908 is a reply to message #275899] Tue, 23 October 2007 05:46 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi ,
I need the output in PL/SQL table.Moreover, one cust should have one entry only.Using distinct will return more than one row where cust is same & e_num = 'Acc:'.

Thanks
Deepayan
Re: PL/SQL table Searching logic [message #275914 is a reply to message #275908] Tue, 23 October 2007 06:17 Go to previous messageGo to next message
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
Re: PL/SQL table Searching logic [message #275951 is a reply to message #275908] Tue, 23 October 2007 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't give a solution, I gave a clue how to get it and Maarten added more clues.

Hope this will be sufficient for you, otherwise post what your try and why it does not work.

Regards
Michel
Re: PL/SQL table Searching logic [message #275966 is a reply to message #275899] Tue, 23 October 2007 09:13 Go to previous messageGo to next message
deepayan
Messages: 51
Registered: December 2005
Member
Hi,
Unfortunately I can't use SQL types. Whatever calculations I have to do , is to be in the procedure only .
Any idea , how we can do it from Pl/SQL?

Thanks,
Deepayan
Re: PL/SQL table Searching logic [message #276005 is a reply to message #275966] Tue, 23 October 2007 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Unfortunately I can't use SQL types

Why?

Quote:

how we can do it from Pl/SQL?

Loop on the source table and fill the result one.

Regards
Michel
Re: PL/SQL table Searching logic [message #276148 is a reply to message #276005] Wed, 24 October 2007 02:14 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Or you could use an associative array:
set serverout on
set scan off
DECLARE
  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;

  TYPE t_distinct IS TABLE OF t_x INDEX BY VARCHAR2(41);
  
  v_tab      t_details;
  v_distinct t_distinct;
  v_idx VARCHAR2(41);
BEGIN

  v_tab(v_tab.count+1).comp := 'ABCS';
  v_tab(v_tab.count).cust := 'ASSL';
  v_tab(v_tab.count).e_num := 'Acc:';
...
...
  v_tab(v_tab.count+1).comp := 'UK'     ;
  v_tab(v_tab.count).cust := 'PANNON';
  v_tab(v_tab.count).e_num := '11321';

  FOR t_idx IN 1..v_tab.count
  LOOP
    IF v_tab(t_idx).e_num = 'Acc:' THEN
      v_distinct(v_tab(t_idx).cust).comp := v_tab(t_idx).comp;
      v_distinct(v_tab(t_idx).cust).cust := v_tab(t_idx).cust;
      v_distinct(v_tab(t_idx).cust).e_num := v_tab(t_idx).e_num;
    END IF;
  END LOOP;
  
  v_idx := v_distinct.FIRST;
  WHILE v_idx IS NOT NULL LOOP
    dbms_output.put_line(v_distinct(v_idx).comp||'/'||v_distinct(v_idx).cust);
    v_idx := v_distinct.NEXT(v_idx);
  END LOOP;
END;
/

set scan on
MHE
Re: PL/SQL table Searching logic [message #276371 is a reply to message #275899] Thu, 25 October 2007 00:53 Go to previous message
deepayan
Messages: 51
Registered: December 2005
Member
Hi Maaher,
Thanks. It will be really helpful to me.Thanks a lot.

Deepayan
Previous Topic: Duplicate value in a single column
Next Topic: Build SQL Statement then Execute
Goto Forum:
  


Current Time: Fri Dec 09 07:41:07 CST 2016

Total time taken to generate the page: 0.52250 seconds