Hey caffeine-addict,
Thanks. I was busy with a SQL Server problem. Oh my! Phew! Hehe.

Have a great Day and Weekend!





Please take a few minutes to provide feedback on the quality of service you received from our staff. The Department of Education values your feedback as a customer. Commissioner of Education Dr. Eric J. Smith is committed to continuously assessing and improving the level and quality of services provided to you.Simply use the link below. Thank you in advance for completing the survey.


http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]




-----Original Message-----
From: caffeine-addict
[mailto:oracledba-ezmlmshield-x65590764.[Email address protected]
Sent: Friday, February 08, 2008 1:57 PM
To: LazyDBA Discussion
Subject: Re: Another Friday Question

SELECT CAST(ROUND(
COALESCE(EXP(SUM(LOG(ABS(NULLIF(value,0))))),0)
* SIGN(MIN(ABS(value)))
* (COUNT(NULLIF(SIGN(value),1))%2*-2+1)
,0) AS INTEGER) AS product
FROM TEST
/

Fundamentally it just exploits the fact that the logarithm of the
product is equal to the sum of the logs:
LOG(a) + LOG(b) = LOG(a * b). The SIGN(MIN()) and COUNT(*) expressions
are there to handle negative values and zeros correctly.

LS

"640KB ought to be enough for anybody." - Bill Gates, 1981


----- Original Message ----
From: Chelur Jayadas <oracledba-ezmlmshield-x79983015.[Email address
protected]
To: LazyDBA Discussion <[Email address protected]
Sent: Friday, February 8, 2008 12:16:01 PM
Subject: Another Friday Question

Hi Ed,

Here is a Friday question for you. This time it is in SQL (I am just a
developer, so pardon me for the blasphemy!).

Consider a table TEST having a single column called num which is of
dataype
NUMBER. Assuming that the table has 5 rows, and all have positive
integers
in the num column. There are no NULLs.

If one has to find the total of the num column for all the rows of the
table, it is
quite simple. Use the aggregate function SUM().

But, if one has to find the product of the num column, how would you do
it ?
(It has to be done in a SQL query, and NOT a PL/SQL script or procedure)

Regards,
Jayadas

Here is the script to create the test environment :-
/* DROP TABLE TEST; */

CREATE TABLE TEST
(
num NUMBER NOT NULL
);

INSERT INTO TEST VALUES (1);
INSERT INTO TEST VALUES (2);
INSERT INTO TEST VALUES (3);
INSERT INTO TEST VALUES (4);
INSERT INTO TEST VALUES (5);

COMMIT;



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

Oracle LazyDBA home page