1. Registered User
Join Date
Feb 2009
Posts
2

Hello,

I have a huge table A which has about 40 fields, which are
U, V, W, X, Y, Z, A1, A2 ...A32
A1 to A32 store values in number.

I have another table B which stores 32 coefficient values
FIELD_NAME VALUE
A1 V1
A2 V2
...
A32 V32

Now, I want to count this for each record in table A
SUM(A1*V1, A2*V2, ... A32*V32)

How can I write the PL/SQL code for such calculation?

Thank you!

2. Registered User
Join Date
Mar 2007
Posts
629
Simple pivotting the B table will give you those values in one row:
Code:
```SELECT SUM( CASE WHEN field_name = 'A1' THEN value END ) v1,
SUM( CASE WHEN field_name = 'A2' THEN value END ) v2,
<etc. for other values>
FROM b;```
Then, simply join it with A table and do the calculation.

3. Registered User
Join Date
Feb 2009
Posts
2

## re:

Thank you.
I was trying to say that the result I want to get for each record in table A is :
A.A1*B.V1 + A.A2*B.V2 + ... + A.A32*B.V32

Originally Posted by flyboy
Simple pivotting the B table will give you those values in one row:
Code:
```SELECT SUM( CASE WHEN field_name = 'A1' THEN value END ) v1,
SUM( CASE WHEN field_name = 'A2' THEN value END ) v2,
<etc. for other values>
FROM b;```
Then, simply join it with A table and do the calculation.

4. Registered User
Join Date
Jan 2009
Location
Posts
51
First of all I do not fully understand your requirement. I think you need to provide more clear information.

By my understanding the following query may works for you

SELECT sum ( x."value") from

( Select v1 * (select A1 from A) "value" from B
UNION
Select v2 * (select A2 from A) "value" from B
.
.
.
.
UNION
Select v32 * (select A32 from A) "value" from B
) x;