Re: How to put hierarchy into columns

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 24 Nov 2005 06:04:44 -0800
Message-ID: <1132841083.954518.168490@z14g2000cwz.googlegroups.com>

Hello again Pawel.

I have put together a specific revision for you showing the entire path as required. SYS_CONNECT_BY_PATH was first introduced in 9i and the code I originally posted used CONNECT_BY_ROOT (10g specific).

As a word of caution I don't have a 9i instance available to confirm the query behaviour. I have executed this query against a 10g instance.

Best regards
Mike

TESSELLA Michael.OShea_at_tessella.com
__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301
www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL>
SQL> COLUMN "Parent Lowest" FORMAT A10
SQL> COLUMN "Parent-1" FORMAT A10
SQL> COLUMN "Parent-2" FORMAT A10
SQL> COLUMN "Parent-3" FORMAT A10
SQL> COLUMN "Parent-4" FORMAT A10
SQL> SET TRUNC ON
SQL> SET WRAP OFF
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION strInstance(path IN VARCHAR2,instanceNo

IN NUMB
R) RETURN VARCHAR2 IS
  2 sInstance VARCHAR2(100) := NULL;
  3 BEGIN
  4 SELECT
SUBSTR(path,INSTR(path,'/',1,instanceNo)+1,INSTR(path||'/','/',1,i stanceNo+1)-2)
  5 INTO sInstance
  6 FROM DUAL;
  7 RETURN sInstance;
  8 END;
  9 /

Function created.

SQL>
SQL>
SQL> SELECT strInstance(SYS_CONNECT_BY_PATH(name,'/'),1) "Parent

Lowest",

  2    strInstance(SYS_CONNECT_BY_PATH(name,'/'),1) "Parent-1",
  3    strInstance(SYS_CONNECT_BY_PATH(name,'/'),2) "Parent-2",
  4    strInstance(SYS_CONNECT_BY_PATH(name,'/'),3) "Parent-3",
  5    strInstance(SYS_CONNECT_BY_PATH(name,'/'),4) "Parent-4"

6 FROM corporate_slaves
7 CONNECT BY PRIOR slave_id=supervisor_id 8 START WITH supervisor_id IS NULL;

Parent Low Parent-1 Parent-2 Parent-3 Parent-4 ---------- ---------- ---------- ---------- ---------- Big Boss M Big Boss M
Big Boss M Big Boss M VP Marketi
Big Boss M Big Boss M VP Sales
Big Boss M Big Boss M VP Sales/J Joe Sales Big Boss M Big Boss M VP Sales/J Joe Sales Bill Sales Big Boss M Big Boss M VP Enginee
Big Boss M Big Boss M VP Enginee Jane Nerd Big Boss M Big Boss M VP Enginee Bob Nerd

8 rows selected.

SQL>
SQL>
SQL> SELECT *

2 FROM V$VERSION; BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bi PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Solaris: Version 10.1.0.3.0 - Production NLSRTL Version 10.1.0.3.0 - Production

SQL> Received on Thu Nov 24 2005 - 08:04:44 CST