RE: Instead of SUM() I require MULTIPLY

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Sat, 10 Dec 2005 09:55:41 +0100
Message-ID: <000c01c5fd67$800f1ca0$6501a8c0@LAPTOPLEX>

you can build your own aggregate functions in Oracle, by defining four "primitives" (initialize, iterate, merge, and terminate)  

for example, I have my own improved SUM function that (in my opinion correctly) returns zero instead of NULL when applied on the empty set :-)  

check out the Oracle documentation: OracleR Database Application Developer's Guide - Fundamentals
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_packages .htm#i1008575  

and look for the section:

Coding Your Own Aggregate Functions

cheers,  

Lex.

---

Jonathan Lewis Seminar <http://www.naturaljoin.nl/events/seminars.html> http://www.naturaljoin.nl/events/seminars.html

---

 

---

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ethan Post
Sent: Friday, December 09, 2005 23:13
To: oracle-l_at_freelists.org
Subject: Instead of SUM() I require MULTIPLY

I just came up with a function I would like, but don't think exists.  

TABLE TEST (X NUMBER)

---

2
2
4  

select sum(x) from test;  

will return 8...  

what I need is  

select multiply(x) from test;  

will return 16, because 2*2*4 is 16.  

Anyone ever seen a SQL aggregate function like this? I don't think it exists but I hold out hope.  

• Ethan

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Dec 10 2005 - 02:55:50 CST