Re: Marterialised View Problem

From: Randolf Geist <mahrah_at_web.de>
Date: Thu, 28 May 2009 07:13:30 -0700 (PDT)
Message-ID: <0acbc1e5-26d4-46b9-9c53-137b02bdb241_at_q16g2000yqg.googlegroups.com>



On May 27, 2:44 pm, Kevin S <Sear..._at_googlemail.com> wrote:
> David,
>
> Thank you very much for this. You have just opened up a whole new
> world to me.
>
> I shall go away and play with it. Not sure my project manager is going
> to be too please about that but whatever.
>
> KS

Kevin,

please note that you don't need necessarily the aggregate functions to perform such a string aggregation, although it's a neat feature. There are numerous plain SQL solution available.

A nice collection of those can be found here on the SQL snippets site: http://www.sqlsnippets.com/en/topic-11787.html

To get you started, here a variant using a hierarchical query based on above sample table:

create table item_comment(

        item_id number,
        item_desc varchar2(40),
        commnt varchar2(40)

);

insert all
into item_comment
values(1,'A','C ALPHA')
into item_comment
values(1,'A','C BETA')
into item_comment
values(1,'A','C GAMMA')
into item_comment
values(2,'B','C DELTA')
into item_comment
values(3,'C','C EPSILON')
into item_comment
values(3,'C','C ZETA')
select * From dual;

commit;

select

        item_id
      , item_desc
      , substr(sys_connect_by_path(commnt, ','), 2) as aggr
from (
  select
          item_id
        , item_desc
        , row_number() over (partition by item_id order by commnt)
row_no
        , commnt
  from
          item_comment

)
where

        connect_by_isleaf = 1
start with

        row_no = 1
connect by

        prior row_no = row_no - 1
and     prior item_id = item_id
order siblings by
        row_no

;

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Thu May 28 2009 - 09:13:30 CDT

Original text of this message