Hello All,
I have a query that I am running which is SELECT DISTINCT. It is a simple query with only one string field in the results. There are multiple WHERE conditions, and then a ORDER BY.
Once I get the result, I add a step for Rows: Remove Duplicates.
The table is connected to others through relationships. When I try to apply changes, I get an error stating "Column 'Location' in Table 'Location' contains a duplicate value' [Name Redacted]' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table".
I manually checked in the preview list, and there are indeed what appear to be duplicate values. If I manually exclude it, a different location then also shows up in the error message, so there are multiple violations.
I have no idea how the above steps can result in duplicates. I specifically picked DISTINCT and added the step to remove duplicates. Any help appreciated!
Query Text:
SELECT DISTINCT LTRIM(RTRIM(OfficeName)) AS [Location]
FROM ADR
WHERE
Market NOT LIKE '%Test%' AND Market NOT LIKE '%Inactive%'
AND Name NOT LIKE '%MS%'
AND PDN = 'P'
AND CONVERT(date, Date) BETWEEN
DATEFROMPARTS(YEAR(GETDATE())-2, 1, 1) AND
DATEADD(dd, -1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))
AND ID IS NOT NULL
AND WCID IS NULL
AND (Type = 'S' OR Type = 'C' OR Type = 'E')
AND Code NOT LIKE '%INACT%'
AND OfficeName IS NOT NULL
ORDER BY LTRIM(RTRIM(OfficeName))
