Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Gray Hair is still relevant

Re: Gray Hair is still relevant

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 17 Jul 2006 07:52:32 -0700
Message-ID: <1153147952.877120.99030@p79g2000cwp.googlegroups.com>


I don't know what DBMS are you using.
This is an example of DB2. But, Oracle have also same functionarity(Recursive query) using CONNECT BY. (I'm sorry if I misunderstand your problem.)

CREATE TABLE Users
(Name VARCHAR( 10) NOT NULL PRIMARY KEY ,Categories VARCHAR(100) NOT NULL
);

INSERT INTO Users
VALUES
 ('Joe', 'Engenner|Tester|Engenner|Sales|Engenner|Project Manager') ,('Ann', 'Secretary|Technician|Engenner|Technical Manager|Sales Manager|Technical Manager')
,('Pak', 'Sales|Engenner|Project Manager|Engenner|Project Manager|Sales')
;

CREATE FUNCTION Remove_Dup_N (I_Text VARCHAR(100), N INTEGER) RETURNS VARCHAR(100)
LANGUAGE SQL
RETURN
WITH Find_Nth_Word (seq, sBar, eBar) AS ( VALUES (1, 0, POSSTR(I_Text||'|', '|') ) UNION ALL
SELECT seq + 1

, eBar
, LOCATE('|', I_Text||'|', eBar + 1)
  FROM Find_Nth_Word
 WHERE seq < 100
   AND seq <= N - 1
)
SELECT SUBSTR(I_Text,1,eBar) ||

       REPLACE(SUBSTR(I_Text||'|',eBar+1), SUBSTR(I_Text||'|',sBar+1,eBar-sBar), '')   FROM Find_Nth_Word
 WHERE seq = (SELECT MAX(seq) FROM Find_Nth_Word) ;

NAME CATEGORIES




Pak Sales|Engenner|Project Manager

Joe Engenner|Tester|Sales|Project Manager

Ann Secretary|Technician|Engenner|Technical Manager|Sales Manager

  3 record(s) selected. Received on Mon Jul 17 2006 - 09:52:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US