Avatar of CPA_MCSE
CPA_MCSE
 asked on

SQL Subqueries in Select Clause

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?
SSRSMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
CPA_MCSE

8/22/2022 - Mon
Ioannis Paraskevopoulos

This is what causes you the error:

 (select UPRTRXAM from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600))

Open in new window


and the other like it.

When you have a subquery for a column, the subquery may not return more than one value.

Think of a query as a description of one row and one column returns more than one row...how is it going to handle that extra row?

I don't know what UPRTRXAM is, but i think you should SUM it.

Giannis
Kelvin Sparks

Try modifying each subquery along the lines of

(select sum(UNTSTOPY) from UPR30300 where UPR30300.PAYROLCD IN (select distinct PAYRCORD from UPR40600 WHERE [Employee ID] = Employees.[Empoyee ID]))

Kelvin
CPA_MCSE

ASKER
@jyparask - Yes, I know that is causing the error.  The thing I am trying to figure out is how to write the subquery so that when the user selects the pay codes for each column, it will give the correct numbers for each employee and each column.  

@kelvinsparks - Yes, something along the lines of that is what I think I am missing.  What I currently have in SSRS is:

(select sum(UNTSTOPY) from UPR30300 where UPR30300.PAYROLCD IN (select @PAYRCORD from UPR40600 where UPR30300.EMPLOYID = Employees.[Employee ID]))  

So, the user would select from a drop-down list of Paycodes (see previous screen shot) and choose the ones they need for each column.  The headbanger for me is getting each amount column to sum by employee so that each employee's info is displayed on a single row.  The whole

UPR30300.EMPLOYID = Employees.[Employee ID]

in the WHERE clause is redundant because that relationship is already established in the join, right?  But I do "think" changing something in the subquery is what I need to do.  I just can't seem to figure out exactly what that is and if I should tackle it in SQL or use some SSRS feature I don't know about...

@jimpen - Yes, these are GP tables, but it's a SQL problem totally independent of the GP app.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
SOLUTION
Kelvin Sparks

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sharath S

Instead of sub-queries, do this with JOINs. What is the logic to calculate       [YTD Plan Compensation] and other subsequent columns in the SELECT clause?
CPA_MCSE

ASKER
Yes!  Just after I posted, I decided to try joins instead.  So my new query in SSRS runs without the error, but the resulting sums are waaaay too high to be for each employee.  (@kelvinsparks first reply and my reply to that about the WHERE is what lit the spark in the darkness - good to see your most recent reply came to the same conclusion as I was testing).

