Retaining certain records from SQL result

[MCubitt]

I have a query:

select
 substr(ipis.contract,1,3) Company,
 ipis.contract,
 ip.type_designation,
 ipis.part_no,
 ipis.lot_batch_no,
 ipis.serial_no,
 ipis.qty_onhand,
 ipc.inventory_value Std_Cost,
 pol.buy_unit_price,
(select currency_rate
from currency_rate_tab cr where
(cr.COMPANY,cr.CURRENCY_CODE,cr.CURRENCY_TYPE, cr.VALID_FROM)
 in
 (select cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE,  max(cr1.VALID_FROM)
 from currency_rate_tab cr1 where
 cr1.COMPANY=substr(ipis.contract,1,3) and
 cr1.CURRENCY_CODE=pol.currency_code and
 cr1.CURRENCY_TYPE=pol.currency_type and
 cr1.VALID_FROM <= ith.dated
 group by cr1.COMPANY,cr1.CURRENCY_CODE,cr1.CURRENCY_TYPE)) currency_rate
from
 ifsapp.inventory_part_in_stock_tab ipis,
 ifsapp.inventory_part_tab ip,
 ifsapp.inventory_part_config_tab ipc,
 ifsapp.inventory_transaction_hist_tab ith,
 ifsapp.purchase_order_line_tab pol
where
 ipis.part_no = ip.part_no and
 ipis.contract = ip.contract and
 ipis.part_no = ipc.part_no and
 ipis.contract = ipc.contract 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
  pol.order_no (+) = ith.order_no and
  pol.line_no (+) = ith.release_no and
 pol.release_no (+) = ith.sequence_no and
  ipis.qty_onhand > 0  and
 ipis.contract like 'OKM%'
 and ipis.part_no = '1111718'
order by
 ipis.contract, ipis.part_no, ipis.lot_batch_no, ipis.serial_no, receipt_date

which lists:

Company	Contract	Type_Designation	Part_No	Lot_Batch_No	Serial_No	Qty_Onhand	Std_Cost	Buy_Unit_Price	Currency_Rate
OKM	OKMDM	5	1111718	*	W101262E	1	3021.775926		
OKM	OKMDM	5	1111718	*	W101401	1	3021.775926		
OKM	OKMDM	5	1111718	*	W201698	1	3021.775926		
OKM	OKMDM	5	1111718	*	W201878	1	3021.775926		
OKM	OKMDM	5	1111718	*	W402318	1	3021.775926	2671.073826	1.49
OKM	OKMDM	5	1111718	*	W402318	1	3021.775926	2671.073826	1.49
OKM	OKMDM	5	1111718	*	W402318	1	3021.775926	2671.073826	1.49
OKM	OKMDM	5	1111718	*	W900144/R	1	3021.775926		
OKM	OKMMN	5	1111718	*	W502661	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502661	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502661	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502662	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502662	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502662	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502672	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502672	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502672	1	3021.775926	2763.819444	1.45

But I only want the records which have no Buy_Unit_Price
OR the 1st of a set (where a set is for a group of Company, Site, Type_Designator, Part_no, Lot_Batch_no, Serial_No, Qty_OnHand, Std_Cost.

So I would like:

Company	Contract	Type_Designation	Part_No	Lot_Batch_No	Serial_No	Qty_Onhand	Std_Cost	Buy_Unit_Price	Currency_Rate
OKM	OKMDM	5	1111718	*	W101262E	1	3021.775926		
OKM	OKMDM	5	1111718	*	W101401	1	3021.775926		
OKM	OKMDM	5	1111718	*	W201698	1	3021.775926		
OKM	OKMDM	5	1111718	*	W201878	1	3021.775926		
OKM	OKMDM	5	1111718	*	W402318	1	3021.775926	2671.073826	1.49
OKM	OKMDM	5	1111718	*	W900144/R	1	3021.775926		
OKM	OKMMN	5	1111718	*	W502661	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502662	1	3021.775926	2763.819444	1.45
OKM	OKMMN	5	1111718	*	W502672	1	3021.775926	2763.819444	1.45

thanks



Applications Support
UK

 

[lewisp]

TO get all records where there is no buy_unit_price, add [tt]AND pol.buy_unit_price IS NULL[/tt] to your WHERE clause.

If you only want single rows where multiples are returned, use DISTINCT immediately after your SELECT.

Does this do what you want?