Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

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

Aketi Jyuuzou
Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
edited November 2010 in SQL & PL/SQL
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.

Best Answer

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    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.

Answers

  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    http://rwijk.blogspot.com/2008/05/string-aggregation-with-model-clause.html
    Rob van Wijk
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited April 2009
    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
    561825 Member Posts: 646
    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
    561825
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Accepted Answer
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited April 2009
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited November 2010
    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
This discussion has been closed.