Re: Two Questions

From: Leo Mannhart <mannhart_at_zuv.unizh.ch>
Date: Wed, 23 Mar 1994 08:01:25 GMT
Message-ID: <mannhart-230394090125_at_zuvmaclm.unizh.ch>


In article <1994Mar22.192430.13174_at_mnemosyne.cs.du.edu>, jdthomas_at_nyx10.cs.du.edu (Jeffery Thomas) wrote:

>
> (1) Can one directly call stored procedures in Powerbuilder? To
> do this in MS Access requires a sqlpassthru dll.
 I don't know
>
> (2) Has anyone implemented a binary tree structure in Oracle
> such as an Org chart? For example:
>
> ParentOrgID varchar(10),
> ChildOrgId varchar(10)
>
> The top-level Org, being the root, would have ParentOrgID set
> to null, while the bottom level orgs, being leaves, would have
> the ChildOrgID set to null. I would like to produce an org
> chart using Pl/SQL, producing a neat, nested listing by
> scanning thru the branches of this orgchart tree. I probably
> could dig out my ancient data structures/algorithm books nad
> figure it out, but it would be nice to avoid reinventing the
> wheel..
So why not use the SQL-example on p. 6-9 of the SQL Language Ref. Man. Version 6.0 ? (Look also on p. 3-27)
On the top level, the ParentOrgId is set to NULL but there is no ChildOrgId, instead you use the OrgId primary key. create table org (
  OrgId varchar(10) not null,
  OrgName varchar(80) not null,
  ParentOrgId varchar(10)
);
and the using the select statement
SELECT lpad(' ',2*(level-1)) || OrgName
FROM org
CONNECT BY PRIOR OrgId = ParentOrgId
START WITH OrgId = 'President' /* or whatever your top OrgName is */
>
> Jeff T.
 

-- 
Leo Mannhart
Planning Office
University of Zurich                              phone: ++41 1 257 23 34
Kuenstlergasse 15                                   fax: ++41 1 257 22 12
CH-8001 Zurich, Switzerland                       eMail:
mannhart_at_zuv.unizh.ch
Received on Wed Mar 23 1994 - 09:01:25 CET

Original text of this message