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!

Fetching the Dynamic Values from a cursor.

Discussion in 'SQL PL/SQL' started by Sandy84, Mar 3, 2015.

  1. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Hi All,

    Here is my issue (from a high level).


    Scenario :


    I have a set of items across 3 different groups



    Group 1

    Group 2

    Group 3





    Group



    Item



    Item Quantity



    Item yield %



    Sold Item




    1



    I1



    2



    20



    I11




    1



    I2



    2



    20




    1



    I3



    3



    20




    1



    I4



    1



    25




    2



    I5



    2



    4




    2



    I6



    3



    2




    2



    I7



    5



    2




    2



    I8



    1



    2




    3



    I9



    2



    3




    3



    I10



    3



    2





    These items together make the item I11 based on their percentage (Item % column).



    e.g. to make X kg of I11 , 20% of available item quantity of item I1 +20% of available item quantity of item I2 + …..+3% of available item
    quantity of item I10 are used.




    a.e.g. to make 10 kgs of item I11 ,

    20% of 10 kg=2 kg needed from I1

    20% of 10 kg=2 kg needed from I2

    20% of 10 kg=2 kg needed from I3

    25% of 10 kg=2.5 kg needed from I4

    4% of 10 kg=0.4 kg needed from I5

    2% of 10 kg=0.2 kg needed from I6

    2% of 10 kg=0.2 kg needed from I7

    2% of 10 kg=0.2 kg needed from I8

    3% of 10 kg=0.3 kg needed from I9

    2% of 10 kg=0.2 kg needed from I10



    If all the ordered items are sufficiently available to make Xkg (10kg) of sold item , then no issues.



    But , even though some of the items are not available (i.e.Item Quantity is 0) ; then the program needs to prorate that missing amount (against
    the un-available item) across the rest of the items uniformly to make the sold item I11.
    a.e.g. if in the above example , 2 kg of I2 is not available ,
    then 2 kg of I2 needs to be prorated across the rest of the items so that
    finally 10 kg of sold quantity is made.


    i.e. if an item has no quantity against it , program will try prorate the required amount from the same group first , if that group items
    have sufficient quantities against them and are able to do the proration, then it’s fine.


    If the adjustment still could not be made in the same group , it will call other groups based on some rules.



    Now , in this process , items of a particular groups (e.g. Group 1) are called multiple times which cause multiple updates in the item quantity in the same loop.



    (The driving cursor fetches one item at a time to run through the loop)




    e.g. in the main driving cursor , I have items I1,I2 ,I3,I4 …sorted in the same order ;




    Now , say , item I1 has no quantity , so the necessary adjustment happens across I2,I3,I4 … and so on in the loop.



    Now , when the item I2 turn comes in the same loop , the main driving cursor still holds the old item quantity value of I2 whereas it’s
    already changed during adjustment for Item i1.



    e.g. Program needs to adjust 2 kgs of I1 ; as part of which item I2 contributes ~ 0.6 kg (=2*20%/65%) for the adjustment. So , it’s quantity changes to 2-0.6=0.4
    ; but the cursor still fetches the old value of 2.



    So ,subsequently it’s resulting into incorrect calculations.

    I tried with a couple of options i.e. 1.re-opening the cursor , 2. storing the updated values in a record type , but it's not yielding expected results.

    Please suggest how to overcome this issue .



    Thanks in Advance.

    Apologies for the data formatting as it seems not appearing in a proper tabular format.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post your code, please, so we can see what it is you're doing.
     
  3. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    I am getting this below error while posting to the forum.

    Oops! your Post seems to be a SPAM! If you are posting live links, you need to have made at least 5 Posts as required by the administrator. Please edit your post or contact the administrator.
     
  4. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    PFA the code.
     

    Attached Files: