Joining three queries in SQL

[MCubitt]

I have 4 queries and need to amalgamate them to create one set of results.

The first:

select
 ipis.part_no,  ipis.contract, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand 
from
 ifsapp.inventory_part_in_stock_tab ipis
where
 ipis.qty_onhand > 0

This is the primary query which should use the other two to obtain an additional column of cost.

I need to add

ipis.qty_onhand, cost

To get the cost I need to go to the transaction history table:

 select
 ith.order_no, ith.release_no, ith.sequence_no, ith.line_item_no
from
 ifsapp.inventory_transaction_hist_tab ith
where
 ith.transaction_code in ('NREC','ARRIVAL') and
 ith.order_type='PUR ORDER' and
 ith.part_no = ipis.part_no and
 ith.contract = ipis.contract and
 ith.lot_batch_no = ipis.lot_batch_no and
 ith.serial_no = ipis.serial_no

and this gives me the purchase order line, from where the cost is held:

select
 pol.buy_unit_price
from
 ifsapp.purchase_order_line_tab pol
where
 pol.order_no=ith.order_no and
 pol.line_no=ith.line_line_no

But if there is no transaction history record that matches the criteria OR no purchase order line, then it needs to use the standard cost:

select
 ip.standard_cost
from
 ifsapp.inventory_part ip
where
 ip.part_no = ipis.part_no and
 ip.contract = ipis.contract

I am really struggling - any offers please?





Applications Support
UK

 

[MCubitt]

I think I managed it after all!

select
 ipis.contract,  ipis.part_no, ipis.lot_batch_no, ipis.serial_no, ipis.qty_onhand, 
 nvl(
 (
 select
 (
 select
  pol.buy_unit_price
 from
  ifsapp.purchase_order_line_tab pol
 where
 rownum = 1 and
  pol.order_no=ith.order_no and
 pol.line_no=ith.line_item_no
 )
from
 ifsapp.inventory_transaction_hist_tab ith
where
 rownum='1' and 
 ith.order_type='PUR ORDER' and
 ith.part_no = ipis.part_no and
 ith.contract = ipis.contract and
 ith.lot_batch_no = ipis.lot_batch_no and
 ith.serial_no = ipis.serial_no
 )
,
 (
 select
  ipc.inventory_value
 from
  ifsapp.inventory_part_config_tab ipc
 where
  ipc.part_no = ipis.part_no and 
  ipc.contract=ipis.contract
 )
 )  Real_Cost
from
 ifsapp.inventory_part_in_stock_tab ipis
where
 ipis.qty_onhand > 0 
order by
 ipis.contract, ipis.part_no

Applications Support
UK