troubleshooting Question

SQL Subqueries in Select Clause

Avatar of CPA_MCSE
CPA_MCSE asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SSRS
11 Comments2 Solutions1077 ViewsLast Modified:
I am trying to make a report wherein the user can select different pay codes for different amount columns.  Ultimately, I want to sum the results, so it shows as one line per employee, but I think I need to get past just getting raw results to show, first.

Here is the raw query as it stands now (I am using parameters in SSRS as per the screen shot because each column requires the user to select only those codes needed for that column), but in the raw SQL query where I am testing it I am getting the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

SELECT     Employees.[Social Security Number], Employees.[Last Name], Employees.[First Name], Employees.[Birth Date], Employees.[Start Date], HRORI012.REHIREDATE_I,
                      TE024230.TERMINATIONDATE_I, UPR30300.PAYROLCD, UPR30300.UPRTRXAM,
                      (select sum(UNTSTOPY) from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD Hours],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD Plan Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD Gross Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD 415 Excluded Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD 415 Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [YTD Plan Excluded Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600)) as [PreEntry Compensation],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct DEDUCTON from UPR40900)) as [Special Status],
                      (rtrim([Address 1])+', '+rtrim([City])+', '+rtrim([State])+', '+rtrim([Zip Code])) as [Location],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct DEDUCTON from UPR40900)) as [YTD Deferral Contribution],
                      (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct BENEFIT from UPR40800)) as [YTD Match Contribution]  
FROM         Employees INNER JOIN
                      UPR30300 ON Employees.[Employee ID] = UPR30300.EMPLOYID LEFT OUTER JOIN
                      HRORI012 ON Employees.[Employee ID] = HRORI012.EMPID_I LEFT OUTER JOIN
                      TE024230 ON Employees.[Employee ID] = TE024230.EMPID_I
                     
----------------------------------

ReportDropDowns
I think I need to somehow join the sub-queries to the main query so that the amounts show correctly for each employee row, but I do not know how to make that happen.  Ideas?
ASKER CERTIFIED SOLUTION
Sharath S
Data Engineer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros