Hierarchie nach oben und unten

[NYBen]

Hallo alle zusammen,

ich versuche momentan ein SQL Statement zu entwickeln welches mir Daten zu einem Unternehmen, einer Abteilung, eines Teams in einer Abteilung, oder sogar eines Arbeitnehmers innerhalb eines Teams in einer Baumstruktur ausgibt.

um es ein wenig zu veranschaulichen versuche ich nachfolgend mal die Komplette Struktur zu skizzieren.

Unternehmen
Abt A Abt B AbtC Abt D
Team A Team C Team E Team G
Gruppe A Gruppe C Gruppe D Gruppe F
User 1 User 6 User 3 User 8
User 5 User 2 User 7
Gruppe B Gruppe E
User 11 User 15
Team B Team D Team F Team H
Gruppe G Gruppe J ... ...
User 21 ...
User 39

So sieht das ganze ungefähr aus. In der Tabelle hat jeder Knoten und Jedes Blatt eine Nummer und in einer 2 Spalte die Nummer des Knotens vor ihm sofern es einen gibt. Nun kann man ja mit CONNECT BY und START WITH schon einiges anfangen, allerdings bin ich noch nicht auf den Trichter gekommen wie ich immer ganz oben anfangen kann.

Folgendes sollte das Ergebnis sein wenn ich die Nummer eines Users eingebe z.B 123 (User 15):

Unternehmen
Abt A
Abt B
Abt C
Team E
Gruppe D
Gruppe E
User 15
Team F
Abt D

Bei einem Team sollte es dann so aussehen
z.B. 5489 (Team G)

Unternehmen
Abt A
Abt B
Abt C
Abt D
Team G
Gruppe F
User 8
Team H

und so weiter und sofort. Sprich ich möchte eine X-Beliebige Nummer eingeben und er soll mir immer die ganze Struktur zeigen allerdings sollen alle Knoten die auf dem Weg zu dieser Nummer liegen in der späteren Anzeige auch geöffnet sein. Das nächste Problem ist das die Struktur nach unten hin aufgefächert werden soll aber eben auch nach oben wenn man eine Nummer wählt hinter der sich noch Zweige oder Blätter verbergen und vor der noch Knoten liegen.

Puh ich hoffe ich konnte mich einigermaßen verständlich machen. Hab heute schon so lange daran überlegt das ich langsam kirre werde
Am einfachsten erklärt ist das ganze wenn man sich den Microsoft Explorer ansieht. Genau das möchte ich erreichen

MfG
NYBen

[fanderlf]

Schau dir mal connect_by_is_leaf an. Damit kannst Du abfragen ob ein Knoten in einem Baum vorkommt.
Im Prinzipe müsstest Du dann nur noch alle bäume aufbauen und per connecty_by_is_leaf denjenigen auswählen der deinen Knoten enthält

Beispiel gibs hier:

http://www.psoug.org/reference/connectby.html

[NYBen]

Danke für deine Antwort. Hab mir die Sache mal angesehen. Leider ist mir noch nicht ganz klar wie mir das helfen sollte. Denn wenn ich nun ISLEAF gibt doch nur eine 1 oder 0 zurück. Aber wie kann ich hiermit den Baum nach unten sowie nach oben komplettieren wenn ich die Nummer eines Teams angebe? In dem Fall wird er mir doch nur sagen das ISLEAF 0 ist.

Vielleicht stehe ich ja jetzt auch auf dem Schlauch aber wie bereits erwähnt habe ich auch schon etwas zu lange über das Thema nachgedacht.

MfG
NYBen

[fanderlf]

Hab grad nochmal nachgelesen und das scheint so wohl wirklich nicht zu gehen xD Ich dachte man könnte damit abfragen ob denn eine Zeile in einem Baum vorkommt. Das war eigentlich mein Gedanke. Aber das ist wohl reine MetaInfo ob denn die Zeile wohl überhaupt nur ein "Leaf" ist.

lässt sich denn der Baum nicht rückwärts durchlaufen? Das sollte doch eigentlich auch funktionieren, oder?

