PO净接收的事务

需求:现用户要求是查询净接收数量,类似接收事务处理查询界面中，输入PO号，显示该PO的所有接收和退货记录。

 

select rt.transaction_id,
       ph.segment1,
       rt.transaction_type,
       rt.quantity,
       rt.destination_type_code,
       rt.primary_quantity
  from po.rcv_transactions rt,
       po.po_headers_all ph
 where ph.segment1='1004811'
   and rt.destination_type_code = 'RECEIVING'
   and rt.po_header_id=ph.po_header_id
   and rt.parent_transaction_id=-1
   and not exists
 (select 'T'
          from po.rcv_transactions rt1        
         where rt.transaction_id = rt1.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)

---

查询效率超慢(原因是rt.parent_transaction_id=-1)

 

改进后的SQL如下

select rt.transaction_id,
       ph.segment1,
       rt.transaction_type,
       rt.quantity,
       rt.destination_type_code,
       rt.primary_quantity
  from po.rcv_transactions rt, 
       po.po_headers_all ph
 where ph.segment1='1004811'
   and rt.destination_type_code = 'RECEIVING'
   and rt.po_header_id = ph.po_header_id
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt.transaction_id = rt1.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)
      
   and not exists
 (select 'T'
          from po.rcv_transactions rt1
         where rt1.transaction_id = rt.parent_transaction_id
           and rt1.po_header_id = rt.po_header_id
           and rt1.destination_type_code = rt.destination_type_code)