asked on

Distinct a Coalesce result?

I'm banging my head trying to get this query to work. I can't seem to get DISTINCT to work in a sub-select (my joins, I'm sure) so I decided to use COALESCE. The query runs, but I get the error:

The multi-part identifier "UPR30300_1.Special" could not be bound.

Uggggh...

How to fix?

SELECT Employees_1.[Social Security Number], Employees_1.[Last Name], Employees_1.[First Name], Employees_1.[Birth Date], Employees_1.[Start Date],
HRORI012.REHIREDATE_I, TE024230.TERMINATIONDATE_I, UPR30300_1.[YTD Hours], UPR30300_1.[YTD Plan Compensation],
UPR30300_1.[YTD Gross Compensation], UPR30300_1.[YTD 415 Excluded Compensation], UPR30300_1.[YTD 415 Compensation],
UPR30300_1.[YTD Plan Excluded Compensation], UPR30300_1.[PreEntry Compensation], RTRIM(Employees_1.[Address 1]) + ', ' + RTRIM(Employees_1.City)
+ ', ' + RTRIM(Employees_1.State) + ', ' + RTRIM(Employees_1.[Zip Code]) AS Location, UPR30300_1.[YTD Deferral Contribution],
UPR30300_1.[YTD Match Contribution]
FROM Employees AS Employees_1 INNER JOIN
(SELECT EMPLOYID, COALESCE (CASE WHEN payrolcd = 'OPEDUE' THEN 'OPEDUE' ELSE NULL END,
CASE WHEN payrolcd = 'CWADUE' THEN 'CWADUE' ELSE NULL END) AS Special, SUM(CASE WHEN payrolcd IN (@YTDHours)
THEN untstopy ELSE 0 END) AS [YTD Hours], SUM(CASE WHEN payrolcd IN (@YTDPlanCompensation) THEN uprtrxam ELSE 0 END)
AS [YTD Plan Compensation], SUM(CASE WHEN payrolcd IN (@YTDGrossCompensation) THEN uprtrxam ELSE 0 END) AS [YTD Gross Compensation],
SUM(CASE WHEN payrolcd IN (@YTD415ExcludedCompensation) THEN uprtrxam ELSE 0 END) AS [YTD 415 Excluded Compensation],
SUM(CASE WHEN payrolcd IN (@YTD415Compensation) THEN uprtrxam ELSE 0 END) AS [YTD 415 Compensation],
SUM(CASE WHEN payrolcd IN (@YTDPlanExcludedCompensation) THEN uprtrxam ELSE 0 END) AS [YTD Plan Excluded Compensation],
SUM(CASE WHEN payrolcd IN (@PreEntryCompensation) THEN uprtrxam ELSE 0 END) AS [PreEntry Compensation],
SUM(CASE WHEN payrolcd IN (@YTDDeferralContribution) AND pyrlrtyp = 2 THEN uprtrxam ELSE 0 END) AS [YTD Deferral Contribution],
SUM(CASE WHEN payrolcd IN (@YTDMatchContribution) AND pyrlrtyp = 3 THEN uprtrxam ELSE 0 END) AS [YTD Match Contribution]
FROM UPR30300 AS UPR30300_2
WHERE (YEAR1 = @Year)
GROUP BY EMPLOYID, UPR30300_1.Special) AS UPR30300_1 ON Employees_1.[Employee ID] = UPR30300_1.EMPLOYID LEFT OUTER JOIN
HRORI012 ON Employees_1.[Employee ID] = HRORI012.EMPID_I LEFT OUTER JOIN
TE024230 ON Employees_1.[Employee ID] = TE024230.EMPID_I

Jared_S

Try combining your cases instead of using coalesce on your two cases. It should be a step in the right direction.

COALESCE (CASE WHEN payrolcd = 'OPEDUE' THEN 'OPEDUE' ELSE NULL END,
CASE WHEN payrolcd = 'CWADUE' THEN 'CWADUE' ELSE NULL END) AS Special

becomes:

special = case when payrolcd = 'OPEDUE' then payrolcd
when payrolcd = 'CWADUE' then payrolcd
else null
end

CPA_MCSE

ASKER

Thanks for that, however, my main problem is that there are multiple occurrences of either value for some employees.

What I am looking to do is just have the value display (once) for the employee if the record does in fact have either value.

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CPA_MCSE

ASKER

You are a SQL DEITY! Again, you come to my rescue. Thank you! *bows deeply and humbly*

PortletPaul

:)
@Sharath: "Deity" is a promotion right?

@cpa_mcse

the rule of thumb is: the "group by" should mimic the selection list
(for all the non-aggregated stuff)

you attempted to achieve this by referencing the column alias, but you did it before the subquery UPR30300_1 alias existed which is what the error message identified:

	GROUP BY EMPLOYID
		, UPR30300_1.Special
	) AS UPR30300_1 ON Employees_1.[Employee ID] = UPR30300_1.EMPLOYID

/* the deity did: */
	GROUP BY EMPLOYID
		, COALESCE(CASE WHEN payrolcd = 'OPEDUE' THEN 'OPEDUE' ELSE NULL END, CASE WHEN payrolcd = 'CWADUE' THEN 'CWADUE' ELSE NULL END)
	) AS UPR30300_1 ON Employees_1.[Employee ID] = UPR30300_1.EMPLOYID

Open in new window

Sharath S

@PortletPaul: I don't think I am "SQL DEITY". I still need to learn a lot of hidden features in SQL.