Hier habe ich ein Statement gefunden was den Baum rückwärts durchläuft:

http://dotnet.org.za/thea/archive/2005/05/25/20944.aspx

Einfach einmal nach unten von dem Knoten den Du suchst. Einmal nach oben. Das ganze per UNION verbinden und du solltest genau das haben was Du suchst.

[NYBen]

das hatte ich mir auch schon überlegt allerdings muss ich die beiden ergebnismengen ja üner eine Spalte verknüpfen. Hier würde sich eigentlich level anbieten wenn der nicht immer bei 1 anfangen würde. Das andere ist das ich die Geschwisterknoten so nicht bekomme.

Aber ausprobieren werde ich es auf jedenfall mal

MfG
NYBen

[jum]

Hallo @NYBen, kannst Du mal einen Testfall posten mit CREATE TABLE und INSERT sowie dem erwarteten Ergebnis - ich kann das Problem noch nicht so ganz nachvollziehen !?

[ralfb]

Hallo,

ich habe es jetzt mal so verstanden:

var struktur VARCHAR2(12);
var abt VARCHAR2(12);
SET termout off
exec :struktur:='Team A1'; 
SET termout on
prompt Team A1
SELECT DISTINCT t.lev
               ,x.struk 
               ,t.pfad
FROM (
SELECT struk
FROM t2
CONNECT BY PRIOR t2.nummer=t2.vorg AND LEVEL=2
START WITH t2.vorg IS NULL
UNION ALL
SELECT struk
FROM t2 
WHERE LEVEL=2
CONNECT BY PRIOR t2.nummer=t2.vorg AND LEVEL=2
START WITH t2.struk=
(SELECT abteilung FROM (SELECT struk Abteilung
      ,level lev
      ,MAX(LEVEL) OVER () maxl
           FROM t2
           CONNECT BY PRIOR vorg = nummer
           START WITH struk=:struktur)
WHERE lev=maxl-1)
UNION ALL
SELECT struk
FROM t2
CONNECT BY PRIOR t2.vorg=t2.nummer
START WITH t2.struk=:struktur
UNION ALL
SELECT struk
FROM t2
CONNECT BY PRIOR t2.nummer =t2.vorg
START WITH t2.struk=:struktur
) x,(SELECT struk
           ,LEVEL lev 
           ,ltrim(substr(sys_connect_by_path(struk,'-'),1,100),'-') pfad
    FROM t2 CONNECT BY PRIOR nummer=vorg 
                    START WITH vorg IS NULL) t
WHERE x.struk=t.struk
ORDER BY t.pfad
;

Das gibt dann mit den Daten

>select * from t2 order by vorg nulls FIRST

STRUK               NUMMER            VORG
Unternehmen              1      
Abt A                    2               1
Abt B                    3               1
Team A1                  4               2
Team A2                  5               2
Team B2                 11               3
Team B1                 10               3
Gruppe A11               6               4
Gruppe A12               7               4
Gruppe A21              17               5
User A111                8               6
User A112                9               6
User A121               16               7
Gruppe B11              12              10
Gruppe B12              13              10
User B111               14              12
User B112               15              12
User A211               18              17

18 Zeilen ausgewählt.

so etwas

User A112
       LEV      STRUK           PFAD
         1      Unternehmen     Unternehmen
         2      Abt A           Unternehmen-Abt A
         3      Team A1         Unternehmen-Abt A-Team A1
         4      Gruppe A11      Unternehmen-Abt A-Team A1-Gruppe A11
         5      User A112       Unternehmen-Abt A-Team A1-Gruppe A11-User A112
         3      Team A2         Unternehmen-Abt A-Team A2
         2      Abt B           Unternehmen-Abt B

7 Zeilen ausgewählt.

Gruppe A11
       LEV      STRUK           PFAD
         1      Unternehmen     Unternehmen
         2      Abt A           Unternehmen-Abt A
         3      Team A1         Unternehmen-Abt A-Team A1
         4      Gruppe A11      Unternehmen-Abt A-Team A1-Gruppe A11
         5      User A111       Unternehmen-Abt A-Team A1-Gruppe A11-User A111
         5      User A112       Unternehmen-Abt A-Team A1-Gruppe A11-User A112
         3      Team A2         Unternehmen-Abt A-Team A2
         2      Abt B           Unternehmen-Abt B

