Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to rewrite the cursor query by removing the OR clause in where conditiion ?

msApr 13 2022 — edited Apr 13 2022

Hi Everyone,
I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Can somebody please tell me how to rewrite the below cursor query so that
it does not use the "OR" clause in the where condition
for performance benefits.
Basically the OR condition should be removed appropriately in the query.

       CURSOR c_picklist_dtl IS
       SELECT DISTINCT pd.dc_code,
                       pd.storer,
                       pd.picklist_key,
                       pd.pickdetail_key,
                       pd.line_no,
                       pd.pick_type,
                       pd.case_id,
                       pd.order_type,
                       pd.sub_type,
                       pd.order_no,
                       pd.item,
                       pd.consignee,
                       pd.bin_code,
                       pd.lot,
                       pd.pallet_id,
                       pd.packkey,
                       pd.to_pick_qty,
                       am.bin_code dispatch_bin_code,
                       ph.wave_id,
                       ph.po_no,
                       pd.pick_area_type,
                       pd.assignment_id,
                       pd.pick_method,
                       pd.pack_method,
                       pd.ord_priority,
                       pd.pick_seq_no 

         FROM table1 ph,
              table2 pd,
              table3 ot,
              table4 am
        WHERE    pd.dc_code = ph.dc_code
              AND pd.storer = ph.storer
              AND pd.picklist_key = ph.picklist_key
              AND ph.dc_code = p_dc_code
              AND ph.storer = p_storer
                AND (  (   ph.po_no = p_order_no
                       AND p_order_prefix = 'X'
                       AND pd.pick_method = 'P'
                       AND pd.status = 'E' 
                                           )
                   OR (   ph.wave_id = p_order_no
                       AND p_order_prefix = 'W'
                       AND pd.pick_method = 'P'
                       AND pd.status = 'E' 
                                           ) 

                  )
              AND pd.item = p_item
              AND pd.consignee = p_consignee
              AND pd.dc_code = ot.dc_code
              AND pd.order_type = ot.order_type
              AND pd.sub_type = ot.sub_type
              AND am.dc_code(+) = ot.dc_code
              AND am.dc_area(+) = ot.dispatch_area
              AND pd.to_pick_qty > 0
          ORDER BY pd.dc_code,
              pd.item,
              pd.consignee,
              pd.pick_seq_no,
              pd.pickdetail_key,
              pd.line_no;

Thanks

This post has been answered by mathguy on Apr 13 2022
Jump to Answer
Comments
Post Details
Added on Apr 13 2022
9 comments
502 views