HANA query to show duplicate UDF entries on PO

[former_member268870]

Experts,

The following query works, but I need to add another WHERE statement to only show when there is duplicates in the T1."ItemCode, meaning when it is the same item code.

SELECT T0."U_XXX_L_02", T0."DocNum", T0."CardName", T1."LineNum", T1."ItemCode", T1."Dscription", T1."Quantity", T1."ShipDate", T1."LineStatus"

FROM OPOR T0  INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T1."LineStatus" = 'O'

Your help will be appreciated,

Marli

[Former Member · Accepted solution]

Hi Marli

Try adding a sub query using count of ItemCode > 1.

Kind regards

Peter Juby

[former_member268870 · Accepted solution]

Hi, here is the query that worked:

SELECT "Repetition Count", "Cust. PO#", CardName, docnum, itemcode, Dscription from

(

SELECT

  DISTINCT COUNT(T1."ItemCode") OVER(PARTITION BY T0."U_NBS_L_02", T1."ItemCode") AS "Repetition Count",

T0."U_NBS_L_02" as "Cust. PO#",

T0."DocNum" as docnum,

T0."CardName" as CardName,

T1."ItemCode" as itemcode,

T1."Dscription" as Dscription

FROM OPOR T0  INNER JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"

WHERE T1."LineStatus" = 'O' AND T0."U_NBS_L_02" = T0."U_NBS_L_02") a

where "Repetition Count" > 1