Digging into why the numbers are so large now.  Here is the SSRS query as it stands now:

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.PAYROLCD, UPR30300_1.UPRTRXAM,
                          (SELECT     SUM(UPR30300.UNTSTOPY) AS Expr1
                            FROM          UPR30300 INNER JOIN
                                                   Employees ON UPR30300.EMPLOYID = Employees.[Employee ID]
                            WHERE      (UPR30300.PAYROLCD IN (@YTDHours))) AS [YTD Hours],
                          (SELECT     SUM(UPR30300_10.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_10 INNER JOIN
                                                   Employees AS Employees_10 ON UPR30300_10.EMPLOYID = Employees_10.[Employee ID]
                            WHERE      (UPR30300_10.PAYROLCD IN (@YTDPlanCompensation))) AS [YTD Plan Compensation],
                          (SELECT     SUM(UPR30300_9.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_9 INNER JOIN
                                                   Employees AS Employees_9 ON UPR30300_9.EMPLOYID = Employees_9.[Employee ID]
                            WHERE      (UPR30300_9.PAYROLCD IN (@YTDGrossCompensation))) AS [YTD Gross Compensation],
                          (SELECT     SUM(UPR30300_8.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_8 INNER JOIN
                                                   Employees AS Employees_8 ON UPR30300_8.EMPLOYID = Employees_8.[Employee ID]
                            WHERE      (UPR30300_8.PAYROLCD IN (@YTD415ExcludedCompensation))) AS [YTD 415 Excluded Compensation],
                          (SELECT     SUM(UPR30300_7.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_7 INNER JOIN
                                                   Employees AS Employees_7 ON UPR30300_7.EMPLOYID = Employees_7.[Employee ID]
                            WHERE      (UPR30300_7.PAYROLCD IN (@YTD415Compensation))) AS [YTD 415 Compensation],
                          (SELECT     SUM(UPR30300_6.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_6 INNER JOIN
                                                   Employees AS Employees_6 ON UPR30300_6.EMPLOYID = Employees_6.[Employee ID]
                            WHERE      (UPR30300_6.PAYROLCD IN (@YTDPlanExcludedCompensation))) AS [YTD Plan Excluded Compensation],
                          (SELECT     SUM(UPR30300_5.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_5 INNER JOIN
                                                   Employees AS Employees_5 ON UPR30300_5.EMPLOYID = Employees_5.[Employee ID]
                            WHERE      (UPR30300_5.PAYROLCD IN (@PreEntryCompensation))) AS [PreEntry Compensation],
                          (SELECT     SUM(UPR30300_4.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_4 INNER JOIN
                                                   Employees AS Employees_4 ON UPR30300_4.EMPLOYID = Employees_4.[Employee ID]
                            WHERE      (UPR30300_4.PAYROLCD IN (@SpecialStatus))) AS [Special Status], RTRIM(Employees_1.[Address 1]) + ', ' + RTRIM(Employees_1.City)
                      + ', ' + RTRIM(Employees_1.State) + ', ' + RTRIM(Employees_1.[Zip Code]) AS Location,
                          (SELECT     SUM(UPR30300_3.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_3 INNER JOIN
                                                   Employees AS Employees_3 ON UPR30300_3.EMPLOYID = Employees_3.[Employee ID]
                            WHERE      (UPR30300_3.PAYROLCD IN (@YTDDeferralContribution))) AS [YTD Deferral Contribution],
                          (SELECT     SUM(UPR30300_2.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_2 INNER JOIN
                                                   Employees AS Employees_2 ON UPR30300_2.EMPLOYID = Employees_2.[Employee ID]
                            WHERE      (UPR30300_2.PAYROLCD IN (@YTDMatchContribution))) AS [YTD Match Contribution]
FROM         Employees AS Employees_1 INNER JOIN
                      UPR30300 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_MCSE

ASKER
D'oh!  Forgot to add the year parameter...

Re-testing...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CPA_MCSE

ASKER
Ok, so I'm thinking employees should not be working several million hours per year each and earning hundreds of millions.  Where am I screwing up..?

I really hate that SSRS automatically adds the underscore and number suffix everywhere.  Could that be a problem?

The latest with the @Year included:

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.PAYROLCD, UPR30300_1.UPRTRXAM,
                          (SELECT     SUM(UPR30300.UNTSTOPY) AS Expr1
                            FROM          UPR30300 INNER JOIN
                                                   Employees ON UPR30300.EMPLOYID = Employees.[Employee ID]
                            WHERE      (UPR30300.PAYROLCD IN (@YTDHours)) AND (UPR30300.YEAR1 = @Year)) AS [YTD Hours],
                          (SELECT     SUM(UPR30300_10.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_10 INNER JOIN
                                                   Employees AS Employees_10 ON UPR30300_10.EMPLOYID = Employees_10.[Employee ID]
                            WHERE      (UPR30300_10.PAYROLCD IN (@YTDPlanCompensation)) AND (UPR30300_10.YEAR1 = @Year)) AS [YTD Plan Compensation],
                          (SELECT     SUM(UPR30300_9.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_9 INNER JOIN
                                                   Employees AS Employees_9 ON UPR30300_9.EMPLOYID = Employees_9.[Employee ID]
                            WHERE      (UPR30300_9.PAYROLCD IN (@YTDGrossCompensation)) AND (UPR30300_9.YEAR1 = @Year)) AS [YTD Gross Compensation],
                          (SELECT     SUM(UPR30300_8.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_8 INNER JOIN
                                                   Employees AS Employees_8 ON UPR30300_8.EMPLOYID = Employees_8.[Employee ID]
                            WHERE      (UPR30300_8.PAYROLCD IN (@YTD415ExcludedCompensation)) AND (UPR30300_8.YEAR1 = @Year)) AS [YTD 415 Excluded Compensation],
                          (SELECT     SUM(UPR30300_7.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_7 INNER JOIN
                                                   Employees AS Employees_7 ON UPR30300_7.EMPLOYID = Employees_7.[Employee ID]
                            WHERE      (UPR30300_7.PAYROLCD IN (@YTD415Compensation)) AND (UPR30300_7.YEAR1 = @Year)) AS [YTD 415 Compensation],
                          (SELECT     SUM(UPR30300_6.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_6 INNER JOIN
                                                   Employees AS Employees_6 ON UPR30300_6.EMPLOYID = Employees_6.[Employee ID]
                            WHERE      (UPR30300_6.PAYROLCD IN (@YTDPlanExcludedCompensation)) AND (UPR30300_6.YEAR1 = @Year)) AS [YTD Plan Excluded Compensation],
                          (SELECT     SUM(UPR30300_5.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_5 INNER JOIN
                                                   Employees AS Employees_5 ON UPR30300_5.EMPLOYID = Employees_5.[Employee ID]
                            WHERE      (UPR30300_5.PAYROLCD IN (@PreEntryCompensation)) AND (UPR30300_5.YEAR1 = @Year)) AS [PreEntry Compensation],
                          (SELECT     SUM(UPR30300_4.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_4 INNER JOIN
                                                   Employees AS Employees_4 ON UPR30300_4.EMPLOYID = Employees_4.[Employee ID]
                            WHERE      (UPR30300_4.PAYROLCD IN (@SpecialStatus)) AND (UPR30300_4.YEAR1 = @Year)) AS [Special Status], RTRIM(Employees_1.[Address 1])
                      + ', ' + RTRIM(Employees_1.City) + ', ' + RTRIM(Employees_1.State) + ', ' + RTRIM(Employees_1.[Zip Code]) AS Location,
                          (SELECT     SUM(UPR30300_3.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_3 INNER JOIN
                                                   Employees AS Employees_3 ON UPR30300_3.EMPLOYID = Employees_3.[Employee ID]
                            WHERE      (UPR30300_3.PAYROLCD IN (@YTDDeferralContribution)) AND (UPR30300_3.YEAR1 = @Year)) AS [YTD Deferral Contribution],
                          (SELECT     SUM(UPR30300_2.UPRTRXAM) AS Expr1
                            FROM          UPR30300 AS UPR30300_2 INNER JOIN
                                                   Employees AS Employees_2 ON UPR30300_2.EMPLOYID = Employees_2.[Employee ID]
                            WHERE      (UPR30300_2.PAYROLCD IN (@YTDMatchContribution)) AND (UPR30300_2.YEAR1 = @Year)) AS [YTD Match Contribution], UPR30300_1.YEAR1
FROM         Employees AS Employees_1 INNER JOIN
                      UPR30300 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
WHERE     (UPR30300_1.YEAR1 = @Year)
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
CPA_MCSE

ASKER
Holy Yoda!  That is a fantastic approach!  The numbers look right.

I am humbled <*bows and kisses Sharath_123's feet*>.    

This seems to solve my primary problem.  However, kelvinsparks also helped me there, too.  I hope you don't mind me splitting the points?
CPA_MCSE

ASKER
I learned a magnificent new approach today!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23