All Forums Database
Gaurav_Mishra 3 posts Joined 11/07
28 May 2008
Perform Multiplication of all the values for a Column

Dear All,Is their any way to perform the multiplication of all the values for a column using recursion.Regards,Gaurav.e.gTable Dept_CountDept_Name Dept_CountIT 2Finance 3Treasurey 5CS 1 The product of all the records for Dept_Count Col should have to be 30(2*3*5*1)

CarlosAL 512 posts Joined 04/08
28 May 2008

Hi.This is my first answer to any post here (I'm quite new to Teradata, big DB background though).Recalling from my maths: 1. logarithm converts products in sums.2. logarithm and exponentiation are inverse functions.SoSELECT EXP(SUM(LN(dept_count))) from Dept_Count should give you the answer.Cheers.Carlos.

SN 77 posts Joined 01/07
28 May 2008

hi carlos,just browsing thru the posts and your solution is an interesting way of using the available functions effectivily.....thats cool!

Gaurav_Mishra 3 posts Joined 11/07
28 May 2008

Hi Carlos,What if we need the sign also in the product?Say if the values are like 1-45Then product should have to be -20 but using log it won't work.Using ABS(absolute) will give you the product but will supress the sign in the Product value.Any suggestions on this?

CarlosAL 512 posts Joined 04/08
29 May 2008

Hi again>>"Then product should have to be -20 but using log it won't work.>>Using ABS(absolute) will give you the product but will supress>>the sign in the Product value.>>Any suggestions on this?"Try: SELECT (case when sum(case when dept_count < 0 then 1 else 0 end) mod 2 = 1 then -1 else 1 end) * EXP(SUM(LN(ABS(dept_count)))) FROM dept_countHope this helps.Cheers.Carlos

Gaurav_Mishra 3 posts Joined 11/07
29 May 2008

Hi Carlos,Firstly thanks a lot for your reply.I also tried the same and it is working.sel cast (EXP(SUM(LN(abs(dept_count)))) as decimal ) *(case when ((-1 *sum(case when dept_count<0 then 1 else 0 end)) mod 2 )=0then 1else -1end) as COL_PROD from dept;Table DeptDept_Name Dept_CountA 1B -2 C -3

dhirajpalse 36 posts Joined 01/08
29 May 2008

Hi,i created a table 'dh' in 'au' database with column name as 'A' of integer data type.Data in this column is as follows.A-10-9-612345I wrote the query, which is just an enhancement of Carlos's query.select (-1**dt3.c3) * dt1.c1*dt2.c2 as product_col from((select exp(sum(ln(abs(a)))) from au.dh where a <0) dt1 (c1)join (select exp(sum(ln(a))) from au.dh where a > 0) dt2 (c2)on 1=1join(select count(a) from au.dh where a<0) dt3 (c3)on 1=1)this gives the product of the column data by taking care of the signs of numbers.output with the given data is -64800.00hope this will help u.

Dhiraj

dhirajpalse 36 posts Joined 01/08
24 Jul 2008

A small change in the above query..select (-1**dt3.c3) * dt1.c1*dt2.c2 as product_col from((select COALESCE(exp(sum(ln(abs(a)))),1) from au.dh where a <0) dt1 (c1)join (select exp(sum(ln(a))) from au.dh where a > 0) dt2 (c2)on 1=1join(select count(a) from au.dh where a<0) dt3 (c3)on 1=1)

Dhiraj

mina 1 post Joined 06/12
19 Jun 2012

what does this warning mean in teradata-

 

Warning: 2892 Null value eliminated in set function
 

You must sign in to leave a comment.