Duplicated Invoice Numbers In A Query

[former_member1103682]

Hi, I´m really new at creating querys and I hope you can help me

This is the one that I created:

SELECT T0.[LicTradNum] AS 'CIF',

T0.[CardName], T1.[Street] AS 'Dirección', T1.[ZipCode], T1.[State], T1.[Country], T0.[E_Mail], T0.[DflIBAN] AS IBAN', T2.[NumAtCard] AS 'Nº de factura', T2.[DocTotal] AS 'Importe', T2.[DocDate], T2[DocDueDate], T2[DocStatus], T0.[GroupNum], T3.[PayMethCod]

FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN OPCH T2 ON T0.[CardCode] = T2.[CardCode] INNER JOIN OPYM T3 ON T0.[PymCode] = T3.[PayMethCod]

WHERE T3.[PayMethCod]='TR P' and T2.[DocDueDate]>'20221003' and T2.[DocStatus]='O'

What happens is that when a supplier has multiple addresses (fiscal address, place of business), the query duplicates the open invoices correspoding to that supplier as many times as different addresses are. I´ve tried DISTINCT but it didn´t work

Hope you can help me, thanks in advance

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

Alex

[LoHa]

Hi Juan,

this is correct system behavior. Distinct wouldn't help because everytime you have another street you receive a new line. The question is, which line do you want?

If you want the default ShipTo try to join in that way

LEFT JOIN CRD1 T1 ON T1.CardCode = T0.CardCode AND T1.Address = T0.ShipToDef AND T1.AdresType = 'S'

If you need the BillTo try this

LEFT JOIN CRD1 T1 ON T1.CardCode = T0.CardCode AND T1.Address = T0.BillToDef AND T1.AdresType = 'B'

The left Join would be better in case there is not default adress

regards Lothar