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)