Adding Customer Group To Query

[Former Member]

SELECT [Customer Code] = Case when T1.[AdresType] = 'B' then T0.[CardCode] else ' ' end, [Customer Name] = Case when T1.[AdresType] = 'B' then T0.[CardName] else ' ' end, [Address Type] = Case when T1.[AdresType] = 'B' then 'Bill To' when T1.[AdresType] = 'S' then 'Ship To' end, [Address ID] = T1.[Address], [Name Line 1] = T1.[Block], [Name Line 2] = T1.[County], [Name Line 3] = T1.[Building], [City] = T1.[City], [County] = T2.[Name], [Post Code] = T1.[ZipCode], [Country] = T3.[Name], [Telephone] =Case when T1.[AdresType] = 'B' then T0.[Phone1] else ' ' end, [Fax] =Case when T1.[AdresType] = 'B' then T0.[Fax] else ' ' end, [E-Mail] =Case when T1.[AdresType] = 'B' then T0.[E_Mail] else ' ' end , [Sales Area] = Case when T1.[AdresType] = 'B' then T4.[SlpName] else ' ' end FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] left outer jOIN OCST T2 ON T1.[State] = T2.[Code] INNER JOIN OCRY T3 ON T0.[Country] = T3.[Code] INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode] WHERE T0.[CardType] = 'C' AND T4.[SlpName] >= CASE '[%0]' When '' Then 'aaaaaa' Else '[%0]' End and T4.[SlpName] <= CASE '[%1]' When '' Then 'ZZZZZZ' Else '[%1]' End

How could i add customer group name to the above query?

Many Thanks,

James

[Johan_H]

Hi James,

You need to join the OCRG table (INNER JOIN OCRG T1 ON T0.GroupCode = T1.GroupCode)

The field you need is OCRG.GroupName

Regards,

Johan

[Former Member]

Hi Johan,

I am struggling to join sucesfully, could you kindly provide some further guidance?

[Johan_H]

SELECT [Customer Code] = Case when T1.[AdresType] = 'B' then T0.[CardCode] else ' ' end, [Customer Name] = Case when T1.[AdresType] = 'B' then T0.[CardName] else ' ' end, [Address Type] = Case when T1.[AdresType] = 'B' then 'Bill To' when T1.[AdresType] = 'S' then 'Ship To' end, [Address ID] = T1.[Address], [Name Line 1] = T1.[Block], [Name Line 2] = T1.[County], [Name Line 3] = T1.[Building], [City] = T1.[City], [County] = T2.[Name], [Post Code] = T1.[ZipCode], [Country] = T3.[Name], [Telephone] =Case when T1.[AdresType] = 'B' then T0.[Phone1] else ' ' end, [Fax] =Case when T1.[AdresType] = 'B' then T0.[Fax] else ' ' end, [E-Mail] =Case when T1.[AdresType] = 'B' then T0.[E_Mail] else ' ' end , [Sales Area] = Case when T1.[AdresType] = 'B' then T4.[SlpName] else ' ' end FROM OCRD T0 INNER JOIN CRD1 T1 ON T0.[CardCode] = T1.[CardCode] left outer jOIN OCST T2 ON T1.[State] = T2.[Code] INNER JOIN OCRY T3 ON T0.[Country] = T3.[Code] INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode] INNER JOIN OCRG T5 ON T0.GroupCode = T5.GroupCode WHERE T0.[CardType] = 'C' AND T4.[SlpName] >= CASE '[%0]' When '' Then 'aaaaaa' Else '[%0]' End and T4.[SlpName] <= CASE '[%1]' When '' Then 'ZZZZZZ' Else '[%1]' End

[Former Member]

Perfect - thank you Johan!

[Johan_H]

You're welcome, please close this thread by awarding best answer.

Regards,

Johan