0
Customer|Product_Code|List_Price|Unit_Price|Unit_Unit_Price|Unit_List_Price|
--------+------------+----------+----------+---------------+---------------+
3BEACHES|CA050608    |9.46      |9.46      |EACH           |EACH
3BEACHES|CA050608    |9.46      |9.46      |INNER          |EACH
3BEACHES|CA050608    |9.46      |9.46      |SHIPPER        |EACH

In my select statement I am joining 4 Views - Customer,Product,Price Book and List Price View and I want only 1 row for a combination of Customer and Product_Code, I am getting 3 rows as attached above because there are different values in Unit_Unit_Price field for some Products, it can be any one of "EACH" or "INNER" or "SHIPPER" but just 1.

I can't put a Where clause on any 1 Value(Each,Inner or Shipper) because for some Customer-Product_Code line it's just showing 1 row(Shipper or Each or Inner), so if I am using where clause on any 1 value of Unit_Unit_Price field then it's deducting the records which is not correct.

What I want: there are no repeating rows for Customer-Product_Code combination, I just need unique rows.

Code I am using:

SELECT TOP (100) PERCENT skh.ARCUS.IDCUST,
                         skh.ICITEM.ITEMNO AS product_code,
                         skh.ICITEM.CNTLACCT,
                         skh.v_list_price_each.price AS list_price,
                         skh.v_price_book.price,
                         skh.v_list_price_each.unit AS Expr2,
                         skh.v_price_book.price_unit
FROM skh.ARCUS
INNER JOIN skh.v_price_book ON skh.ARCUS.PRICLIST = skh.v_price_book.price_list
INNER JOIN skh.ICITEM ON skh.v_price_book.item_short = skh.ICITEM.ITEMNO
AND skh.v_price_book.company = skh.ICITEM.AUDTORG
INNER JOIN skh.v_list_price_each ON skh.v_price_book.item_short = skh.v_list_price_each.item_short
WHERE (skh.ICITEM.INACTIVE = 0)
  AND (skh.ARCUS.SWACTV = 1)
ORDER BY skh.ARCUS.IDCUST
  • 1
    Can you post the desired output ? – Sabin Bio Jun 28 '17 at 5:56
  • Give your current query as well – Utsav Jun 28 '17 at 6:15
3

It is difficult to say without your current query and sample data, but looks like this is something you want

select t1.*
from 
(
select t.*,
    row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk
from 
 (your current query) t
) t1
where t1.rnk=1

Note: If your current query doesn't have any group by then you can plug in row_number() over (partition by Customer,Product_Code order by Unit_Unit_Price) as rnk in your current query select clause and filter it in outer query as the example above.

  • it's not working, results says Field not bound. – user7129146 Jun 28 '17 at 10:19
1

If you need to limit the number of rows returned by one of your tables, you could use a CROSS APPLY solution.

Right table expression is processed once for each row from the left table expression.

CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb

Due you're using a TOP (X) expression, you could add an ORDER BY clause into the SELECT statement.

SELECT      skh.ARCUS.IDCUST,
            pb.ITEMNO AS product_code,
            pb.CNTLACCT,
            pb.price AS list_price,
            pb.price,
            pb.unit AS Expr2,
            pb.price_unit
FROM        skh.ARCUS
CROSS APPLY (SELECT TOP 1 *  --<< add required fields here don't use *
             FROM         skh.v_price_book 
             INNER JOIN   skh.ICITEM 
             ON           skh.ICITEM.ITEMNO = pb.item_short
             AND          skh.ICITEM.AUDTORG = pb.company
             INNER JOIN   skh.v_list_price_each 
             ON           skh.v_list_price_each.item_short = pb.item_short
             WHERE        skh.v_price_book.price_list = skh.ARCUS.PRICLIST
             AND          skh.ICITEM.INACTIVE = 0) pb
WHERE        skh.ARCUS.SWACTV = 1
--ORDER BY    skh.ARCUS.IDCUST

As it seems you're using this query within a VIEW (TOP (100) PERCENT), I'd suggest you to remove TOP and move ORDER BY to your SELECT view_name statement.

  • It's not working, still getting the same number of rows. – user7129146 Jun 28 '17 at 10:17
  • Is there a relation 1:1 between ARCUS, v_price_book and ICITEM? – McNets Jun 28 '17 at 10:18
  • Yes, there is a field called Pricelist which connects ARCUS to PRICEBOOK, furhter ICITEM and PRICEBOOK are connected by item_code, PRICEBOOK and LISTPRICE are also connected by item_code but ICITEM and ARCUS are not directly connected. – user7129146 Jun 28 '17 at 11:03
  • which table produce the duplicated values ("EACH" or "INNER" or "SHIPPER")? – McNets Jun 28 '17 at 11:05
  • pricebook table – user7129146 Jun 28 '17 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.