1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Real life Problem (Realy need help) with plsql procedure

Discussion in 'SQL PL/SQL' started by alepfi, Jun 21, 2012.

  1. alepfi

    alepfi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hello all,
    i have to solve following problem with plsql.

    Given:
    1) box with n=multiple acquisitions (following a)
    2) box with n payments for acquisitions (following pa)
    3) box with n sales (following s)
    4) box with n payments for sales (following ps)

    Each of the positions in the boxes has his own value.
    The positions are connected.


    1)An a can be connected with n pa like a pa can be connected with n a
    put also with n s.
    2)A s can be connected with n sa like a ps can be connected with n s
    put also with n a.

    I have to figure out what for a value has the connection (can be multiple solution i have to take one and the connection can be wrog then i have to giva errMsg).

    Example
    box 1 a)
    v1=1000
    v2=2000

    box 2 pa)
    v3=500
    v4=1000

    box 3 s)
    v5=5000

    box 4 ps)
    v6=3500

    Given connections are:
    v1=>v3
    v1=>v5
    v2=>v4
    v2=>v5
    v5=>v6

    possible solution for this is
    v1 =500> v3
    v1 =500> v5
    v2 =1000>v4
    v2 =1000>v5
    v5 =3500>v6

    create table TEST_A
    (
    cod NUMBER(20) not null,
    type VARCHAR2(2),
    value NUMBER(15,2)
    ) ;

    -- Create table
    create table test_b
    (
    cod_a1 number(20),
    cod_a2 number(20),
    value NUMBER(16,2)
    );
    alter table TEST_A
    add constraint PRIMARY_KEY primary key (COD)
    ;
    -- Create/Recreate primary, unique and foreign key constraints
    alter table test_b
    add constraint foreign_key_a1 foreign key (COD_A1)
    references test_a (COD);
    alter table test_b
    add constraint foreign_key_a2 foreign key (COD_A2)
    references test_a (COD);

    insert into test_a (COD, TYPE, VALUE)
    values (1, 'a', 1000.00);

    insert into test_a (COD, TYPE, VALUE)
    values (2, 'a', 2000.00);

    insert into test_a (COD, TYPE, VALUE)
    values (3, 'pa', 500.00);

    insert into test_a (COD, TYPE, VALUE)
    values (4, 'pa', 1000.00);

    insert into test_a (COD, TYPE, VALUE)
    values (5, 's', 5000.00);

    insert into test_a (COD, TYPE, VALUE)
    values (6, 'ps', 3500.00);

    insert into test_b (COD_A1, COD_A2, VALUE)
    values (1, 3, null);

    insert into test_b (COD_A1, COD_A2, VALUE)
    values (1, 5, null);

    insert into test_b (COD_A1, COD_A2, VALUE)
    values (2, 4, null);

    insert into test_b (COD_A1, COD_A2, VALUE)
    values (2, 5, null);

    insert into test_b (COD_A1, COD_A2, VALUE)
    values (5, 6, null);

    Hope i explained my problem.

    Thanks for every help.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This 'real life' example looks suspicously like a school project or homework.

    What have you written to solve this? Please post your attempt at a solution so we can see where you might have gone wrong.
     
  3. alepfi

    alepfi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    I tryed to use a bulck collect and go over a collection and substract always the lower value.
    But it s totally the wrong way.
    For me it looks like i have to solve a system of equations with n solutions.
    But i can't get it out how to solve it in plsql.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Please post the solution you tried.
     
  5. alepfi

    alepfi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Ok
    i post my real tabels + package ignore other fields important are :
    tcons_ordine:
    -cod reference to tcons_ordine_collegamento
    -cvaleuro is populated or-cvlaeuro_stimato is populated
    -direction (IN or OUT)
    -order_type(ORDER or MOP)
    (1) acquisitions => direction = IN && order_tpe=ORDER
    2) payments for acquisitions=> direction =OUT && order_tpe=MOP
    3) sales => direction = IN && order_tpe=ORDER
    4) payments for sales=> direction = OUT&& order_tpe=MOP
    )
    tcons_ordine_collegamento:
    cod_ordine_in
    cod_ordine_out
    amount

    You can find the tabels + packegs as attachemnts (hope it worked)
     

    Attached Files:

  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    If understand the problem correctly, i dont think you need to use PL/SQL block for the solution. Here is one SQl solution.

    Code (SQL):
    SQL> SELECT * FROM test_aa;

           COD TY      VALUE
    ---------- -- ----------
             1 a        1000
             2 a        2000
             3 pa        500
             4 pa       1000
             5 s        5000
             6 ps       3500

    6 ROWS selected.

    SQL> SELECT * FROM test_bb;

        COD_A1     COD_A2      VALUE
    ---------- ---------- ----------
             1          3
             1          5
             2          4
             2          5
             5          6

    SQL> SELECT COD_A1, COD_A2,
            (SELECT MIN (a1.VALUE)
             FROM  test_bb b1 ,test_aa a1
             WHERE  b.COD_A1 = b1.COD_A1
             AND b1.COD_A2 = a1.COD ) new_val
    FROM test_bb b  ;

        COD_A1     COD_A2    NEW_VAL
    ---------- ---------- ----------
             1          3        500
             1          5        500
             2          4       1000
             2          5       1000
             5          6       3500

    SQL>
    SQL>
    SQL> UPDATE test_bb b
    SET VALUE = (SELECT MIN (a1.VALUE)
                 FROM  test_bb b1 ,test_aa a1
                 WHERE  b.COD_A1 = b1.COD_A1
                 AND b1.COD_A2 = a1.COD );  

    5 ROWS updated.

    SQL> SELECT * FROM test_bb;

        COD_A1     COD_A2      VALUE
    ---------- ---------- ----------
             1          3        500
             1          5        500
             2          4       1000
             2          5       1000
             5          6       3500

    SQL>
     
  7. alepfi

    alepfi Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Is totally correct for this problem.
    But for an other example:
    Example
    box 1 a)
    v1=1000
    v2=1000

    box 2 pa)
    v3=500

    box 3 s)
    v4=1500

    connections:
    v1=>v3 (250) or other possible solution
    v2=>v3(250) or other possible solution
    v1=>v4(750)
    v2=>v4(750)

    I does not work.
    This example explains why i dont can only substract always the min value