Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    16

    Question Unanswered: subquery returned more than 1 value

    Hello compadres,

    I am getting this typical error: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

    Here is my SQL code and then I will explain:

    Code:
    SELECT DISTINCT
           PRSYSTEM.NAME AS "School",
           EMPLOYEE.DEPARTMENT AS "Dept",
           EMPLOYEE.LAST_NAME AS "Last Name",
           EMPLOYEE.FIRST_NAME AS "First Name",
           PAPOSITION.DESCRIPTION AS "Position",
           HREMPUSF.A_FIELD AS "Tenure Status",
           (SELECT DISTINCT ob1.ACTUAL_DATE
              FROM REVIEW ob1
             WHERE CODE = 'SUMMATIVE' AND EMPLOYEE = T.EMPLOYEE)
              AS "Summative"
      FROM    (   (   (   REVIEW T
                       RIGHT OUTER JOIN
                          prod.dbo.EMPLOYEE EMPLOYEE
                       ON (T.EMPLOYEE = EMPLOYEE.EMPLOYEE))
                   INNER JOIN
                      prod.dbo.PRSYSTEM PRSYSTEM
                   ON (PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL))
               INNER JOIN
                  prod.dbo.PAPOSITION PAPOSITION
               ON (PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION))
           LEFT OUTER JOIN
              prod.dbo.HREMPUSF HREMPUSF
           ON (HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE)
     WHERE EMPLOYEE.EMP_STATUS NOT IN
                 ('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
    To guess, the code written in blue is causing the problem since this is my subquery. I still want to list duplicates of an employee because all the information is not going to be the same. The error seems to be pulling up because there is an employee that is a duplicate. Any suggestions on how I can change the subquery without getting the error? I've tried "IN" and unfortunately I get the same message.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good Lord. Who taught you to write code that way? Do you get paid by the parenthesis, or was that autogenerated by some damnable ORM?

    Try this, which should read much easier:
    Code:
    SELECT	PRSYSTEM.NAME AS "School",
    	EMPLOYEE.DEPARTMENT AS "Dept",
    	EMPLOYEE.LAST_NAME AS "Last Name",
    	EMPLOYEE.FIRST_NAME AS "First Name",
    	PAPOSITION.DESCRIPTION AS "Position",
    	HREMPUSF.A_FIELD AS "Tenure Status",
    	REVIEW.ACTUAL_DATE AS "Summative"
    FROM	prod.dbo.EMPLOYEE EMPLOYEE
    	LEFT OUTER JOIN REVIEW
    		on EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
    		and REVIEW.CODE = 'SUMMATIVE'
    	INNER JOIN prod.dbo.PRSYSTEM PRSYSTEM ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
    	INNER JOIN prod.dbo.PAPOSITION PAPOSITION ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
    	LEFT OUTER JOIN prod.dbo.HREMPUSF HREMPUSF ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
    WHERE	EMPLOYEE.EMP_STATUS NOT IN ('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
    If this is returning multiple records (due to multiple REVIEW.ACTUAL_DATE records per employee), then you will need to either apply more filter criteria or use a max or min function on the ACTUAL_DATE depending upon your business rules.
    Last edited by blindman; 11-25-09 at 14:06.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2009
    Posts
    16

    Smile

    Haha. It was an ORM. I took the parenthesis out and made the suggested changes, THANK YOU!. Here is the full SQL. It looks a little bit congested. I'm sure there might be a better way to query and not hinder performance.

    Code:
    SELECT DISTINCT
           PRSYSTEM.NAME AS "School",
           EMPLOYEE.DEPARTMENT AS "Dept",
           EMPLOYEE.LAST_NAME AS "Last Name",
           EMPLOYEE.FIRST_NAME AS "First Name",
           PAPOSITION.DESCRIPTION AS "Position",
           HREMPUSF.A_FIELD AS "Tenure Status",
           REVIEW.ACTUAL_DATE AS "Pre-Ob 1",
           REVIEW_1.ACTUAL_DATE AS "Obsv 1",
           REVIEW_2.ACTUAL_DATE AS "PostConf 1",
           REVIEW_3.ACTUAL_DATE AS "Pre-Ob 2",
           REVIEW_4.ACTUAL_DATE AS "Obsv 2",
           REVIEW_5.ACTUAL_DATE AS "PostConf 2",
           REVIEW_6.ACTUAL_DATE AS "Pre-Ob 3",
           REVIEW_7.ACTUAL_DATE AS "Obsv 3",
           REVIEW_8.ACTUAL_DATE AS "PostConf 3",
           REVIEW_9.ACTUAL_DATE AS "Pre Ob 4",
           REVIEW_10.ACTUAL_DATE AS "Obsv 4",
           REVIEW_11.ACTUAL_DATE AS "PostConf 4",
           REVIEW_12.ACTUAL_DATE AS "Mentor 1",
           REVIEW_13.ACTUAL_DATE AS "Mentor 2",
           REVIEW_14.ACTUAL_DATE AS "Summative"
      FROM
    EMPLOYEE EMPLOYEE
    LEFT OUTER JOIN
    REVIEW
    ON EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
    AND REVIEW.CODE = 'PRE-OB 1  '
    LEFT OUTER JOIN
    REVIEW REVIEW_1
    ON EMPLOYEE.EMPLOYEE = REVIEW_1.EMPLOYEE
    AND REVIEW_1.CODE = 'OBSV1'
    LEFT OUTER JOIN
    REVIEW REVIEW_2
    ON EMPLOYEE.EMPLOYEE = REVIEW_2.EMPLOYEE
    AND REVIEW_2.CODE = 'POSTCONF 1'
    LEFT OUTER JOIN
    REVIEW REVIEW_3
    ON EMPLOYEE.EMPLOYEE = REVIEW_3.EMPLOYEE
    AND REVIEW_3.CODE = 'PRE-OB 2  '
    LEFT OUTER JOIN
    REVIEW REVIEW_4
    ON EMPLOYEE.EMPLOYEE = REVIEW_4.EMPLOYEE
    AND REVIEW_4.CODE = 'OBSV2'
    LEFT OUTER JOIN
    REVIEW REVIEW_5
    ON EMPLOYEE.EMPLOYEE = REVIEW_5.EMPLOYEE
    AND REVIEW_5.CODE = 'POSTCONF 2'
    LEFT OUTER JOIN
    REVIEW REVIEW_6
    ON EMPLOYEE.EMPLOYEE = REVIEW_6.EMPLOYEE
    AND REVIEW_6.CODE = 'PRE-OB 3  '
    LEFT OUTER JOIN
    REVIEW REVIEW_7
    ON EMPLOYEE.EMPLOYEE = REVIEW_7.EMPLOYEE
    AND REVIEW_7.CODE = 'OBSV3'
    LEFT OUTER JOIN
    REVIEW REVIEW_8
    ON EMPLOYEE.EMPLOYEE = REVIEW_8.EMPLOYEE
    AND REVIEW_8.CODE = 'POSTCONF 3'
    LEFT OUTER JOIN
    REVIEW REVIEW_9
    ON EMPLOYEE.EMPLOYEE = REVIEW_9.EMPLOYEE
    AND REVIEW_9.CODE = 'PRE-OB 4  '
    LEFT OUTER JOIN
    REVIEW REVIEW_10
    ON EMPLOYEE.EMPLOYEE = REVIEW_10.EMPLOYEE
    AND REVIEW_10.CODE = 'OBSV4'
    LEFT OUTER JOIN
    REVIEW REVIEW_11
    ON EMPLOYEE.EMPLOYEE = REVIEW_11.EMPLOYEE
    AND REVIEW_11.CODE = 'POSTCONF 4'
    LEFT OUTER JOIN
    REVIEW REVIEW_12
    ON EMPLOYEE.EMPLOYEE = REVIEW_12.EMPLOYEE
    AND REVIEW_12.CODE = 'MENTOR1'
    LEFT OUTER JOIN
    REVIEW REVIEW_13
    ON EMPLOYEE.EMPLOYEE = REVIEW_13.EMPLOYEE
    AND REVIEW_13.CODE = 'MENTOR2'
    LEFT OUTER JOIN
    REVIEW REVIEW_14
    ON EMPLOYEE.EMPLOYEE = REVIEW_14.EMPLOYEE
    AND REVIEW_14.CODE = 'SUMMATIVE'
    INNER JOIN
    PRSYSTEM PRSYSTEM
    ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
    INNER JOIN
    PAPOSITION PAPOSITION
    ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
    LEFT OUTER JOIN
    HREMPUSF HREMPUSF
    ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
    WHERE EMPLOYEE.EMP_STATUS NOT IN ('T1', 'T2', 'T3', 'T4', 'TC', 'SU', 'R1', 'L4', 'P6', 'P7')
    
    UNION
    SELECT DISTINCT
           PRSYSTEM.NAME AS "School",
           EMPLOYEE.DEPARTMENT AS "Dept",
           EMPLOYEE.LAST_NAME AS "Last Name",
           EMPLOYEE.FIRST_NAME AS "First Name",
           PAPOSITION.DESCRIPTION AS "Position",
           HREMPUSF.A_FIELD AS "Tenure Status",
           REVIEW.ACTUAL_DATE AS "Pre-Ob 1",
           REVIEW_1.ACTUAL_DATE AS "Obsv 1",
           REVIEW_2.ACTUAL_DATE AS "PostConf 1",
           REVIEW_3.ACTUAL_DATE AS "Pre-Ob 2",
           REVIEW_4.ACTUAL_DATE AS "Obsv 2",
           REVIEW_5.ACTUAL_DATE AS "PostConf 2",
           REVIEW_6.ACTUAL_DATE AS "Pre-Ob 3",
           REVIEW_7.ACTUAL_DATE AS "Obsv 3",
           REVIEW_8.ACTUAL_DATE AS "PostConf 3",
           REVIEW_9.ACTUAL_DATE AS "Pre Ob 4",
           REVIEW_10.ACTUAL_DATE AS "Obsv 4",
           REVIEW_11.ACTUAL_DATE AS "PostConf 4",
           REVIEW_12.ACTUAL_DATE AS "Mentor 1",
           REVIEW_13.ACTUAL_DATE AS "Mentor 2",
           REVIEW_14.ACTUAL_DATE AS "Summative"
      FROM
    EMPLOYEE EMPLOYEE
    LEFT OUTER JOIN
    REVIEW
    ON EMPLOYEE.EMPLOYEE = REVIEW.EMPLOYEE
    AND REVIEW.CODE = 'PRE-OB 1  '
    LEFT OUTER JOIN
    REVIEW REVIEW_1
    ON EMPLOYEE.EMPLOYEE = REVIEW_1.EMPLOYEE
    AND REVIEW_1.CODE = 'OBSV1'
    LEFT OUTER JOIN
    REVIEW REVIEW_2
    ON EMPLOYEE.EMPLOYEE = REVIEW_2.EMPLOYEE
    AND REVIEW_2.CODE = 'POSTCONF 1'
    LEFT OUTER JOIN
    REVIEW REVIEW_3
    ON EMPLOYEE.EMPLOYEE = REVIEW_3.EMPLOYEE
    AND REVIEW_3.CODE = 'PRE-OB 2  '
    LEFT OUTER JOIN
    REVIEW REVIEW_4
    ON EMPLOYEE.EMPLOYEE = REVIEW_4.EMPLOYEE
    AND REVIEW_4.CODE = 'OBSV2'
    LEFT OUTER JOIN
    REVIEW REVIEW_5
    ON EMPLOYEE.EMPLOYEE = REVIEW_5.EMPLOYEE
    AND REVIEW_5.CODE = 'POSTCONF 2'
    LEFT OUTER JOIN
    REVIEW REVIEW_6
    ON EMPLOYEE.EMPLOYEE = REVIEW_6.EMPLOYEE
    AND REVIEW_6.CODE = 'PRE-OB 3  '
    LEFT OUTER JOIN
    REVIEW REVIEW_7
    ON EMPLOYEE.EMPLOYEE = REVIEW_7.EMPLOYEE
    AND REVIEW_7.CODE = 'OBSV3'
    LEFT OUTER JOIN
    REVIEW REVIEW_8
    ON EMPLOYEE.EMPLOYEE = REVIEW_8.EMPLOYEE
    AND REVIEW_8.CODE = 'POSTCONF 3'
    LEFT OUTER JOIN
    REVIEW REVIEW_9
    ON EMPLOYEE.EMPLOYEE = REVIEW_9.EMPLOYEE
    AND REVIEW_9.CODE = 'PRE-OB 4  '
    LEFT OUTER JOIN
    REVIEW REVIEW_10
    ON EMPLOYEE.EMPLOYEE = REVIEW_10.EMPLOYEE
    AND REVIEW_10.CODE = 'OBSV4'
    LEFT OUTER JOIN
    REVIEW REVIEW_11
    ON EMPLOYEE.EMPLOYEE = REVIEW_11.EMPLOYEE
    AND REVIEW_11.CODE = 'POSTCONF 4'
    LEFT OUTER JOIN
    REVIEW REVIEW_12
    ON EMPLOYEE.EMPLOYEE = REVIEW_12.EMPLOYEE
    AND REVIEW_12.CODE = 'MENTOR1'
    LEFT OUTER JOIN
    REVIEW REVIEW_13
    ON EMPLOYEE.EMPLOYEE = REVIEW_13.EMPLOYEE
    AND REVIEW_13.CODE = 'MENTOR2'
    LEFT OUTER JOIN
    REVIEW REVIEW_14
    ON EMPLOYEE.EMPLOYEE = REVIEW_14.EMPLOYEE
    AND REVIEW_14.CODE = 'SUMMATIVE'
    INNER JOIN
    PRSYSTEM PRSYSTEM
    ON PRSYSTEM.PROCESS_LEVEL = EMPLOYEE.PROCESS_LEVEL
    INNER JOIN
    PAPOSITION PAPOSITION
    ON PAPOSITION.R_POSITION = EMPLOYEE.R_POSITION
    LEFT OUTER JOIN
    HREMPUSF HREMPUSF
    ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
    WHERE EMPLOYEE.R_POSITION IN ('0000-072')

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by subserved View Post
    Haha. It was an ORM.
    I've learned to recognize ORM code by its smell....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by blindman View Post
    Do you get paid by the parenthesis...
    Hey, you just insulted both of the professional LISP coders.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would never make fun of someone's speech impediment.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •