Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 590
  • Last Modified:

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
0
CPA_MCSE
Asked:
CPA_MCSE
1 Solution
 
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
 
SharathData 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now