Only include emails once in SQL query

Question

Having a bit of a dead brain moment right now.

I have this query to give me BP Codes,Names, Contacts and Sales Person. I only want to include an existing email once in the query. If the same email exists for multiple BP's then only 1 BP should be included (lets say TOP 1).

SELECT DISTINCT 
OCRD.CardCode, 
OCRD.CardName,
OCPR.Name,
OCPR.E_MailL,
OSLP.SlpName
FROM OCRD 
INNER JOIN OCRG ON OCRG.GroupCode = OCRD.GroupCode 
INNER JOIN OCPR ON OCPR.CardCode = OCRD.CardCode
INNER JOIN OOND ON OOND.IndCode = OCRD.IndustryC 
INNER JOIN OSLP ON OCRD.SlpCode = OSLP.SlpCode 
WHERE OCRG.GroupName <> 'Retail'  AND 
OOND.IndName = 'Aged Care' and 
OCRD.frozenFor = 'N' AND
OCPR.E_MailL LIKE '%@%'
ORDER BY OCRD.CardCode ASC

For the life of me I cant figure it out right now. Any help will be appreciated.

score -1 · Answer 1 · answered May 26 '22 at 02:24

-1

Based on this stackoverflow post, I assume this might work

SELECT DISTINCT ON (OCPR.E_MailL)
OCRD.CardCode, 
OCRD.CardName,
OCPR.Name,
OCPR.E_MailL,
OSLP.SlpName
FROM OCRD 
INNER JOIN OCRG ON OCRG.GroupCode = OCRD.GroupCode 
INNER JOIN OCPR ON OCPR.CardCode = OCRD.CardCode
INNER JOIN OOND ON OOND.IndCode = OCRD.IndustryC 
INNER JOIN OSLP ON OCRD.SlpCode = OSLP.SlpCode 
WHERE OCRG.GroupName <> 'Retail'  AND 
OOND.IndName = 'Aged Care' and 
OCRD.frozenFor = 'N' AND
OCPR.E_MailL LIKE '%@%'
ORDER BY OCRD.CardCode ASC, OCRD.E_MAIL

answered May 26 '22 at 02:24

WithThee

79
4

Thanks for the reply @WithThee, it doesnt appear that DISTINCT ON works in SAP B1 for SQL. – Nick Jones May 26 '22 at 04:09
This does not work on HANA Sql script, shows error 'sql syntax error: incorrect syntax near "ON"'. – Vyron Paschalidis Jul 08 '22 at 15:15

Only include emails once in SQL query

1 Answers1