Using Query to generate Mailing and Email Address List

[ben_hunger]

Hello,

I have been going in circles trying to figure out how to successfully write a query that essentially provides a mailing and email address list for each contact within a BP. I have found some other discussions with similar questions, but they have not resolved my questions.

Thank you in advance.

Here is where I currently stand:

T0.[CardType],

T0.[GroupCode],

T1.[GroupName],

T2.[FirstName],

T2.[LastName],

T2.[E_MailL],

T3.[Address],

T3.[Street],

T3.[StreetNo],

T3.[Block],

T3.[Building],

T3.[City],

T3.[State],

T3.[ZipCode],

T3.[Country],

T3.[U_XXDUPSRC]

FROM OCRD T0

INNER JOIN OCPR T2 ON T0.CardCode = T2.CardCode

INNER JOIN CRD1 T3 ON T3.Address = T2.Address

[dbo].[OCRG]  T1, [dbo].[OCRD]  T0

OCPR.[Address] could be either a Bill-To or Ship-To address since CRD1.[Address] is sometimes the same. So a where statement should probably be used such as WHERE T1.AdresType = 'S'. However, if for some reason, there is no associated address (OCPR.[Address] is blank) the contact should appear anyway

One additional piece of information I would like to include is any associated OCQG.[GroupName] and if there are multiples, they should each have their own column if possible.

[Former Member · Accepted solution]

Hi Bernd,

Try this

Select T0.[CardType],

T0.[GroupCode],

T1.[GroupName],

T2.[FirstName],

T2.[LastName],

T2.[E_MailL],

T3.[Address],

T3.[Street],

T3.[StreetNo],

T3.[Block],

T3.[Building],

T3.[City],

T3.[State],

T3.[ZipCode],

T3.[Country],

T3.[U_XXDUPSRC]

FROM OCRD T0

INNER JOIN OCPR T2 ON T0.CardCode = T2.CardCode

LEFT JOIN CRD1 T3 ON T3.Address = T2.Address

INNER JOIN OCRG T1 ON T1.GroupCode=T0.GroupCode

for OCQG, you need to use a case statement for that

Hope this helps

Regards

JP

[ben_hunger · Accepted solution]

Hi JP,

Thank you for your response. It works partially. I believe I have a solution, but not quite yet. I'll post more information when I have it.

[kothandaraman_nagarajan]

Hi,

Did you get required query?

Thanks & Regards,

Nagarajan