Disorting pake function aja
SQL> CREATE TABLE kel (
2 who VARCHAR2(1),
3 parent VARCHAR2(1),
4 no NUMBER(3));
Table created.
SQL> INSERT INTO kel VALUES ('A',NULL,1);
1 row created.
SQL> INSERT INTO kel VALUES ('B','A',1);
1 row created.
SQL> INSERT INTO kel VALUES ('C','A',2);
1 row created.
SQL> INSERT INTO kel VALUES ('D','B',1);
1 row created.
SQL> INSERT INTO kel VALUES ('E','B',2);
1 row created.
SQL> INSERT INTO kel VALUES ('F',NULL,2);
1 row created.
SQL> INSERT INTO kel VALUES ('G','F',1);
1 row created.
SQL> CREATE OR REPLACE FUNCTION order_kel(v_who IN VARCHAR2) RETURN
VARCHAR2 IS
2 v_parent VARCHAR2(1);
3 v_no NUMBER(3);
4 t_no VARCHAR2(10);
5 t_who VARCHAR2(1);
6 v_stat BOOLEAN;
7 n NUMBER(3);
8
9 BEGIN
10 t_no := '';
11 t_who := v_who;
12 v_stat := TRUE;
13 n := 0;
14 WHILE v_stat LOOP
15 SELECT parent,no INTO v_parent,v_no FROM kel WHERE who = t_who;
16 t_no := v_no||n||t_no;
17 IF v_parent IS NULL THEN
18 v_stat := FALSE;
19 END IF;
20 t_who := v_parent;
21 n := n + 1;
22 END LOOP;
23 RETURN t_no;
24 END;
25 /
Function created.
SQL> SELECT who FROM kel ORDER BY order_kel(who);
W
-
A
B
C
D
E
F
G
7 rows selected.
hth,
AWAL
dedy afriyanto wrote:
>
> Problem
> ------------
> saya punya tabel
>
> TABEL KELUARGA : WHO , PARENT, NO
>
> sekarang saya mau menampilkannya secara terurut ( berdasarkan no, dan
> parent
> )
> bagaimana caranya??
>
> jadi semisal
>
> WHO PARENT NO
> A 1
> B A 1
> C A 2
> D B 1
> E B 2
> F 2
> G F 1
>
> Kebetulan saja datanya udah di insertnya secara terurut kalau tidak
> bagaimana??
>
> OUTPUT
> ------------
> A
> B
> C
> D
> E
> F
> G
>
> Trima Kasih
>
> [Non-text portions of this message have been removed]
>
>
