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) ;

• Commands Entered ------------------------- WITH Recurse (N, Name, Categories, Cat_Count) AS ( SELECT 1
  , Name
  , Categories
  , LENGTH(REPLACE(TRANSLATE(UPPER(Categories), '',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ', ''))+1 FROM Users UNION ALL SELECT N + 1
  , Name
  , Remove_Dup_N (Categories, N)
  , LENGTH(REPLACE(TRANSLATE(UPPER(Categories), '',
  'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),' ', ''))+1 FROM Recurse WHERE N < 100 AND N <= Cat_Count ) SELECT Name
  , CAST(REPLACE(Categories, '||', '') AS VARCHAR(100)) AS
  Categories FROM Recurse A WHERE N = (SELECT MAX(N) FROM Recurse B WHERE B.Name = A.Name ) ;

  ---

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