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
CPA_MCSEAsked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
can you try this?
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, 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 LEFT OUTER JOIN
                      HRORI012 ON Employees_1.[Employee ID] = HRORI012.EMPID_I LEFT OUTER JOIN
                      TE024230 ON Employees_1.[Employee ID] = TE024230.EMPID_I

Open in new window

0
 
Jared_SCommented:
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
0
 
CPA_MCSEAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
CPA_MCSEAuthor Commented:
You are a SQL DEITY!  Again, you come to my rescue.  Thank you! *bows deeply and humbly*
0
 
PortletPaulfreelancerCommented:
:)
@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

0
 
SharathData EngineerCommented:
@PortletPaul: I don't think I am "SQL DEITY". I still need to learn a lot of hidden features in SQL.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.