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!
SELECT DISTINCT LTRIM(RTRIM(OfficeName)) AS [Location]
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