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

Aketi Jyuuzou

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.

Rob van Wijk

Hi Aketi,

Ok, that is slightly different indeed. But it can be easily solved by using a max analytic function, like this:

SQL> create table workT(ID,Val) as
  2  select 1,'AAA' from dual union all
  3  select 1,'BBB' from dual union all
  4  select 1,'CCC' from dual union all
  5  select 1,'CCC' from dual union all
  6  select 1,'DDD' from dual union all
  7  select 2,'EEE' from dual union all
  8  select 2,'EEE' from dual union all
  9  select 2,'EEE' from dual union all
 10  select 2,'EEE' from dual union all
 11  select 2,'FFF' from dual union all
 12  select 2,'FFF' from dual union all
 13  select 2,'GGG' from dual
 14  /

Tabel is aangemaakt.

SQL> select id
  2       , rn
  3       , substr(max(constr) over (partition by id,val),2) constr
  4    from ( select *
  5             from workt
  6            model
  7                  partition by (ID)
  8                  dimension by(Row_Number() over(partition by ID order by Val) as rn)
  9                  measures(Val,cast(null as varchar2(50)) as conStr)
 10                  rules
 11                  ( conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()]
 12                  )
 13         )
 14  /

        ID         RN CONSTR
---------- ---------- --------------------------------------------------
         1          1 AAA
         1          2 AAA,BBB
         1          3 AAA,BBB,CCC,CCC
         1          4 AAA,BBB,CCC,CCC
         1          5 AAA,BBB,CCC,CCC,DDD
         2          1 EEE,EEE,EEE,EEE
         2          4 EEE,EEE,EEE,EEE
         2          3 EEE,EEE,EEE,EEE
         2          2 EEE,EEE,EEE,EEE
         2          5 EEE,EEE,EEE,EEE,FFF,FFF
         2          6 EEE,EEE,EEE,EEE,FFF,FFF
         2          7 EEE,EEE,EEE,EEE,FFF,FFF,GGG

12 rijen zijn geselecteerd.

Regards,
Rob.

Rob van Wijk

http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html

Aketi Jyuuzou

Thanks reply.
But my sample has duplicate rows.
Then it is not easy that we can use Row_number
I guess we have to use iteration_number and other model feature

In other words.
BelowResult is not correct.

col conStr for a50

select *
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr)
rules(
conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()]);

ID  RN  VAL  conStr
--  --  ---  ----------------------------
 1   1  AAA  ,AAA
 1   2  BBB  ,AAA,BBB
 1   3  CCC  ,AAA,BBB,CCC          <-correct result is AAA,BBB,CCC,CCC
 1   4  CCC  ,AAA,BBB,CCC,CCC
 1   5  DDD  ,AAA,BBB,CCC,CCC,DDD
 2   1  EEE  ,EEE                  <-correct result is EEE,EEE,EEE,EEE
 2   2  EEE  ,EEE,EEE              <-correct result is EEE,EEE,EEE,EEE
 2   3  EEE  ,EEE,EEE,EEE          <-correct result is EEE,EEE,EEE,EEE
 2   4  EEE  ,EEE,EEE,EEE,EEE
 2   5  FFF  ,EEE,EEE,EEE,EEE,FFF
 2   6  FFF  ,EEE,EEE,EEE,EEE,FFF,FFF
 2   7  GGG  ,EEE,EEE,EEE,EEE,FFF,FFF,GGG

561825

I have enhanced slightly the solution of Rob.

select id, val, constr
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr, count(*) over(partition by id, val) as cnt)
rules(
conStr[any] order by rn = trim(both ',' from (conStr[cv()-1]) || ',' || 
                          case when val[cv()] != val[cv() - 1] or constr[CV() - 1] is null then
                                                      rpad(val[cv()] || ',', cnt[cv()] * length(val[cv()]) + cnt[cv()],
                                                           val[cv()] || ','
                                                          )
                                                 End
                              )
)              

Regards

Raj

Rob van Wijk

Hi Aketi,

Ok, that is slightly different indeed. But it can be easily solved by using a max analytic function, like this:

SQL> create table workT(ID,Val) as
  2  select 1,'AAA' from dual union all
  3  select 1,'BBB' from dual union all
  4  select 1,'CCC' from dual union all
  5  select 1,'CCC' from dual union all
  6  select 1,'DDD' from dual union all
  7  select 2,'EEE' from dual union all
  8  select 2,'EEE' from dual union all
  9  select 2,'EEE' from dual union all
 10  select 2,'EEE' from dual union all
 11  select 2,'FFF' from dual union all
 12  select 2,'FFF' from dual union all
 13  select 2,'GGG' from dual
 14  /

Tabel is aangemaakt.

SQL> select id
  2       , rn
  3       , substr(max(constr) over (partition by id,val),2) constr
  4    from ( select *
  5             from workt
  6            model
  7                  partition by (ID)
  8                  dimension by(Row_Number() over(partition by ID order by Val) as rn)
  9                  measures(Val,cast(null as varchar2(50)) as conStr)
 10                  rules
 11                  ( conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()]
 12                  )
 13         )
 14  /

        ID         RN CONSTR
---------- ---------- --------------------------------------------------
         1          1 AAA
         1          2 AAA,BBB
         1          3 AAA,BBB,CCC,CCC
         1          4 AAA,BBB,CCC,CCC
         1          5 AAA,BBB,CCC,CCC,DDD
         2          1 EEE,EEE,EEE,EEE
         2          4 EEE,EEE,EEE,EEE
         2          3 EEE,EEE,EEE,EEE
         2          2 EEE,EEE,EEE,EEE
         2          5 EEE,EEE,EEE,EEE,FFF,FFF
         2          6 EEE,EEE,EEE,EEE,FFF,FFF
         2          7 EEE,EEE,EEE,EEE,FFF,FFF,GGG

12 rijen zijn geselecteerd.

Regards,
Rob.

Aketi Jyuuzou

OOPS.
I did not realize that there are nice RPAD usage and max analytic function usage.
thank you very much.

And I arranged Rob's solution ;-)

col CONSTR for a50

select ID,VAL,substr(CONSTR,2) as CONSTR
  from workT model
partition by (ID)
dimension by(Row_Number() over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr)
rules(
conStr[any] order by rn = conStr[cv()-1] || ',' || Val[cv()],
conStr[any] = max(conStr) over(partition by Val));

ID  VAL  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

Aketi Jyuuzou

And I arranged R.Subramanian's splution :-)

select ID,Val,substr(conStr,2) as conStr
  from workT
 model
partition by(ID)
dimension by(Row_Number()
             over(partition by ID order by Val) as rn)
measures(Val,cast(null as varchar2(50)) as conStr,
         sum(Length(Val)+1) over(partition by ID order by Val) as LenB)
rules(
conStr[any] order by rn = RPad(conStr[cv()-1] || ',' || Val[cv()],LenB[cv()]
                              ,',' || Val[cv()]));

http://www.geocities.jp/oraclesqlpuzzle/10-291.html