Solved

Distinct a Coalesce result?

Posted on 2013-05-14
6
563 Views
Last Modified: 2013-05-15
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
Comment
Question by:CPA_MCSE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Expert Comment

by:Jared_S
ID: 39165796
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
 

Author Comment

by:CPA_MCSE
ID: 39165968
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
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39166123
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Closing Comment

by:CPA_MCSE
ID: 39166225
You are a SQL DEITY!  Again, you come to my rescue.  Thank you! *bows deeply and humbly*
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167221
:)
@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
 
LVL 41

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Replication failure 1 31
Tracking Problematic Page Splits 1 49
SQL Server 2005 running VERY slowly on new hardware 22 61
What is GIS method of Geometry data type? 6 33
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question