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!

Sort multiple columns .

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

  1. Sandy84

    Sandy84 Active Member

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

    I have a query as below ;

    I need to fetch records order by multiple columns.
    e.g.

    select * from my_tab order by col1 desc ,col2 ;

    But , I need to sort based on a relation between col1 , col2

    e.g. say , one of the records to be fetched has col1 has value of 5 and col2 has a value of 6
    another record to be fetched has col1 has value of 2 and col2 has a value of 1
    Now , If I run the above query , first I will get the record( say , R1) that has col1,col2 values as 5,6 ; the record(say ,R2) that has col1,col2 values as 2,1 will come next.
    But , I want R2 to come first , I want to first fetch those records which have col1>col2
    Please advise how to model the query accordingly.
    Thanks.
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sandy,

    One way of doing it is as follows - I'm not sure if that's exactly what you want:
     
  3. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Rajen.

    Below is what I am using :

    with mytab as (SELECT pli.im_item,
    pli.loc_value,
    pli.xform_sellable_item,
    ils.stock_on_hand,
    pli.xform_yield_pct,
    ( pli.xform_yield_pct
    * (pli.tran_type_num * pli.total_wastage_qty * pli.pack_qty)
    / (1 - pli.xform_prod_loss_pct))
    qty,
    pli.tran_date,
    pli.eow_date,
    pli.im_inventory_ind,
    axgd.group_no
    FROM svc_posupld_line_item_gtt pli,
    item_loc_soh ils,
    apu_xform_item_group_detail axgd
    WHERE pli.sales_process_id = 623
    AND pli.error_msg IS NULL
    AND pli.im_item = ils.item
    AND pli.loc_value = ils.loc
    AND pli.im_item_xform_ind = 'Y'
    AND pli.im_pack_ind = 'N'
    AND pli.xform_sellable_item IS NOT NULL
    AND pli.im_item = axgd.xform_orderable_item
    AND pli.xform_sellable_item = axgd.xform_sellable_item
    ) select * from mytab order by (4-6) desc , 6;

    Output is :

    IM_ITEM LOC_VALUE XFORM_SELLABLE_ITEM STOCK_ON_HAND XFORM_YIELD_PCT QTY TRAN_DATE EOW_DATE IM_INVENTORY_IND GROUP_NO
    100071383 1521 100071295 0.2 0.02 0.2 2/12/2015 2/14/2015 Y 2
    100071375 1521 100071295 0.3 0.02 0.2 2/12/2015 2/14/2015 Y 2
    100071367 1521 100071295 0.5 0.02 0.2 2/12/2015 2/14/2015 Y 2
    100071404 1521 100071295 0.15 0.02 0.2 2/12/2015 2/14/2015 Y 3
    100071391 1521 100071295 0.1 0.03 0.3 2/12/2015 2/14/2015 Y 3
    100071359 1521 100071295 2 0.04 0.4 2/12/2015 2/14/2015 Y 2
    100071332 1521 100071295 2 0.2 2 2/12/2015 2/14/2015 Y 1
    100071324 1521 100071295 1 0.2 2 2/12/2015 2/14/2015 Y 1
    100071316 1521 100071295 1 0.2 2 2/12/2015 2/14/2015 Y 1
    100071341 1521 100071295 2.4 0.25 2.5 2/12/2015 2/14/2015 Y 1

    But , it's still not as expected ...

    Something like below is expected :

    IM_ITEM LOC_VALUE XFORM_SELLABLE_ITEM STOCK_ON_HAND XFORM_YIELD_PCT QTY TRAN_DATE EOW_DATE IM_INVENTORY_IND GROUP_NO
    100071359 1521 100071295 2 0.04 0.4 2/12/2015 2/14/2015 Y 2
    100071367 1521 100071295 0.5 0.02 0.2 2/12/2015 2/14/2015 Y 2
    100071375 1521 100071295 0.3 0.02 0.2 2/12/2015 2/14/2015 Y 2
    100071316 1521 100071295 1 0.2 2 2/12/2015 2/14/2015 Y 1
    100071324 1521 100071295 1 0.2 2 2/12/2015 2/14/2015 Y 1
    100071341 1521 100071295 2.4 0.25 2.5 2/12/2015 2/14/2015 Y 1
    100071391 1521 100071295 0.1 0.03 0.3 2/12/2015 2/14/2015 Y 3
    100071404 1521 100071295 0.15 0.02 0.2 2/12/2015 2/14/2015 Y 3

    Please suggest.
    Thanks.
     
  4. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sandy,

    Now this is interesting:

    is evaluated by Oracle to
    => "4" and "6" are not treated as column positions but numbers and -2 being a non-existent column position, there is no effective sorting. Sorting is done only on the "6" (6th column) which exists.

    So that's why your data is sorted in this manner.

    Try using :
     
  5. eras

    eras Active Member

    Messages:
    23
    Likes Received:
    9
    Trophy Points:
    90
    Location:
    Lithuania
    Sandy,

    .............
    order by case
    when col1 > col2 then
    0
    else
    1
    end, col1 desc, col2
     
  6. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank You Rajen/Eras ... the query is fetching expected results with either of the ways.

    Need your suggestions on the below ;
    Please advise if this cursor can be made dynamic so that it retrieves the latest values against the fields in the cursor.
    Currently , I am fetching the cursor into an object type and processing the records from this object type using loop.
    Say , I have 10 records in the object type to process , the program picks up the first record and processes it followed by other records with increasing sequence.
    Now , if the program , during the processing of nth record , makes changes to the stocks of (n+1)th record, then the stock of the (n+1)th record is updated accordingly ;say stock value for (n+1)th record changes from from 10 to 8.
    Now , when the program starts with the (n+1)th record , its stock value needs to be the latest one (i.e. 8), but as expected it's fetching 10 from the object type.
    Kindly advise if this can be achievable.
    Thanks.
     
  7. Sandy84

    Sandy84 Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Code Snippet below :
    ............................
    DECLARE
    .
    .
    .
    CURSOR C_MAIN
    IS
    select * from (SELECT pli.im_item,
    pli.loc_value,
    pli.xform_sellable_item,
    ils.stock_on_hand,
    pli.xform_yield_pct,
    ( pli.xform_yield_pct
    * (pli.tran_type_num * pli.total_wastage_qty * pli.pack_qty)
    / (1 - pli.xform_prod_loss_pct))
    qty,
    pli.tran_date,
    pli.eow_date,
    pli.im_inventory_ind,
    axgd.group_no
    FROM svc_posupld_line_item_gtt pli,
    item_loc_soh ils,
    axgd
    WHERE pli.sales_process_id = I_sales_process_id
    AND pli.error_msg IS NULL
    AND pli.im_item = ils.item
    AND pli.loc_value = ils.loc
    AND pli.im_item_xform_ind = 'Y'
    AND pli.im_pack_ind = 'N'
    AND pli.xform_sellable_item IS NOT NULL
    AND pli.im_item = axgd.xform_orderable_item
    AND pli.xform_sellable_item = axgd.xform_sellable_item
    ORDER BY stock_on_hand desc,qty desc) order by case when stock_on_hand>=qty then 1 else 0 end desc;

    TYPE ils_mp_type IS TABLE OF C_MAIN%ROWTYPE
    INDEX BY BINARY_INTEGER;

    L_ils_mp_tab ILS_MP_TYPE;
    .
    .
    .
    BEGIN
    .
    .
    .
    OPEN C_MAIN;

    FETCH C_MAIN
    BULK COLLECT INTO L_ils_mp_tab;

    CLOSE C_MAIN;
    .
    .
    .

    IF L_ils_mp_tab.FIRST IS NOT NULL
    THEN
    FOR soh_rec IN L_ils_mp_tab.FIRST .. L_ils_mp_tab.LAST
    LOOP
    .
    .
    .
     
  8. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sandy,

    This is perfectly in line with how oracle processes cursors by providing a read consistent view of the data.
    Your table will contain a "snapshot" of the data as it was at the time of fetch and will not be updated if you perform any updates within your cursor-loop.

    However, in the processing of your table of records, you could eventually programatically handle the update of the collection along with the update of the physical table. This assumes that you have fetched ALL your records once for all in the collection and this might have considerable impact on performance (memory) if the cursor retrieves a large number of records.
     
  9. Sandy84

    Sandy84 Active Member

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

    Yes , all my records are being fetched by the cursor and stored in the collection at once.
    This is not a big volume of data and I don't think this is going to hit the performance.
    Please suggest as to how to update the collection along with the physical table.
    Is it feasible to pass the object type as one of the parameters in the function calls where I am updating the physical table and update the collection there.
    Please advise.
    Thanks.
     
  10. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Sandy,

    Sure you can do that.
    Here's a simple demo example :

    The tricky part would be getting the "p_index" (i.e. knowing which record to update in the collection) and this you have to determine by looking for the record with it's unique/primary key.
     
    Sandy84 likes this.
  11. Sandy84

    Sandy84 Active Member

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

    Thanks for your valuable inputs.
    The code is working fine.

    Regards,
    Sandy