SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

order in a connect by

Rosario VigilanteJul 15 2010 — edited Dec 9 2010

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
;

1 person found this helpful

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

1 person found this helpful

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! ;)

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 13 2010

Added on Jul 15 2010

4 comments

12,210 views

SQL & PL/SQL

order in a connect by

Comments

Post Details