Attach Vendor Address to Query

[Former Member]

I am using the following query to pull out invoices that were ran for payment.  How can I attach the vendor address from Business Partners to this query?

SELECT InvKey, DocNum, CardCode, QUOTENAME(CardName,'"'), PostDate, Convert(Decimal(10,2),InvPayAmnt),

CONVERT(VARCHAR(10), T5.PmntDate,101), NumAtCard, PymMeth,

sum(InvPayAmnt) over (partition by CardName) as payment_sum,

'Objtype' = CASE WHEN Objtype = 19 THEN 'CREDIT' ELSE 'INVOICE' END

FROM PWZ3

INNER JOIN OPWZ T5 ON T5.IdNumber = IdEntry

WHERE T5.PmntDate = '5/16/2011'

AND T5.Canceled = 'N'

AND Checked = 'Y'

AND PymMeth = 'PM2'

[Former Member]

Hi,

Try:

SELECT T0.InvKey, T0.DocNum, T0.CardCode, QUOTENAME(T0.CardName,'"'), T0.PostDate, Convert(Decimal(10,2),T0.InvPayAmnt),

CONVERT(VARCHAR(10), T5.PmntDate,101), T0.NumAtCard, T0.PymMeth,

sum(T0.InvPayAmnt) over (partition by T0.CardName) as payment_sum,

'Objtype' = CASE WHEN T0.Objtype = 19 THEN 'CREDIT' ELSE 'INVOICE' END, T1.Address, T1.City, T1.ZipCode, T1.State1, T1.Country

Thanks, Gordon

FROM PWZ3 T0

INNER JOIN OPWZ T5 ON T5.IdNumber = T0.IdEntry AND T5.PmntDate = '5/16/2011' AND T5.Canceled = 'N'

INNER JOIN OCRD T1 ON T1.CardCode = T0.CardCode

WHERE T0.Checked = 'Y'

AND T0.PymMeth = 'PM2'

[former_member186095]

Hi,

you can add the CRD1 table in the above query. In the table, there is field address.

Rgds,

JimM