Forum Stats

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

Discussions

wm_concat(column_name) order by [SOLVED]

Ludock
Ludock Member Posts: 185
edited August 2008 in SQL & PL/SQL
The question is based on a other thread:
425510

Aketi Jyuuzou Posted: Nov 23, 2007 3:17 AM

with the next statement there will be 4 records displayed
(there are 4 columns for this constraint)

select constraint_name, wm_concat(column_name) over(order by position) as column_names
from user_cons_columns
where constraint_name='EUL5_DOC_UK_2'

1 EUL5_DOC_UK_2 DOC_EU_ID
2 EUL5_DOC_UK_2 DOC_EU_ID,DOC_NAME
3 EUL5_DOC_UK_2 DOC_EU_ID,DOC_NAME,DOC_BATCH
4 EUL5_DOC_UK_2 DOC_EU_ID,DOC_NAME,DOC_BATCH,DOC_FOLDER_ID

is it possible to select only the last (4) record?

Thanks in advance..

Comments

  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited August 2008
    Maybe
    select constraint_name, 
    wm_concat(case when Rn <=4 then column_name end) over(order by position) as column_names
    from (select constraint_name,column_name,position,
    Row_Number() over(order by position) as Rn
    from user_cons_columns)
    where constraint_name='EUL5_DOC_UK_2'
    Because aggregate function ignores nulls.
    for instance
    select wmsys.wm_concat(case when Rn <=4 then Rn end) over(order by Rn) str
    from (select RowNum as rn
    from all_catalog
    where RowNum <= 6);
    STR     
    --------
    1
    1,2
    1,2,3
    1,2,3,4
    1,2,3,4
    1,2,3,4
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Note that wmsys.wm_concat is undocumented and unsupported.

    It looks like the script below, which I use to show me the constraints on a table, might be useful to you:
    column "Constraint" format a20
    column "Kolom(men)" format a40
    column "Soort" format a11
    column "Verwijst naar" format a23
    column "Inhoud check constraint" format a36 truncate
    accept P_TABEL char prompt 'Van welke tabel wil je de constraints zien? '
    set verify off
    select c.constraint_name "Constraint"
    , case c.constraint_type
    when 'P' then 'Primary key'
    when 'U' then 'Unique key'
    when 'R' then 'Foreign key'
    when 'C' then 'Check'
    else 'Anders: ' || c.constraint_type
    end "Soort"
    , rtrim(cc.cols,',') "Kolom(men)"
    , case when c.r_owner is not null then c.r_owner || '.' || c.r_constraint_name end "Verwijst naar"
    , c.search_condition "Inhoud check constraint"
    from user_constraints c
    , ( select constraint_name
    , p
    , c cols
    from user_cons_columns
    where table_name = upper('&&P_TABEL')
    model
    partition by (constraint_name)
    dimension by (nvl(position,row_number() over (partition by constraint_name order by null)) p)
    measures (column_name c)
    ( c[any] order by p desc = c[cv()] || ',' || c[cv()+1]
    )
    ) cc
    where c.constraint_name = cc.constraint_name
    and cc.p = 1
    order by
    case c.constraint_type
    when 'P' then 1
    when 'U' then 2
    when 'R' then 3
    when 'C' then 4
    else 5
    end
    , c.constraint_name
    /
    undefine P_TABEL
    set verify on
    Groet,
    Rob.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Oh solution of Rob is great.

    My other solution which will access to table twice ;-)
    SortKey  Val
    ------- ---
    10 AAA
    20 BBB
    30 CCC
    40 DDD
    50 EEE
    60 FFF
    70 GGG
    80 HHH
    90 III
    create table strTable(SortKey,Val) as
    select 10,'AAA' from dual union
    select 20,'BBB' from dual union
    select 30,'CCC' from dual union
    select 40,'DDD' from dual union
    select 50,'EEE' from dual union
    select 60,'FFF' from dual union
    select 70,'GGG' from dual union
    select 80,'HHH' from dual union
    select 90,'III' from dual;
    col ConcatVal for a20
    select a.SortKey,
    Rtrim(substr(
    replace(
    replace(XMLAgg(XMLElement("dummy",b.Val) order by b.SortKey)
    ,'</dummy>'),'<dummy>'
    ,','),2),',') as ConcatVal
    from strTable a,
    (select SortKey,
    case when Row_Number() over(order by SortKey) <= 4
    then Val end as Val
    from strTable) b
    where a.SortKey >= b.SortKey
    group by a.SortKey;
    SortKey  ConcatVal
    ------- ---------------
    10 AAA
    20 AAA,BBB
    30 AAA,BBB,CCC
    40 AAA,BBB,CCC,DDD
    50 AAA,BBB,CCC,DDD
    60 AAA,BBB,CCC,DDD
    70 AAA,BBB,CCC,DDD
    80 AAA,BBB,CCC,DDD
    90 AAA,BBB,CCC,DDD
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,386 Black Diamond
    I am probably missing something here, but why not simply:
    SQL> select constraint_name, wm_concat(column_name) over(order by position) as column_names
      2  from user_cons_columns
      3  where constraint_name='DDL_COUNT_PK';
    
    CONSTRAINT_NAME                COLUMN_NAMES
    ------------------------------ ----------------------------------------
    DDL_COUNT_PK                   USERNAME
    DDL_COUNT_PK                   USERNAME,DDL
    DDL_COUNT_PK                   USERNAME,DDL,DT
    
    SQL> select  constraint_name,
      2          column_names
      3    from  (
      4           select  constraint_name,
      5                   wm_concat(column_name) over(order by position) as column_names,
      6                   count(*) over() cnt,
      7                   position
      8             from  user_cons_columns
      9             where constraint_name='DDL_COUNT_PK'
     10          )
     11    where position = cnt;
    
    CONSTRAINT_NAME                COLUMN_NAMES
    ------------------------------ ----------------------------------------
    DDL_COUNT_PK                   USERNAME,DDL,DT
    
    SQL> 
    SY.
  • 94799
    94799 Member Posts: 2,208
    why not simply
    Indeed, although I believe attempting to use user-defined aggregates as analytic functions can result in internal errors as late as some versions of 10g.
  • Ludock
    Ludock Member Posts: 185
    Thanks Sy,

    that was where im looking for.
This discussion has been closed.