8 Zeilen ausgewählt.

Team A1
       LEV      STRUK           PFAD
         1      Unternehmen     Unternehmen
         2      Abt A           Unternehmen-Abt A
         3      Team A1         Unternehmen-Abt A-Team A1
         4      Gruppe A11      Unternehmen-Abt A-Team A1-Gruppe A11
         5      User A111       Unternehmen-Abt A-Team A1-Gruppe A11-User A111
         5      User A112       Unternehmen-Abt A-Team A1-Gruppe A11-User A112
         4      Gruppe A12      Unternehmen-Abt A-Team A1-Gruppe A12
         5      User A121       Unternehmen-Abt A-Team A1-Gruppe A12-User A121
         3      Team A2         Unternehmen-Abt A-Team A2
         2      Abt B           Unternehmen-Abt B

10 Zeilen ausgewählt.

Abt A
       LEV      STRUK           PFAD
         1      Unternehmen     Unternehmen
         2      Abt A           Unternehmen-Abt A
         3      Team A1         Unternehmen-Abt A-Team A1
         4      Gruppe A11      Unternehmen-Abt A-Team A1-Gruppe A11
         5      User A111       Unternehmen-Abt A-Team A1-Gruppe A11-User A111
         5      User A112       Unternehmen-Abt A-Team A1-Gruppe A11-User A112
         4      Gruppe A12      Unternehmen-Abt A-Team A1-Gruppe A12
         5      User A121       Unternehmen-Abt A-Team A1-Gruppe A12-User A121
         3      Team A2         Unternehmen-Abt A-Team A2
         4      Gruppe A21      Unternehmen-Abt A-Team A2-Gruppe A21
         5      User A211       Unternehmen-Abt A-Team A2-Gruppe A21-User A211
         2      Abt B           Unternehmen-Abt B

12 Zeilen ausgewählt.

Gruß Ralf

[NYBen]

Guten Morgen,

erstmal möchte ich mich für eure Unterstützung bedanken. Vor allem bei Ralf, dessen Tipp mir gut geholfen hat.

Aber wie ihr sicher wisst gibt es fast keine Lösung ohne neues Problem
Und zwar ist ja die unterste ebene bzw. Das unterste Level der User. Bei der Nummer die nun als leaf im Baum zu finden ist handelt es sich um eine pseudonummer. Die soll auch so wie in der jetzigen Lösung im Baum auftauchen. Nur soll darunter noch ein Level kommen mit der logischen Nummer. Hört sich erstmal nicht so schwer an. Ist es aber da Pseudo und logische Nummer in einem Datensatz stehen. Daher zeigt ein normaler connect by Befehl nur die Pseudo Nummer und dann ist Ende, da nur diese Nummer auf den letzten Knoten verweist.

Beispiel:
Datensatz: AbtA, 12345, null, ...
User, 85428, 12345, 54368, ...

Als Baum hätte ich hier gerne das folgende:
AbtA
*85428
**54368

ich hoffe das das nicht unmöglich ist

MfG
NYBen

[ralfb]

Hallo, NYBen,

da kann ich dir jetzt auch nicht mehr folgen ;-) Kannst du nicht einfach die echte Nummer mit || anhängen? Ohne Zusatzsatz hört Connect by natürlich auf. Und darüber nachzudenken, wie man solche Pseudosätze erzeugen könnte - Model Clause? -, habe ich gerade keine Lust.

Gruß

Ralf

[ralfb]

Hallo,

heute früh schien die Sonne so schön, da habe ich noch mal was gebastelt, was dir vielleicht - wenn ich deine Aufgabe überhaupt richtig verstanden habe - weiterhilft.

var struktur VARCHAR2(12);
var abt VARCHAR2(12);
SET termout off
exec :struktur:='Team A1'; 
SET termout on
prompt Team A1
SELECT DISTINCT t.lev
               ,du.zahl
               ,t.nummer
               ,x.struk 
               ,DECODE(du.zahl,2,TO_CHAR(t.nummer),x.struk) sonder
               ,t.pfad
FROM (
SELECT struk
FROM t2
CONNECT BY PRIOR t2.nummer=t2.vorg AND LEVEL=2
START WITH t2.vorg IS NULL
UNION ALL
SELECT struk
FROM t2 
WHERE LEVEL=2
CONNECT BY PRIOR t2.nummer=t2.vorg AND LEVEL=2
START WITH t2.struk=
(SELECT abteilung FROM (SELECT struk Abteilung
      ,level lev
      ,MAX(LEVEL) OVER () maxl
           FROM t2
           CONNECT BY PRIOR vorg = nummer
           START WITH struk=:struktur)
WHERE lev=maxl-1)
UNION ALL
SELECT struk
FROM t2
CONNECT BY PRIOR t2.vorg=t2.nummer
START WITH t2.struk=:struktur
UNION ALL
SELECT struk
FROM t2
CONNECT BY PRIOR t2.nummer =t2.vorg
START WITH t2.struk=:struktur
) x,(SELECT struk
           ,LEVEL lev 
           ,nummer
           ,ltrim(substr(sys_connect_by_path(struk,'-'),1,100),'-') pfad
    FROM t2 CONNECT BY PRIOR nummer=vorg 
                    START WITH vorg IS NULL) t
   ,(SELECT LEVEL zahl FROM dual CONNECT BY LEVEL<=2) du
WHERE x.struk=t.struk
  AND ((t.lev<=5 AND du.zahl=1) OR (t.lev=5 AND du.zahl=2))
ORDER BY t.pfad,du.zahl
;

Da bin ich jetzt davon ausgegangen, dass die höchste Hierarchieebene 5 ist (vorl. Zeile) und habe die nummer als deine logische Nummer verwendet.

Das ergibt dann z.B. so etwas:

Team A1
       LEV            ZAHL          NUMMER      STRUK           SONDER          
         1               1               1      Unternehmen     Unternehmen
         2               1               2      Abt A           Abt A           
         3               1               4      Team A1         Team A1         
         4               1               6      Gruppe A11      Gruppe A11      
         5               1               8      User A111       User A111       
         5               2               8      User A111       8               
         5               1               9      User A112       User A112       
         5               2               9      User A112       9               
         4               1               7      Gruppe A12      Gruppe A12      
         5               1              16      User A121       User A121       
         5               2              16      User A121       16              
         3               1               5      Team A2         Team A2         
         2               1               3      Abt B           Abt B

Gruß

Ralf

[NYBen]

Hi,

danke für die Hilfe hat geklappt. Nun wo ich wieder ein wenig luft habe möchte ich mich ein wenig mit der "Model Clause" im Zusammenhang mit meinem Problem beschäftigen.
Ich verstehe aber nicht so ganz wie ich mittels Model Clause alle Daten aus dem Select Befehl bekomme + ein zusätzlicher datensatz wenn eine bestimmte Bedingung eintrifft. Geht das überhaupt mit der Model Clause?
Außer eventuell mit Iterate. Feste Daten kann ich in der Model Clause nicht angeben da ich nicht weiß wieviel Datensätze kommen.

MfG
Ben

[ralfb]

Hallo, Ben,

so der große Model-Kenner bin ich nicht, weil ich einfach noch nie einen Fall gefunden habe, wo ich's mal brauchen könnte. Aber so was wie

WITH daten AS (SELECT 1 lev, 'a' wert FROM dual
               UNION ALL
               SELECT 2, 'b' FROM dual)
SELECT lev
      ,wert
FROM daten
model 
  dimension BY (lev)
  measures (wert)
--  rules (wert[3]=wert[2])
  rules (wert[FOR lev IN (SELECT COUNT(*)+1 FROM daten)]=wert[cv()-1] )
ORDER BY lev
;
      LEV      W
         1      a
         2      b
         3      b

geht schon auch mit einer unbekannten Anzahl von Sätzen. Vielleicht hilft dir das.

Gruß

Ralf