Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

emulate "wm_sys.wm_conat over(order by sKey)" using "model caluse"

Aketi JyuuzouApr 14 2009 — edited Nov 4 2010
create table workT(ID,Val) as
select 1,'AAA' from dual union all
select 1,'BBB' from dual union all
select 1,'CCC' from dual union all
select 1,'CCC' from dual union all
select 1,'DDD' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'EEE' from dual union all
select 2,'FFF' from dual union all
select 2,'FFF' from dual union all
select 2,'GGG' from dual;
There is one of usage of "wm_sys.wm_concat" and emulate it.
col conStr for a50

select ID,Val,
wm_sys.wm_concat(Val)
over(partition by ID order by Val) as conStr
  from workT

select ID,aVal,
substr(sys_connect_by_path(bVal,','),2) as conStr
  from (select a.ID,a.Val as aVal,b.Val as bVal,
        a.RowID as Row_ID,
        Row_Number()
        over(partition by a.RowID order by b.Val) as rn
          from workT a,workT b
         where a.ID = b.ID
           and a.Val >= b.Val)
 where connect_by_IsLeaf = 1
Start With rn=1
connect by prior rn+1   = rn
       and prior Row_ID = Row_ID
order by ID,aVal;

ID  aVal  conStr
--  ----  ---------------------------
 1  AAA   AAA
 1  BBB   AAA,BBB
 1  CCC   AAA,BBB,CCC,CCC
 1  CCC   AAA,BBB,CCC,CCC
 1  DDD   AAA,BBB,CCC,CCC,DDD
 2  EEE   EEE,EEE,EEE,EEE
 2  EEE   EEE,EEE,EEE,EEE
 2  EEE   EEE,EEE,EEE,EEE
 2  EEE   EEE,EEE,EEE,EEE
 2  FFF   EEE,EEE,EEE,EEE,FFF,FFF
 2  FFF   EEE,EEE,EEE,EEE,FFF,FFF
 2  GGG   EEE,EEE,EEE,EEE,FFF,FFF,GGG
However,I want to know "model solution" which is tablseScan once only.
Can we make it?
I supoose we must use TableFunction.

I am using Oracle10gR2.
This post has been answered by Rob van Wijk on Apr 14 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 13 2009
Added on Apr 14 2009
6 comments
1,885 views