question

jamie.baird_macphie avatar image
jamie.baird_macphie asked

Remove Duplicated from Query

Hi,

I have the following query,

select CUST_ORD_CUSTOMER_API.Get_Name(a.DELIVER_TO_CUSTOMER_NO) as Customer
,a.SHIPMENT_ID as Shipment
, a.parent_consol_shipment_id as Con_Shipment
,Customer_Order_Line_API.Get_Catalog_No(b.ORDER_NO,b.LINE_NO,b.REL_NO,b.LINE_ITEM_NO) as Part
,Customer_Order_Line_API.Get_Catalog_Desc(b.ORDER_NO,b.LINE_NO,b.REL_NO,b.LINE_ITEM_NO) as Description
,b.sales_qty
,Shipment_Order_Line_API.Get_Qty_To_Reserve(b.SHIPMENT_ID, b.ORDER_NO, b.LINE_NO, b.REL_NO, b.LINE_ITEM_NO) as Qty_to_reserve
,b.QTY_TO_PICK
,b.ORDER_LINE_STATE
,c.QTY_ONHAND
,c.LOT_BATCH_NO
,c.WAIV_DEV_REJ_NO
,c.EXPIRATION_DATE
,COMPANY_SITE_API.Get_Description(c.contract) as Site
,PART_AVAILABILITY_CONTROL_API.Get_Description(c.AVAILABILITY_CONTROL_ID) as PAC
from
SHIPMENT a
inner join SHIPMENT_ORDER_LINE_OVW b on a.SHIPMENT_ID=b.SHIPMENT_ID
left outer join INVENTORY_PART_IN_STOCK_UIV c on Customer_Order_Line_API.Get_Catalog_No(b.ORDER_NO,b.LINE_NO,b.REL_NO,b.LINE_ITEM_NO) = c.part_no and c.qty_onhand > 0 AND (C.CONTRACT = '20' OR C.CONTRACT = 'E16')
where a.parent_consol_shipment_id = '16260'
and b.sales_qty >0
order by Customer_Order_Line_API.Get_Catalog_No(b.ORDER_NO,b.LINE_NO,b.REL_NO,b.LINE_ITEM_NO)

The right table contains multiple results which results in duplicated lines on the left table results,

How do I remove these? I have seen the UNION option but not sure how to factor this in?

Kindest,

Jamie

sqlquery-results
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered

You could try a little brute force by changing the select to a select distinct.

A cleaner approach might be using outer apply rather then left outer join to get just a single row from the right for each row on the left

select
   ...
from 
   SHIPMENT a
   inner join SHIPMENT_ORDER_LINE_OVW b on a.SHIPMENT_ID = b.SHIPMENT_ID
   outer apply (
      select top 1 
         QTY_ONHAND
         ,LOT_BATCH_NO
         ,WAIV_DEV_REJ_NO
         ,contract
         ,EXPIRATION_DATEAVAILABILITY_CONTROL_ID 
      from 
         INVENTORY_PART_IN_STOCK_UIV 
      where 
         Customer_Order_Line_API.Get_Cataolog_No(b.ORDER_NO, b.LINE_NO, b.REL_NO, b.LINE_ITEM_NO) = part_no 
         and qty_onhand > 0 
         and (contract = '20' or contract = 'E16'
   ) as c
where 
   a.parent_consol_shipment_id = '16260'
   and b.sales_qty > 0
order by 
   Customer_Order_Line_API.Get_Catalog_No(b.ORDER_NO,b.LINE_NO,b.REL_NO,b.LINE_ITEM_NO)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.