Connect by ... start with ... order by siblings

amaia
20 de Enero del 2006
Dada esta sentencia:

select level,orden,conjunto,componente from
estructuras
start with codigo_conjunto='1212222122'
connect by prior codigo_conjunto=codigo_componente
order siblings to_number(orden)

Ejecuta bien pero en el resultado no llega a ordenar por el campo ORDEN. Realmente cual es la funcionalidad de SIBLINGS?? lo utilizo bien para ordenar los hermanos???


andrewsreid
20 de Enero del 2006
Acabo de mirar en Metalink (metalink.oracle.com).
¿Qué versión de Oracle estás empleando?
A causa del error Oracle 2988417 hay problemas con éste en 9.2.0.3 y 9.2.0.5.
Pero según Oracle estos problemas fueron reparados en 9.2.0.6 y 10.1.0.2.
Aquí está la descripción, desafortunadamente es en inglés:

Description
Wrong results from CONNECT BY ORDER SIBLINGS by a key
which includes LEVEL.

eg:
select /*+ no_filtering */ lpad(' ', level) || ename enam
from emp
start with ename = 'KING'
connect by prior empno = mgr
order siblings by enam;

does not indent as expect as LEVEL is not evaluated correctly.

Workaround:
Do not order siblings by an expression using LEVEL.

Note:
With this fix an ORA-976 error is raised for such SQL as ordering by an
expression involving LEVEL is not supported.

andrewsreid
20 de Enero del 2006
Antes de leer tu pregunta esta mañana no sabía nada de SIBLINGS.
He hecho unos ejemplos y me parece muy interesante.
Estoy de acuerdo contigo.
Creo que SIBLINGS puede servirte para ordenar las líneas con los mismos padres.
Aquí están unos ejemplos:

SQL> @connect_by
SQL> set echo on
SQL> col Manager format a15
SQL> col Employee format a15
SQL> spool connect_by
SQL> create table emp
2 (ename varchar2(10),
3 mgr varchar2(10));

Table created.

SQL> insert into emp values('Russell','King');

1 row created.

SQL> insert into emp values('Smith','King');

1 row created.

SQL> insert into emp values('Jones','King');

1 row created.

SQL> insert into emp values('Morrison','Russell');

1 row created.

SQL> insert into emp values('Matthews','Russell');

1 row created.

SQL> insert into emp values('Pearson','Russell');

1 row created.

SQL> insert into emp values('Williams','Smith');

1 row created.

SQL> insert into emp values('Brown','Smith');

1 row created.

SQL> insert into emp values('Fielder','Brown');

1 row created.

SQL> insert into emp values('Moir','Fielder');

1 row created.

SQL> select mgr Manager, ename Employee
2 from emp
3 start with mgr = 'King'
4 connect by prior ename = mgr
5 /

MANAGER EMPLOYEE
--------------- ---------------
King Russell
Russell Morrison
Russell Matthews
Russell Pearson
King Smith
Smith Williams
Smith Brown
Brown Fielder
Fielder Moir
King Jones

10 rows selected.

SQL> select lpad(' ',level)||mgr Manager, lpad(' ',level)||ename Employee
2 from emp
3 start with mgr = 'King'
4 connect by prior ename = mgr
5 order siblings by Employee
6 /

MANAGER EMPLOYEE
--------------- ---------------
King Jones
King Russell
Russell Matthews
Russell Morrison
Russell Pearson
King Smith
Smith Brown
Brown Fielder
Fielder Moir
Smith Williams

10 rows selected.

SQL> drop table emp;

Table dropped.

SQL> spool off


amaia
20 de Enero del 2006
Resulta que el Siblings funciona correctamente. He probado con tablas nuevas pero con una tabla exactamente no me funciona. La tabla esta creada en la version 8 de oracle.


andrewsreid
20 de Enero del 2006
La última vez yo trabajaba con una base de datos de Oracle 9.2.0.5.
Esta vez estoy trabajando con una base de datos de Oracle 8.1.7.4.
Tienes razón, SIBLINGS no funciona allí:

UNX7 BUSOAT /usr/users/oracle/andrew >sqlplus /

SQL*Plus: Release 8.1.7.0.0 - Production on Fri Jan 20 15:33:52 2006

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

SQL> @connect_by
SQL> col Manager format a15
SQL> col Employee format a15
SQL> spool connect_by
SQL> create table emp
2 (ename varchar2(10),
3 mgr varchar2(10));

Table created.

SQL> insert into emp values('Russell','King');

1 row created.

SQL> insert into emp values('Smith','King');

1 row created.

SQL> insert into emp values('Jones','King');

1 row created.

SQL> insert into emp values('Morrison','Russell');

1 row created.

SQL> insert into emp values('Matthews','Russell');

1 row created.

SQL> insert into emp values('Pearson','Russell');

1 row created.

SQL> insert into emp values('Williams','Smith');

1 row created.

SQL> insert into emp values('Brown','Smith');

1 row created.

SQL> insert into emp values('Fielder','Brown');

1 row created.

SQL> insert into emp values('Moir','Fielder');

1 row created.

SQL> select mgr Manager, ename Employee
2 from emp
3 start with mgr = 'King'
4 connect by prior ename = mgr
5 /

MANAGER EMPLOYEE
--------------- ---------------
King Russell
Russell Morrison
Russell Matthews
Russell Pearson
King Smith
Smith Williams
Smith Brown
Brown Fielder
Fielder Moir
King Jones

10 rows selected.

SQL> select lpad(' ',level)||mgr Manager, lpad(' ',level)||ename Employee
2 from emp
3 start with mgr = 'King'
4 connect by prior ename = mgr
5 order siblings by Employee
6 /
order siblings by Employee
*
ERROR at line 5:
ORA-00924: missing BY keyword


SQL> drop table emp;

Table dropped.

SQL> spool off
SQL>