Forum Stats

  • 3,827,807 Users
  • 2,260,823 Discussions
  • 7,897,380 Comments

Discussions

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

gg
gg Member Posts: 150 Bronze Badge
edited Apr 13, 2022 5:37AM in SQL & PL/SQL

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           

Best Answer

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond
    Answer ✓

    Even after @alvinder fixes that problem, he has not eliminated the OR-based condition. "something IN (..., ..., ...)" is expanded to an OR-based condition, it's just another way to write the same thing.

    I won't do the work for you - your problem, your work. I will just tell you what you need to change. An OR-based condition like that can be rewritten as a UNION ALL (a low-cost set operation on rowsets) of the result of two disjoint queries. Something like this:

    select ...
    from ...
    where ...
    ... and ph.po_no = p_order_no
        and p_order_prefix = 'X'
        and pd.pick_method = 'P'
        and pd.status = 'E' 
    ........
    UNION ALL
    select ...
    from ...
    where ...
    ... and ph.wave_id = p_order_no
        and p_order_prefix = 'W'
        and pd.pick_method = 'P'
        and pd.status = 'E' 
    ........
    ORDER BY ...     -- this applies to the RESULT of UNION ALL 
    

    Since p_order_prefix is 'X' in one branch of UNION ALL and 'W' in the other, you don't need to worry about double-counting (the same row appearing in both branches), which is the only thing you must be careful with in a situation like this.

    I am a bit surprised that the optimizer isn't performing this transformation for you already (assuming that in fact it is not; you didn't provide evidence one way or the other).

    gg

