SQL Subqueries in Select Clause

This is what causes you the error:

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

Select allOpen 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

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

@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.

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?

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

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

Re-testing...

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)

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?

I learned a magnificent new approach today!