order in a connect by

Rosario Vigilante

hello to all

having

stat   LEVEL c
...
...
1	2	Urologia(43)
1	3	Visita generale urologica(VURO)
1	4	201043000103-17/06/2010
1	3	Visita urologia successiva(VUROC)
1	4	201043000230-08/07/2010

get from a query 
...
..
ORDER SIBLINGS BY c 

How can I get result ordered in desc only when level = 4
that is,

ORDER SIBLINGS BY c ,  case when LEVEL= 4 then c  desc end      /*substr(c, 18,23)  end*/

return error ORA-00976: LEVEL, PRIOR, or ROWNUM not allowed here

This is result set I would like to get

stat   lev    c
...
...
1	2	Urologia(43)

1	3	Visita urologia successiva(VUROC)
1	4	201043000230-08/07/2010     -- first (desc ordered)

1	3	Visita generale urologica(VURO)
1	4	201043000103-17/06/2010

Thanks in advance

Edited by: Rosario Vigilante on Jul 15, 2010 11:27 AM

Frank Kulash

Hi,

You can get the results you want using ORDER BY.
I don't have your table, so I'll use scott.emp to illustrate.
Say we want LEVEL 1 and 2 sorted by ename ('BLAKE' < 'CLARK' < 'JONES'), but all the higher LEVELs sorted by sal.

SELECT	ename
,	sal
,	LEVEL
,	SYS_CONNECT_BY_PATH ( CASE 
				  WHEN  LEVEL <= 2 	
				  THEN  RPAD (ename, 10)
				  ELSE  TO_CHAR (sal, '000000') 
			     END
			    , '/'
			    )	AS sort_key
FROM	scott.emp
START WITH	mgr	IS NULL
CONNECT BY	mgr	= PRIOR empno
ORDER BY	sort_key
;

Output:

ENAME             SAL      LEVEL SORT_KEY
---------- ---------- ---------- ----------------------------------------
KING             5000          1 /KING
BLAKE            2850          2 /KING      /BLAKE
JAMES             950          3 /KING      /BLAKE     / 000950
MARTIN           1250          3 /KING      /BLAKE     / 001250
WARD             1250          3 /KING      /BLAKE     / 001250
TURNER           1500          3 /KING      /BLAKE     / 001500
ALLEN            1600          3 /KING      /BLAKE     / 001600
CLARK            2450          2 /KING      /CLARK
MILLER           1300          3 /KING      /CLARK     / 001300
JONES            2975          2 /KING      /JONES
FORD             3000          3 /KING      /JONES     / 003000

ENAME             SAL      LEVEL SORT_KEY
---------- ---------- ---------- ----------------------------------------
SCOTT            3000          3 /KING      /JONES     / 003000
SMITH             800          4 /KING      /JONES     / 003000/ 000800
ADAMS            1100          4 /KING      /JONES     / 003000/ 001100

Of course, we don't actually want to display sort_key, so tell your front end to hide it (e.g., in SQL*Plus, say

COLUMN  sort_key     NOPRINT

), or compute it in a sub-query, like this:

WITH	got_sort_key	AS
(
	SELECT	ename
	,	sal
	,	LEVEL   AS lvl
	,	SYS_CONNECT_BY_PATH ( CASE 
					  WHEN  LEVEL <= 2 	
					  THEN  RPAD (ename, 10)
					  ELSE  TO_CHAR (sal, '000000') 
				     END
				    , '/'
				    )	AS sort_key
	FROM	scott.emp
	START WITH	mgr	IS NULL
	CONNECT BY	mgr	= PRIOR empno	
)
SELECT	ename
,	sal
,	lvl
FROM	got_sort_key
ORDER BY	sort_key
;

Aketi Jyuuzou

I like recursive with clause B-)

with rec(empno,ename,sal,LV,SK) as(
SELECT empno,ename,sal,1,null
  from scott.emp
 where mgr IS NULL
union all
SELECT b.empno,b.ename,b.sal,a.LV+1,
case when a.LV+1 >= 3 then b.sal end
  from rec a,scott.emp b
 where a.empno = b.mgr)
search depth first by SK,ename set rn
select ename,SAL,LV,SK,rn from rec order by rn;

ENAME    SAL  LV    SK  RN
------  ----  --  ----  --
KING    5000   1  null   1
BLAKE   2850   2  null   2
JAMES    950   3   950   3
MARTIN  1250   3  1250   4
WARD    1250   3  1250   5
TURNER  1500   3  1500   6
ALLEN   1600   3  1600   7
CLARK   2450   2  null   8
MILLER  1300   3  1300   9
JONES   2975   2  null  10
FORD    3000   3  3000  11
SMITH    800   4   800  12
SCOTT   3000   3  3000  13
ADAMS   1100   4  1100  14

Aketi Jyuuzou

Hi "Frank Kulash"
Your solution is very Nice idea to use sys_connect_by_path at order by clause :-)

But needs a little change.
Because there is the same sal which is 3000.
Therefore order by sys_connect_by_path brokes hierarchical tree.
Then we have to use row_number to make distinct sortkeys ;-)

My homepage mentions how to emulate order siblings by of oracle at db2.
http://www.geocities.jp/oraclesqlpuzzle/db2/db2-recwith-3.html

col SKs for a50

select ename,sal,LEVEL AS lvl,
SYS_CONNECT_BY_PATH(CASE WHEN LEVEL <= 2
                         THEN RPAD (ename,10)
                    ELSE TO_CHAR (SK, 'fm000')
                    /*or hex numbers to_char(SK,'XXX')*/
                    END , '/') as SKs
from (select empno,mgr,ename,sal,
      Row_Number() over(order by sal,ename) as SK
      from scott.emp)
start with mgr IS NULL
CONNECT BY mgr = PRIOR empno
order by SKs;

ENAME    SAL  LVL  SKS
------  ----  ---  ------------------------------
KING    5000    1  /KING
BLAKE   2850    2  /KING      /BLAKE
JAMES    950    3  /KING      /BLAKE     /002
MARTIN  1250    3  /KING      /BLAKE     /004
WARD    1250    3  /KING      /BLAKE     /005
TURNER  1500    3  /KING      /BLAKE     /007
ALLEN   1600    3  /KING      /BLAKE     /008
CLARK   2450    2  /KING      /CLARK
MILLER  1300    3  /KING      /CLARK     /006
JONES   2975    2  /KING      /JONES
FORD    3000    3  /KING      /JONES     /012
SMITH    800    4  /KING      /JONES     /012/001
SCOTT   3000    3  /KING      /JONES     /013
ADAMS   1100    4  /KING      /JONES     /013/003

munky

>
I like recursive with clause
>
Yeah, it must now be used for absolutely everything!