Forum Stats

  • 3,734,032 Users
  • 2,246,861 Discussions
  • 7,857,003 Comments

Discussions

order in a connect by

Rosario Vigilante
Rosario Vigilante Member Posts: 2,824 Silver Badge
edited December 2010 in SQL & PL/SQL
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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,497 Red Diamond
    edited July 2010
    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
    ;
    Frank Kulash
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited December 2010
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    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
    munky Member Posts: 2,739 Gold Trophy
    >
    I like recursive with clause
    >
    Yeah, it must now be used for absolutely everything! ;)
This discussion has been closed.