Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    3

    Unanswered: beginner calcualtion error

    Hi all, I am literally new to SQL but have to pick up as fast as possible...

    While doing some sample calculations, I encounterd following calculation error:

    this should calculate the summarized receive_qty for certain parts from table 1

    select depot, part, sum (receive_qty)
    from ia_receiving
    where part IN (select part from ia_shipping)
    group by part, depot
    order by part;

    this should calculate the summarized receive_qty for certain parts from table 2

    select depot, part, sum (rec_qty)
    from ia_rec
    where part IN (select part from ia_shipping)
    group by part, depot
    order by part;

    Now I wanted to reduce the summarized receive_qty by rec_qty

    INSERT INTO ia_remainder
    (depot, part, qty)
    SELECT i1.depot, i1.part,
    (SUM (i2.receive_qty) - NVL (SUM (i1.receive_qty), 0) -- here is still a mistake
    ) AS qty
    FROM ia_rec i1, ia_receiving i2
    WHERE i1.part = i2.part AND i1.depot = i2.depot
    GROUP BY i1.depot, i1.part;

    When I pull the numbers seperately from table 1 and table 2 I get number like ~28000 and ~27000. The output after reducing should be ~1000 but it is 567766. I think , well I am sure, that there is a big mistake how I tried to link the tables.

    This is the result for table 1 receive_qty
    DEPOT PART SUM(RECEIVE_QTY)
    RE FK586AA #ABA 28,092.00
    RO FK586AA #ABA 31,752.00

    This is the result for table 2 rec_qty
    DEPOT PART SUM(RECEIVE_QTY)
    RE FK586AA #ABA 27,092.00
    RO FK586AA #ABA 30,752.00

    Now I would like to insert the difference of these values into ia_remainder.

    Can anybody help me how to do this right?

    Thanks
    Peter
    Last edited by ironpete; 01-22-09 at 23:05.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, you might use your queries as inline views:
    Code:
    insert into ia_remainder
      (depot, part, qty)
      (select x.depot, x.part, x.rec_qty - y.rec_qty
       from (select a.depot, a.part, sum(a.receive_qty) rec_qty
             from ia_receiving a
             where a.part in (select part from ia_shipping)
             group by a.depot, a.part
            ) x,
            (select b.depot, b.part, sum(b.rec_qty) rec_qty
             from ia_rec b
             where b.part in (select part from ia_shipping)
             group by b.depot, b.part
            ) y
       where x.depot = y.depot
         and x.part = y.part
      );
    NVLs, outer joins and similar might have to be used, but that can be added later if necessary.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ironpete
    INSERT INTO ia_remainder
    (depot, part, qty)
    SELECT i1.depot, i1.part,
    (SUM (i2.receive_qty) - NVL (SUM (i1.receive_qty), 0) -- here is still a mistake
    ) AS qty
    FROM ia_rec i1, ia_receiving i2
    WHERE i1.part = i2.part AND i1.depot = i2.depot
    GROUP BY i1.depot, i1.part;

    When I pull the numbers seperately from table 1 and table 2 I get number like ~28000 and ~27000. The output after reducing should be ~1000 but it is 567766. I think , well I am sure, that there is a big mistake how I tried to link the tables.
    Do "depot" and "part" form unique keys in both IA_REC and IA_RECEIVING?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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