Re: Marterialised View Problem

From: Kevin S <SearleK_at_googlemail.com>
Date: Thu, 28 May 2009 03:42:23 -0700 (PDT)
Message-ID: <13a4177f-c1fc-4327-88a4-fbc065904cc3_at_u10g2000vbd.googlegroups.com>

I have had a go at ptting the function in a pakage as show below but the error I am getting when I come to call it is

    select c.primarykey, c.sourcetable, udaggfunction_pkg.stragg_fnc (c.lcrcomment) comments

                                                                     *

    ERROR at line 2:
    ORA-00979: not a GROUP BY expression

Is this a limitation of user defined aggregate function or have I miscoded something?

Thanks

DROP TABLE item_comment;

DROP MATERIALIZED VIEW item_comm_mv;
DROP MATERIALIZED VIEW item_comm_fnc_mv;
DROP MATERIALIZED VIEW item_comm_pkg_mv;

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;

create or replace type string_agg_type as object (
  total varchar2(4000),
  static function ODCIAggregateInitialize(     sctx IN OUT string_agg_type )
  return number,

  member function ODCIAggregateIterate(
    self IN OUT string_agg_type ,
    value IN varchar2 )
  return number,

  member function ODCIAggregateTerminate(self IN string_agg_type,     returnValue OUT varchar2,
    flags IN number)
  return number,

  member function
    ODCIAggregateMerge(

      self IN OUT string_agg_type,
      ctx2 IN string_agg_type)

  return number
);
/

create or replace type body string_agg_type is

  static function ODCIAggregateInitialize(     sctx IN OUT string_agg_type)
  return number
  is
  begin

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

  end;

  member function ODCIAggregateIterate(
    self IN OUT string_agg_type,
    value IN varchar2 )
  return number
  is
  begin

     self.total := self.total || ',' || value;
     return ODCIConst.Success;

  end;

  member function ODCIAggregateTerminate(     self IN string_agg_type,
    returnValue OUT varchar2,
    flags IN number)
  return number
  is
  begin

     returnValue := ltrim(self.total,',');
     return ODCIConst.Success;

  end;

  member function ODCIAggregateMerge(
    self IN OUT string_agg_type,
    ctx2 IN string_agg_type)
  return number
  is
  begin

     self.total := self.total || ctx2.total;
     return ODCIConst.Success;

  end;

end;
/

CREATE or replace FUNCTION stragg(input varchar2 ) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

CREATE or replace PACKAGE udaggfunction_pkg IS

  FUNCTION stragg_fnc(pi_input IN VARCHAR2)   RETURN varchar2
  PARALLEL_ENABLE AGGREGATE USING string_agg_type;

END udaggfunction_pkg;
/

show errors

CREATE OR REPLACE PACKAGE BODY udaggfunction_pkg IS

  FUNCTION stragg_fnc(pi_input IN VARCHAR2)   RETURN VARCHAR2
  PARALLEL_ENABLE AGGREGATE USING string_agg_type;

END udaggfunction_pkg;
/

show errors

create materialized view item_comm_fnc_mv as select item_id, item_desc, stragg(commnt) comments from item_comment
group by item_id, item_desc
/

create materialized view item_comm_pkg_mv as select item_id, item_desc, stragg(commnt) comments from item_comment
group by item_id, item_desc
/

select item_id, item_desc, comments from item_comm_fnc_mv
/

select item_id, item_desc, comments from item_comm_pkg_mv
/
Received on Thu May 28 2009 - 05:42:23 CDT