Answers

  • alvinder
    alvinder Member Posts: 435 Silver Badge

    Try this.

     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 IN ( 'X','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;
    
  • gg
    gg Member Posts: 150 Bronze Badge

    Thanks for your response @alvinder

    But both the below condition does not seem to be satisfied.

    ph.po_no = p_order_no
    ph.wave_id = p_order_no
    

    Can you please modify the query so that both the condition are satisfied.

    Thanks

  • alvinder
    alvinder Member Posts: 435 Silver Badge
    edited Apr 13, 2022 6:12AM
    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 ( (  p_order_no in (ph.po_no,ph.wave_id) -- learned something new today.
                AND p_order_prefix IN ( 'X','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;
    


    only issue is if there is aph.po_no with p_order_prefix = 'W'
    or ph.wave_id with p_order_prefix = 'X'
    if This can't happen then you can use the above query.
    
    Otherwise use a union and have 2 queries.
    
    
    
    
    


    gg
  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond
    Answer ✓

    Even after @alvinder fixes that problem, he has not eliminated the OR-based condition. "something IN (..., ..., ...)" is expanded to an OR-based condition, it's just another way to write the same thing.

    I won't do the work for you - your problem, your work. I will just tell you what you need to change. An OR-based condition like that can be rewritten as a UNION ALL (a low-cost set operation on rowsets) of the result of two disjoint queries. Something like this:

    select ...
    from ...
    where ...
    ... and ph.po_no = p_order_no
        and p_order_prefix = 'X'
        and pd.pick_method = 'P'
        and pd.status = 'E' 
    ........
    UNION ALL
    select ...
    from ...
    where ...
    ... and ph.wave_id = p_order_no
        and p_order_prefix = 'W'
        and pd.pick_method = 'P'
        and pd.status = 'E' 
    ........
    ORDER BY ...     -- this applies to the RESULT of UNION ALL 
    

    Since p_order_prefix is 'X' in one branch of UNION ALL and 'W' in the other, you don't need to worry about double-counting (the same row appearing in both branches), which is the only thing you must be careful with in a situation like this.

    I am a bit surprised that the optimizer isn't performing this transformation for you already (assuming that in fact it is not; you didn't provide evidence one way or the other).

    gg
  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    @alvinder

    The second proposed solution suffers from the same problem: an IN condition is expanded as an OR-based condition. You changed the syntax but didn't solve the OP's problem.

    Moreover, your new solution allows pairings that are not permitted in the OP's original question. The way you wrote the condition, the combination p_order_no = ph.po_no, p_order_prefix = 'W' will satisfy the where clause; it does not satisfy the OP's original where clause.

    The correct transformation to an IN condition (which still doesn't solve the problem of avoiding OR) looks like this:

    ...
    and (p_order_no, p_order_prefix) in ( (ph.po_no, 'X'), (ph.wave_id, 'W') )
    ...
    


    alvindergg
  • alvinder
    alvinder Member Posts: 435 Silver Badge
    edited Apr 13, 2022 6:24AM

    Thanks @mathguy for correcting it. Learned something new today.

    gg
  • gg
    gg Member Posts: 150 Bronze Badge

    Thanks a lot @mathguy

    I will do as per your proposed solution and verify.

  • gg
    gg Member Posts: 150 Bronze Badge

    Hi @mathguy

    I tried out your solution.

    explain plan for 
                SELECT * FROM (
           SELECT DISTINCT wpd.dc_code,
                           wpd.storer,
                           wpd.picklist_key,
                           wpd.pickdetail_key,
                           wpd.line_no,
                           wpd.pick_type,
                           wpd.case_id,
                           wpd.order_type,
                           wpd.sub_type,
                           wpd.order_no,
                           wpd.item,
                           wpd.consignee,
                           wpd.bin_code,
                           wpd.lot,
                           wpd.pallet_id,
                           wpd.packkey,
                           wpd.to_pick_qty,
                           wam.bin_code dispatch_bin_code,
                           wph.wave_id,
                           wph.po_no,
                           wpd.pick_area_type,
                           wpd.assignment_id,
                           wpd.pick_method,
                           wpd.pack_method,
                           wpd.ord_priority,
                           wpd.pick_seq_no,
                           wpd.order_type_category
             FROM wms_pick_head wph,
                  wms_pick_detail wpd,
                  wms_order_types wot,
                  wms_area_master wam
            WHERE    wpd.dc_code = wph.dc_code
                  AND wpd.storer = wph.storer
                  AND wpd.picklist_key = wph.picklist_key
                  AND wph.dc_code = :p_dc_code
                  AND wph.storer = :p_storer
                  AND wpd.item = :p_item
                  AND wpd.consignee = :p_consignee
                  AND wpd.dc_code = wot.dc_code
                  AND wpd.order_type = wot.order_type
                  AND wpd.sub_type = wot.sub_type
                  AND wam.dc_code(+) = wot.dc_code
                  AND wam.dc_area(+) = wot.dispatch_area
                  AND wpd.to_pick_qty > 0              
                  AND wph.po_no = :p_order_no
                  AND :p_order_prefix = 'X'
                  AND wpd.pick_method = 'P'
                  AND wpd.status = 'E' 
    UNION ALL
           SELECT DISTINCT wpd.dc_code,
                           wpd.storer,
                           wpd.picklist_key,
                           wpd.pickdetail_key,
                           wpd.line_no,
                           wpd.pick_type,
                           wpd.case_id,
                           wpd.order_type,
                           wpd.sub_type,
                           wpd.order_no,
                           wpd.item,
                           wpd.consignee,
                           wpd.bin_code,
                           wpd.lot,
                           wpd.pallet_id,
                           wpd.packkey,
                           wpd.to_pick_qty,
                           wam.bin_code dispatch_bin_code,
                           wph.wave_id,
                           wph.po_no,
                           wpd.pick_area_type,
                           wpd.assignment_id,
                           wpd.pick_method,
                           wpd.pack_method,
                           wpd.ord_priority,
                           wpd.pick_seq_no,
                           wpd.order_type_category
             FROM wms_pick_head wph,
                  wms_pick_detail wpd,
                  wms_order_types wot,
                  wms_area_master wam
            WHERE    wpd.dc_code = wph.dc_code
                  AND wpd.storer = wph.storer
                  AND wpd.picklist_key = wph.picklist_key
                  AND wph.dc_code = :p_dc_code
                  AND wph.storer = :p_storer
                  AND wpd.item = :p_item
                  AND wpd.consignee = :p_consignee
                  AND wpd.dc_code = wot.dc_code
                  AND wpd.order_type = wot.order_type
                  AND wpd.sub_type = wot.sub_type
                  AND wam.dc_code(+) = wot.dc_code
                  AND wam.dc_area(+) = wot.dispatch_area
                  AND wpd.to_pick_qty > 0              
                  AND  wph.wave_id = :p_order_no
                  AND :p_order_prefix = 'W'
                  AND wpd.pick_method = 'P'
                  AND wpd.status = 'E')
          ORDER BY dc_code,
                  item,
                  consignee,
                  pick_seq_no,
                  pickdetail_key,
                  line_no;
    
    

    select * from table(dbms_xplan.display);  


    I got the explain plan as below:

    | Id | Operation                                  | Name               | Rows | Bytes | Cost (%CPU)| Time    |
    -------------------------------------------------------------------------------------------------------------------
    |  0 | SELECT STATEMENT                           |                    |    2 |  544 |   15 (20)| 00:00:01 |
    |  1 | SORT ORDER BY                             |                    |    2 |  544 |   15 (20)| 00:00:01 |
    |  2 |  VIEW                                     |                    |    2 |  544 |   14 (15)| 00:00:01 |
    |  3 |   UNION-ALL                               |                    |      |      |           |         |
    |  4 |    HASH UNIQUE                            |                    |    1 |  185 |    7 (15)| 00:00:01 |
    |* 5 |     FILTER                                |                    |      |      |           |         |
    |  6 |      NESTED LOOPS OUTER                   |                    |    1 |  185 |    6  (0)| 00:00:01 |
    |  7 |       NESTED LOOPS                        |                    |    1 |  171 |    5  (0)| 00:00:01 |
    |  8 |        NESTED LOOPS                       |                    |    1 |  157 |    4  (0)| 00:00:01 |
    |* 9 |         TABLE ACCESS BY INDEX ROWID BATCHED| WMS_PICK_HEAD      |    1 |   26 |    2  (0)| 00:00:01 |
    |* 10 |          INDEX RANGE SCAN                 | WMS_PICK_HEAD_IDX3 |    1 |      |    1  (0)| 00:00:01 |
    |* 11 |         TABLE ACCESS BY INDEX ROWID BATCHED| WMS_PICK_DETAIL    |    1 |  131 |    2  (0)| 00:00:01 |
    |* 12 |          INDEX RANGE SCAN                 | WMS_PICK_DETAIL_IDX |    1 |      |    1  (0)| 00:00:01 |
    | 13 |        TABLE ACCESS BY INDEX ROWID        | WMS_ORDER_TYPES    |    1 |   14 |    1  (0)| 00:00:01 |
    |* 14 |         INDEX UNIQUE SCAN                 | WMS_ORDER_TYPES_PK |    1 |      |    0  (0)| 00:00:01 |
    | 15 |       TABLE ACCESS BY INDEX ROWID         | WMS_AREA_MASTER    |    1 |   14 |    1  (0)| 00:00:01 |
    |* 16 |        INDEX UNIQUE SCAN                  | WMS_AREA_MASTER_PK |    1 |      |    0  (0)| 00:00:01 |
    | 17 |    HASH UNIQUE                            |                    |    1 |  185 |    7 (15)| 00:00:01 |
    |* 18 |     FILTER                                |                    |      |      |           |         |
    | 19 |      NESTED LOOPS OUTER                   |                    |    1 |  185 |    6  (0)| 00:00:01 |
    | 20 |       NESTED LOOPS                        |                    |    1 |  171 |    5  (0)| 00:00:01 |
    | 21 |        NESTED LOOPS                       |                    |    1 |  157 |    4  (0)| 00:00:01 |
    |* 22 |         TABLE ACCESS BY INDEX ROWID BATCHED| WMS_PICK_HEAD      |    1 |   26 |    2  (0)| 00:00:01 |
    |* 23 |          INDEX RANGE SCAN                 | WMS_PICK_HEAD_IDX  |    1 |      |    1  (0)| 00:00:01 |
    |* 24 |         TABLE ACCESS BY INDEX ROWID BATCHED| WMS_PICK_DETAIL    |    1 |  131 |    2  (0)| 00:00:01 |
    |* 25 |          INDEX RANGE SCAN                 | WMS_PICK_DETAIL_IDX |    1 |      |    1  (0)| 00:00:01 |
    | 26 |        TABLE ACCESS BY INDEX ROWID        | WMS_ORDER_TYPES    |    1 |   14 |    1  (0)| 00:00:01 |
    |* 27 |         INDEX UNIQUE SCAN                 | WMS_ORDER_TYPES_PK |    1 |      |    0  (0)| 00:00:01 |
    | 28 |       TABLE ACCESS BY INDEX ROWID         | WMS_AREA_MASTER    |    1 |   14 |    1  (0)| 00:00:01 |
    |* 29 |        INDEX UNIQUE SCAN                  | WMS_AREA_MASTER_PK |    1 |      |    0  (0)| 00:00:01 |
        
    
    
    
    
    Then i also tried this method:
    
            explain plan for
            SELECT DISTINCT wpd.dc_code,
                            wpd.storer,
                            wpd.picklist_key,
                            wpd.pickdetail_key,
                            wpd.line_no,
                            wpd.pick_type,
                            wpd.case_id,
                            wpd.order_type,
                            wpd.sub_type,
                            wpd.order_no,
                            wpd.item,
                            wpd.consignee,
                            wpd.bin_code,
                            wpd.lot,
                            wpd.pallet_id,
                            wpd.packkey,
                            wpd.to_pick_qty,
                            wam.bin_code dispatch_bin_code,
                            wph.wave_id,
                            wph.po_no,
                            wpd.pick_area_type,
                            wpd.assignment_id,
                            wpd.pick_method,
                            wpd.pack_method,
                            wpd.ord_priority,
                            wpd.pick_seq_no,
                            wpd.order_type_category
              FROM wms_pick_head wph,
                   wms_pick_detail wpd,
                   wms_order_types wot,
                   wms_area_master wam
             WHERE     wpd.dc_code = wph.dc_code
                   AND wpd.storer = wph.storer
                   AND wpd.picklist_key = wph.picklist_key
                   AND wph.dc_code = :p_dc_code
                   AND wph.storer = :p_storer
                   AND wpd.pick_method = 'P'
                   AND wpd.status = 'E'
                   AND case :p_order_prefix when 'X' then wph.po_no
                                           when 'W' then wph.wave_id
                       end =  :p_order_no                   
                   AND wpd.item = :p_item
                   AND wpd.consignee = :p_consignee
                   AND wpd.dc_code = wot.dc_code
                   AND wpd.order_type = wot.order_type
                   AND wpd.sub_type = wot.sub_type
                   AND wam.dc_code(+) = wot.dc_code
                   AND wam.dc_area(+) = wot.dispatch_area
                   AND wpd.to_pick_qty > 0
           ORDER BY wpd.dc_code,
                   wpd.item,
                   wpd.consignee,
                   wpd.pick_seq_no,
                   wpd.pickdetail_key,
                   wpd.line_no;
                   
                   
    select * from table(dbms_xplan.display);                
    
    I got the explain plan as below:
    
    
    | Id  | Operation                                 | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                          |                      |     1 |   185 |     8  (25)| 00:00:01 |
    |   1 |  SORT ORDER BY                            |                      |     1 |   185 |     8  (25)| 00:00:01 |
    |   2 |   HASH UNIQUE                             |                      |     1 |   185 |     7  (15)| 00:00:01 |
    |   3 |    NESTED LOOPS                           |                      |     1 |   185 |     6   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS                          |                      |     1 |   185 |     6   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS OUTER                   |                      |     1 |   159 |     5   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS                        |                      |     1 |   145 |     4   (0)| 00:00:01 |
    |*  7 |        TABLE ACCESS BY INDEX ROWID BATCHED| WMS_PICK_DETAIL      |     1 |   131 |     3   (0)| 00:00:01 |
    |*  8 |         INDEX RANGE SCAN                  | WMS_PICK_DETAIL_IDX1 |     1 |       |     2   (0)| 00:00:01 |
    |   9 |        TABLE ACCESS BY INDEX ROWID        | WMS_ORDER_TYPES      |     1 |    14 |     1   (0)| 00:00:01 |
    |* 10 |         INDEX UNIQUE SCAN                 | WMS_ORDER_TYPES_PK   |     1 |       |     0   (0)| 00:00:01 |
    |  11 |       TABLE ACCESS BY INDEX ROWID         | WMS_AREA_MASTER      |     1 |    14 |     1   (0)| 00:00:01 |
    |* 12 |        INDEX UNIQUE SCAN                  | WMS_AREA_MASTER_PK   |     1 |       |     0   (0)| 00:00:01 |
    |* 13 |      INDEX UNIQUE SCAN                    | WMS_PICK_HEAD_PK     |     1 |       |     0   (0)| 00:00:01 |
    |* 14 |     TABLE ACCESS BY INDEX ROWID           | WMS_PICK_HEAD        |     1 |    26 |     1   (0)| 00:00:01 |        
    

    Does the OR functionality eliminated by using the case statement above?

    Which method do you suggest i use for the query ?


    Thanks

  • Paulzip
    Paulzip Member Posts: 8,694 Blue Diamond
    edited Apr 13, 2022 9:58AM

    Unless I'm mistaken, I think this is equivalent :

    with 
      data as (
        SELECT 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
          JOIN table2 pd ON pd.dc_code = ph.dc_code
                        AND pd.storer = ph.storer
                        AND pd.picklist_key = ph.picklist_key
                        AND pd.pick_method = 'P'
                        AND pd.status = 'E'                       
          JOIN table3 ot ON ot.dc_code = pd.dc_code 
                        AND ot.order_type = pd.order_type 
                        AND ot.sub_type = pd.sub_type 
          LEFT JOIN table4 am ON am.dc_code = ot.dc_code
                             AND am.dc_area = ot.dispatch_area
         WHERE 1 = 1
           AND ph.dc_code = p_dc_code
           AND ph.storer = p_storer
           AND pd.item = p_item
           AND pd.consignee = p_consignee
           AND pd.to_pick_qty > 0
       )
    SELECT *
    FROM   data
    WHERE  po_no = p_order_no AND p_order_prefix = 'X'
    UNION
    SELECT *
    FROM   data
    WHERE  wave_id = p_order_no AND p_order_prefix = 'W'
    ORDER BY dc_code,
             item,
             consignee,
             pick_seq_no,
             pickdetail_key,
             line_no;
    

    Notes

    • You need to learn ANSI joins, you aren't living in the dark ages, everyone uses ANSI joins now, they are clearer and are more flexible than SQL 89 joins
    • Because the WITH subquery is referenced twice, it should materialize
    • The UNION is effectively doing the DISTINCT operation, so DISTINCT is removed from the WITH subquery


    gg