Re: possible/impossible query?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 19 Apr 2002 18:06:01 GMT
Message-ID: <d0Zv8.54423$zN.26166038@twister.socal.rr.com>

You didn't mention what version of Oracle. I'll start with a 9i solution because it was more interesting.

using a user-defined aggregate function in 9i ...

select col1, col2, col3, agg_concat(col4) as col4, agg_concat(col5) as col5, agg_concat(col6) as col6, agg_concat(col7) as col7
from info group by col1, col2, col3

C C C COL4 COL5 COL6 COL7 - - - ---------- ---------- ---------- ----------

1 2 3 4ad        5          6b         7ce
a b c d          dgi        eh         f
z y x w          v          u          t

3 rows selected.

agg_concat is the aggregate function I wrote ...

create or replace type agg_concat_type as object (

current_string varchar2(100),

static function ODCIAggregateInitialize(sctx in out agg_concat_type)

return number,

member function ODCIAggregateIterate(self in out agg_concat_type,

value in varchar2) return number,

member function ODCIAggregateTerminate(self in agg_concat_type,

return_value out varchar2, flags in number) return number,

member function ODCIAggregateMerge(self in out agg_concat_type,

ctx2 in agg_concat_type) return number

);
/

create or replace type body agg_concat_type is

static function ODCIAggregateInitialize(sctx in out agg_concat_type)

return number is
begin

        sctx := agg_concat_type(null);
        return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self in out agg_concat_type, value in varchar2)

return number is
begin

        current_string := current_string || value;
        return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self in agg_concat_type,

return_value out varchar2, flags in number) return number is begin

        return_value := current_string;
        return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self in out agg_concat_type,

ctx2 in agg_concat_type) return number is begin

        current_string := current_string || ctx2.current_string;
        return ODCIConst.Success;

end;
end;
/

create or replace function agg_concat (input varchar2) return varchar2

parallel_enable aggregate using agg_concat_type; /

Note: my function doesn't handle strings over 100 characters. My first implementation used 32767 but then I go this error...

ORA-01467: sort key too long

I'll have to delve deeper into that to understand what the best solution is to that type of problem.

As for 8i, this really convoluted solution occurs to me ...

select col1, col2, col3,

  utl_raw.cast_to_varchar2(hextoraw(sum(col4))) as col4,
  utl_raw.cast_to_varchar2(hextoraw(sum(col5))) as col5,
  utl_raw.cast_to_varchar2(hextoraw(sum(col6))) as col6,
  utl_raw.cast_to_varchar2(hextoraw(sum(col7))) as col7

from (
select col1, col2, col3,

    rawtohex(utl_raw.cast_to_raw(rpad(col4, piece, chr(0)))) as col4,
    rawtohex(utl_raw.cast_to_raw(rpad(col5, piece, chr(0)))) as col5,
    rawtohex(utl_raw.cast_to_raw(rpad(col6, piece, chr(0)))) as col6,
    rawtohex(utl_raw.cast_to_raw(rpad(col7, piece, chr(0)))) as col7

from (
select col1, col2, col3,

      dense_rank() over (
        partition by col1, col2, col3
        order by col4 desc, col5 desc, col6 desc, col7 desc) as piece,
      col4, col5, col6, col7

from info
)
)
group by col1, col2, col3
/

There's probably an easier way but it doesn't occur to me.

Richard

dxcreepin wrote:
>
> I am looking for guidance on a problem that has perplexed me for the
> past couple of months. Here is a description of what I have and what I
> am trying to do:
>
> Table: INFO (all the columns are varchar(1) – in this example)
> Col1 col2 col3 col4 col5 col6 col7
> a b c d d e f
> a b c g h
> a b c i
> 1 2 3 4 5 6 7
> 1 2 3 a b c
> 1 2 3 d e
> z y x w v u t
>
> The first 3 columns are not null and the remaining columns can be
> null. I am trying to develop a query and/or procedure/function/package
> to return this type result:
>
> Col1 col2 col3 col4 col5 col6 col7
> a b c d dgi eh f
> 1 2 3 4ad 5 6b 7ce
> z y x w v u t
>
> If there are duplicates of the first 3 columns, the remaining rows
> with not null data will be concatenated together into one field.
> Eventually I would like to use this in Oracle Reports. If you are
> familiar with PVCS Dimensions, I am trying to emulate the behavior of
> thee unlimited text fields.
>
> Can you please help me try to find a solution to this?
Received on Fri Apr 19 2002 - 13:06:01 CDT