DISTINCT OCRD from OINV

[BAH_OH]

Hello all,

I'm trying to get DISTINCT contact info based on invoices in a specific time frame. Specifically, this is related to the onboarding of the Avalara Tax add-on. I have written a query to get the information I need and it works with the exception that the contact information is not DISTINCT and I cannot figure out how to get it to be. Any help here is appreciated, please! B1 v10 HANA

Here is the query:

SELECT T0."CardCode", T0."CardName", T0."CardType", T0."GroupCode", T0."CmpPrivate", T0."Address", T0."Building", T0."StreetNo", T0."ZipCode", T0."City", T0."County", T0."State1", T0."Country", T0."CntctPrsn", T0."E_Mail", T0."Phone1", T0."LicTradNum", T2."Tel1", T2."U_Ext", T2."E_MailL"

FROM OCRD T0 INNER JOIN OINV T1 ON T0."CardCode" = T1."CardCode" INNER JOIN OCPR T2 ON T1."CntctCode" = T2."CntctCode"

WHERE T1."DocDate" >= '20240101'

[Johan_Hakkesteegt · Accepted solution]

Hi,

Please give this a try:

SELECT distinct T0."CardCode", T0."CardName", T0."CardType", T0."GroupCode"
, T0."CmpPrivate", T0."Address"
, cast(T0."Building" as nvarchar) as "Building"
, T0."StreetNo", T0."ZipCode"
, T0."City", T0."County", T0."State1", T0."Country", T0."CntctPrsn", T0."E_Mail"
, T0."Phone1", T0."LicTradNum", T2."Tel1", T2."U_Ext"
, T2."E_MailL"

FROM OCRD T0 INNER JOIN OINV T1 ON T0."CardCode" = T1."CardCode" INNER JOIN OCPR T2 ON T1."CntctCode" = T2."CntctCode"

WHERE T1."DocDate" >= '20240101'

Regards,

Johan

[BAH_OH · Accepted solution]

Hello Johan, I appreciate your response. This is a step in the right direction as now there is no error displayed by B1; however, it's not quite there yet for what I need. The results are showing distinct per T0.CntctPrsn within T0.CardCode which makes sense based on the query. I need to refine my needs a little more or do the last step manually. .

[mgregur · Accepted solution]

Hi,

just alter the join to OCPR to be "OCPR T2 ON T1."CntctCode" = T2."CntctCode" AND T1."CardCode" = T2."CardCode".

BR,

Matija