CODEWITHSUNDEEP
oracle-databaseoracle11g
Sachin S
Sachin S

Reputation: 396

Row data multiplication in Oracle

Consider following table where I am doing row data multiplication:

with v1 (member_id, the_number) as 
(
select 1, 3 from dual union all
select 1, 5 from dual union all
select 2, 2 from dual union all
select 2, 3 from dual union all
select 2, 4 from dual union all
select 3, 9 from dual union all
select 3, 3 from dual union all
select 3, 2 from dual
)
select member_id, EXP(SUM(LN(the_number))) from v1
GROUP BY member_id;

It gives the correct result as:

MEMBER_ID   EXP(SUM(LN(THE_NUMBER)))
1           15
2           24
3           54

The moment I put a negative value in the the_number column, I get the following Oracle error: ORA-01428: argument 'x' is out of range This is because the range for LN () argument is > 0.

How can I modify the query so that I can have negative values as well in the_number column? I am using Oracle 11g.

Upvotes: 0

Views: 348

Answers (1)

forpas
forpas

Reputation: 164139

Get the product of the absolute values of the numbers and finally multiply by -1 or 1 depending on whether there is an odd or even number of negative numbers:

select 
  member_id,
  CASE WHEN MOD(SUM(CASE WHEN the_number < 0 THEN 1 ELSE 0 END), 2) = 1 THEN -1 ELSE 1 END *
  EXP(SUM(LN(ABS(the_number)))) from v1
GROUP BY member_id;

See the demo.

Upvotes: 2

Related Questions