RE: Org charts

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Fri, 15 Aug 2003 17:59:23 -0800
Message-ID: <F001.005CAF67.20030815175923@fatcity.com>

Dang !! and I thought micro$oft org chart had no competition ...  

Thanks
Raj

-----Original Message-----
Sent: Friday, August 15, 2003 9:04 PM
To: Multiple recipients of list ORACLE-L

I was playing around with org charts yesterday, and came up with a function to use for drawing org charts with lines from sqlplus.

This has probably been done before, but I couldn't seem to find one via google.

The output from scott.emp looks like:

select

   org_chart_line(level-1,3) || ename
from emp e
start with e.job = 'PRESIDENT'
connect by prior e.empno = e.mgr
/

---

---

KING

|___JONES 


|        |___SCOTT 

|        |        |___ADAMS 


|        |___FORD 

|        |        |___SMITH 
|___BLAKE 


|        |___ALLEN 

|        |___WARD 

|        |___MARTIN 

|        |___TURNER 

|        |___JAMES 

|___CLARK 


|        |___MILLER 


14 rows selected. 

The problem with working alone (in the Oracle sense ) is there's never
anyone 
in the next cube over to show stuff to. 

Jared 

=============================================== 

create or replace function org_chart_line ( 
        level_in integer 
        , indent_level_in integer 

)
return varchar2
is

        org_line varchar2(100);

        space_string varchar2(20) := '                    '; 
        char_string varchar2(20) := '____________________'; 

begin

        if level_in = 0 then 
                return ''; 
        end if; 

        for i in 1 .. ((level_in -1) * indent_level_in ) 
        loop 
                if mod(i,indent_level_in) = 0 then 
                        org_line := org_line ||
substr(space_string,1,indent_level_in -1) || '|'; 
                else 
                        org_line := org_line ||
substr(space_string,1,indent_level_in ) ; 
                end if; 
        end loop; 

        org_line := org_line ||  substr(char_string,1,indent_level_in  ); 

        org_line := '|' || substr(org_line,1); 

        return org_line; 

end;
/

show error function oc

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



text/plain attachment: ESPN_Disclaimer.txt

Received on Fri Aug 15 2003 - 20:59:23 CDT