Home » SQL & PL/SQL » SQL & PL/SQL » Build custom sum function
Build custom sum function [message #211642] Sat, 30 December 2006 05:53 Go to next message
moody_4u
Messages: 16
Registered: May 2005
Junior Member
Hello folks..

i am glad to be a member on this great forum over the web...

the problem that going in my mind that.. could i build a custom function that calculate sum of a column without using oracle stored function (sum)????

i meant

select custom_sum(sal) from emp;
???

could it work?
Re: Build custom sum function [message #211643 is a reply to message #211642] Sat, 30 December 2006 06:30 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course, it would work.

But would alse be a worse solution than built-in SUM function.
Re: Build custom sum function [message #211645 is a reply to message #211643] Sat, 30 December 2006 06:39 Go to previous messageGo to next message
moody_4u
Messages: 16
Registered: May 2005
Junior Member
ty for ur fast response

i know it's worst but i am in case study i want to know how oracle sum function work

i want to create function like it

anybody know how can it be done???

ty
Re: Build custom sum function [message #211649 is a reply to message #211645] Sat, 30 December 2006 08:26 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You know table name.
You know column name.
Iterate through all records and add first record's column value with second record's column value with ... nth records's column value.
Return sum you got.
Re: Build custom sum function [message #211651 is a reply to message #211642] Sat, 30 December 2006 09:22 Go to previous messageGo to next message
moody_4u
Messages: 16
Registered: May 2005
Junior Member
man I'm familiar with the algorithm Smile

but i need help in the code

just like headlines

the condition to stop(with code)
and how to pass type column to function

ty in advance
Re: Build custom sum function [message #211664 is a reply to message #211651] Sat, 30 December 2006 20:13 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To build an aggregate function, you need to use the Data Cartridge features. There are examples on this site - search for CONCAT_ALL.

Ross Leishman
Re: Build custom sum function [message #211678 is a reply to message #211664] Sun, 31 December 2006 04:16 Go to previous messageGo to next message
moody_4u
Messages: 16
Registered: May 2005
Junior Member
thanx dude

Razz
Re: Build custom sum function [message #211681 is a reply to message #211678] Sun, 31 December 2006 05:43 Go to previous messageGo to next message
moody_4u
Messages: 16
Registered: May 2005
Junior Member
hello folks

i have solved it and wanna share the solution with u
it depend on user-defined aggregate functions

create or replace type CustomSumImpl as object
(
sum NUMBER,
static function ODCIAggregateInitialize(sctx IN OUT CustomSumImpl) return number,
member function ODCIAggregateIterate(self IN OUT CustomSumImpl,value IN number) return number,
member function ODCIAggregateTerminate(self IN CustomSumImpl,returnValue OUT number, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT CustomSumImpl, 
ctx2 IN CustomSumImpl) return number
);
========
create or replace type body CustomSumImpl is 
static function ODCIAggregateInitialize(sctx IN OUT CustomSumImpl) 
return number is 
begin
sctx := CustomSumImpl(0);
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT CustomSumImpl, value IN number) 
return number is
begin
self.sum := self.sum + value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN CustomSumImpl, returnValue OUT number, flags IN number)
return number is
begin
returnValue := self.sum;
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT CustomSumImpl, ctx2 IN CustomSumImpl) 
return number is
begin
self.sum := ctx2.sum;
return ODCIConst.Success;
end;
end;

==================

create or replace function CustomSum (input NUMBER)
RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING CustomSumImpl;
==========================
select CustomSum(sal)
from emp;

Re: Build custom sum function [message #211688 is a reply to message #211681] Sun, 31 December 2006 20:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Cool, thanks for that. I'll bookmark this so that I can find it if SUM() ever stops working. Wink
Re: Build custom sum function [message #211711 is a reply to message #211688] Mon, 01 January 2007 10:20 Go to previous message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I write a custom SUM() and AVG() for INTERVAL DAY TO SECOND values, since Oracle seem to have forgotten to overload the existing functions. Perhaps they will get around to it in 11g.

www.williamrobertson.net/code/agg_dsinterval.typ.txt
Previous Topic: delete several columns from a table
Next Topic: types of index
Goto Forum:
  


Current Time: Sun Dec 04 16:58:28 CST 2016

Total time taken to generate the page: 0.21438 seconds