Skip to Content
0

Adding Customer Group To Query

Nov 01, 2017 at 08:01 AM

28

avatar image
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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Johan Hakkesteegt Nov 01, 2017 at 09:25 AM
0

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

Show 4 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Johan,

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

0

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

0
Former Member

Perfect - thank you Johan!

0

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

Regards,

Johan

0