Solved

Distinct a Coalesce result?

Posted on 2013-05-14
6
558 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
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 40

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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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 40

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 43
Sql Server group by 10 45
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 47
union query column need default text 2 15
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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