[Solved] SQL – Distinct with Case When

by 


select distinct
OENT.OTHER_EXTERNAL_ID AS AGENT_NUMBER,
CASE 
    WHEN SE.Organization_Name IS NULL OR RTRIM(LTRIM(SE.ORGANIZATION_NAME)) = '' THEN '' 
    ELSE upper( SE.ORGANIZATION_NAME )
END AS AGENT
FROM SomeTableName
order by AGENT_NUMBER

It will still return duplicates in case if Agent number differs. If you want to find duplicated names then

select AGENT, count (AGENT_NUMBER) from (
    select OENT.OTHER_EXTERNAL_ID AS AGENT_NUMBER,
    CASE 
        WHEN SE.Organization_Name IS NULL OR RTRIM(LTRIM(SE.ORGANIZATION_NAME)) = '' THEN '' 
        ELSE upper( SE.ORGANIZATION_NAME )
    END AS AGENT
    FROM SomeTableName
    )
    group by AGENT
    HAVING count (AGENT_NUMBER) >1
    order by AGENT

To find duplicate names for same Agent number:

select AGENT_NUMBER, count (AGENT) from (
    select OENT.OTHER_EXTERNAL_ID AS AGENT_NUMBER,
    CASE 
        WHEN SE.Organization_Name IS NULL OR RTRIM(LTRIM(SE.ORGANIZATION_NAME)) = '' THEN '' 
        ELSE upper( SE.ORGANIZATION_NAME )
    END AS AGENT
    FROM SomeTableName
    )
    group by AGENT_NUMBER
    HAVING count (AGENT) >1
    order by AGENT_NUMBER

4

solved SQL – Distinct with Case When