Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Unanswered: Help on calculation

    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. #2
    Join Date
    Mar 2007
    Posts
    623
    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. #3
    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


    Quote 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. #4
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    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;

    you need to fill up the ... lines. Hope it will help you. if not please give more information
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •