>> I require a query to return the primary key from the main table and
the PRODUCT (i.e. all numeric values multiplied together) of the three
child records, much like a SUM would add them together. <<
Here is a version of the aggregate product function in SQL. You will
need to have the logorithm and exponential functions. They are not
standards, but they are very common.
The idea is that there are three special cases - all positive numbers,
one or more zeroes, and some negative numbers in the set.
You can find out what your situation is with a quick test on the
sign() of the minimum value in the set.
Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.
SELECT CASE MIN (SIGN(nbr))
WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers
WHEN 0 THEN 0.00 -- some zeroes
WHEN -1 -- some negative numbers
THEN (EXP(SUM(LN(ABS(nbr))))
* (CASE WHEN
MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1
THEN -1.00 ELSE 1.00 END)
ELSE NULL END AS big_pi
FROM NumberTable;
You will need to have the logarithm, exponential, mod and sign
functions in your SQL product. They are not standards, but they are
very common.
The idea is that there are three special cases - all positive numbers,
one or more zeros, and some negative numbers in the set. You can find
out what your situation is with a quick test on the sign() of the
minimum value in the set.
Within the case where you have negative numbers, there are two
sub-cases: (1) an even number of negatives or (2) an odd number of
negatives. You then need to apply some High School algebra to
determine the sign of the final result.
Itzak Ben-Gan had problems in implementing this in SQL Server that are
worth passing along in case your SQL product also has them. The query
as written returns a domain error in SQL Server, even though it should
not had the result expressions in the CASE expression been evaluated
<i>after<i> the conditional flow had performed a short circuit
evaluation. Examining the execution plan of the above query, it
looks like the optimizer evaluates all of the possible result
expressions in a step prior to handling the flow of the CASE
expression.
This means that in the expression after WHEN 1 ... the LN() function
is also invoked in an intermediate phase for zeros and negative
numbers, and in the expression after WHEN -1 ... the LN(ABS()) is
also invoked in an intermediate phase for 0's. This explains the
domain error.
To handle this, I had to use the ABS() and NULLIF() functions in the
positive numbers when CLAUSE, and the NULLIF() function in the
negative numbers when CLAUSE:
...
WHEN 1 THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00)))))
and
...
WHEN -1
THEN EXP(SUM(LN(ABS(NULLIF(result, 0.00)))))
* CASE ...
