Database Hierarchical Select

SELECT PROGRAM_ID,AD || DECODE(TUR,2,' (Yan Dal)',3,' (Çift Anadal)','') || DECODE(EGITIM_TURU,2,' (İkinci Öğr.)',3,' (Ekstern)','') FROM PROGRAM WHERE FAAL=1 AND BIRIM IN " +
"(select BIRIM_ID from BIRIM WHERE FAAL=1 start with BIRIM_ID=356" +
" connect by prior BIRIM_ID = UST_BIRIM_ID) order by AD


Üstteki sorgu ne yapıyor?_

Program tablosundaki birim alanını, birim tablosunun birim_id kolonundan seçiyor, seçerken başlangıçta bi birim_id olarak 356 yi alıyor ondan sonra birim tablosuında ust_birim_id si 356 olan satırların birim_id lerini alıyor.

DATABASE: Documentation About Triggers

http://www.enterprisedb.com/documentation/triggers.html

START WITH and CONNECT BY PRIOR clauses in ORACLE

select * from employee;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
1 0 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000

13 rows selected.

SQL>
SQL>
SQL> --START WITH and CONNECT BY PRIOR clauses.
SQL>
SQL> SELECT employee_id, manager_id, first_name, last_name
2 FROM employee
3 START WITH employee_id = 1
4 CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ----------
1 0 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn

13 rows selected.