Link to home
Start Free TrialLog in
Avatar of erikTsomik
erikTsomikFlag for United States of America

asked on

Subquery returned more than 1 value.

I am getting an error message
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I do know where the problem is but do not know how to correct the issue.

      
                DECLARE @userKey INT
                SET @userKey = 71963

                DECLARE @Dob   datetime
                SELECT  @Dob= (select dateOfBirth from users where userKey = 71963)
           
            SELECT top 1 F.sessionKey,F.locationKey,F.locationkeylist,F.officeStateKey,F.sessionStart,F.sessionEnd,F.locationName,F.address1,F.address2,F.city,F.state,F.zip,F.gender,F.badgeNum,
			F.seatCount,F.seats,F.availSeats,F.btwScheduleThreshold,F.availStateCode,F.repost,F.ConflictIn,F.ConflictIn2,F.buddyHoldIn,F.firstname,F.lastname,F.searcherAge,F.age,F.multiLoc
            FROM
            (
            	SELECT T.*,
                case WHEN T.availSeats = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 FROM users U inner join sessionMap SM2 on SM2.userKey = U.userKey WHERE SM2.sessionKey = T.sessionKey)
                    WHEN T.repost = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 FROM users U inner join sessionMap SM3 on SM3.userKey = U.userKey WHERE SM3.sessionKey = T.sessionKey and SM3.repost = 1)
                    ELSE 0
                    END as age
                    ,NULL as multiLoc
                FROM (
                    SELECT v.SessionKey,
                    v.locationKey,
                    v.locationKeyList,
                    v.officeStateKey,
                    v.sessionStart,
                    v.sessionEnd,
                    v.name AS locationName,
                    v.address1,
                    v.address2,
                    v.city,
                    v.state,
                    v.zip,
                    v.gender,
                    (
                        SELECT TOP 1 UIL.licenseCd
                        FROM userInstructorLicense UIL
                        WHERE UIL.userKey = v.instructorKey and UIL.expirationdt is not NULL
                    )as badgeNum,
                    (
                        SELECT COUNT(1)
                        FROM sessionMap SM
                        WHERE v.sessionKey = SM.sessionKey
                    ) AS seatCount,
                    v.Seats,
                    IsNull(v.btwSeatsOverride, v.Seats) - (
                        SELECT COUNT(1)
                        FROM sessionMap SM
                        WHERE v.sessionKey = SM.sessionKey
                         and sm.userKey <> 0
                    ) AS availSeats,
                    v.btwScheduleThreshold,
                    v.availStateCode,
                     (
                        SELECT top 1 isNull(SM.repost,0)
                        FROM SessionMap SM
                        WHERE SM.sessionKey = v.sessionKey
                        ORDER BY isNull(SM.repost,0) desc
                    ) as repost,
                  
                        (

                        SELECT TOP 1 SUX.sessionUnitKey
                            FROM sessionUnit SUX
                                INNER JOIN sessionMap SMX  ON SUX.sessionKey = SMX.sessionKey
                            WHERE SMX.userKey = @userKey
                                AND DAY(v.sessionStart) = DAY(SUX.sessionStart)
                                AND MONTH(v.sessionStart) = MONTH(SUX.sessionStart)
                                AND YEAR(v.sessionStart) = YEAR(SUX.sessionStart)
                            
							   and (v.sessionStart BETWEEN SUX.sessionStart and SUX.sessionEnd OR v.sessionEnd BETWEEN SUX.sessionStart and SUX.sessionEnd)
                               AND SMX.sessionMapKey != 382901


                        ) AS ConflictIn,

						 ( SELECT TOP 1 SUX.sessionUnitKey
							FROM sessionUnit SUX
							INNER JOIN sessionMap SMX ON SUX.sessionKey = SMX.sessionKey
							WHERE SMX.userKey = @userKey
							AND DAY(v.sessionStart) = DAY(SUX.sessionStart)
							AND MONTH(v.sessionStart) = MONTH(SUX.sessionStart)
							AND YEAR(v.sessionStart) = YEAR(SUX.sessionStart)
							AND SUX.unit is NULL
								
                                    AND SMX.sessionMapKey != 382901
                               
						 ) AS ConflictIn2,


                        (
                            SELECT COUNT(1)
                            FROM sessionMap SMH
                                INNER JOIN sessionMap SM1  ON SMH.sessionKey = SM1.sessionKey
                                    AND SM1.isBuddyHold = 1 AND SM1.userKey = 0
                                INNER JOIN users U  ON SMH.userKey = U.userKey
                                INNER JOIN buddy B  ON ((U.userKey = B.user1Key AND B.user2Key = @userKey) OR (U.userKey = B.user2Key AND B.user1Key = @userKey))
                            WHERE v.sessionKey = SMH.sessionKey
                        )  AS buddyHoldIn,
                        U.firstName,
                        U.lastName,
                        (CONVERT(int, CONVERT(varchar, v.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, @Dob, 112)))/10000 as searcherAge
                    

                    FROM vBTW v
                   
                        LEFT OUTER JOIN (
                            sessionMap SMH
                            INNER JOIN sessionMap SM1  WITH (NOLOCK) ON SMH.sessionKey = SM1.sessionKey
                                AND SM1.isBuddyHold = 1 AND SM1.userKey = 0 and sm1.type <> 'SBW'
                            INNER JOIN users U  WITH (NOLOCK) ON SMH.userKey = U.userKey
                            INNER JOIN buddy B WITH (NOLOCK)  ON ((U.userKey = B.user1Key AND B.user2Key = @userKey) OR (U.userKey = B.user2Key AND B.user1Key = @userKey))
                         ) ON v.sessionKey = SMH.sessionKey
                   
                    WHERE 1=1
and v.sessionKey = 290504
					and isNULL(v.suppressShowing,0) = 0
					and v.status = 'enabled'
					and datediff(hh, getdate(), v.sessionStart) > 72
					
					
                           
                            
                            
                   

                    AND (
                            (
                                SELECT COUNT(1)
                                FROM sessionMap SM
                                WHERE v.sessionKey = SM.sessionKey
                                 and sm.userKey <> 0
                            ) < ISNULL(v.btwSeatsOverride, v.seats)

                        
                       
                            OR (
                                EXISTS (
                                    SELECT top 1 SMH.sessionKey
                                    FROM sessionMap SMH
                                        INNER JOIN sessionMap SM1  ON SMH.sessionKey = SM1.sessionKey
                                            AND SM1.isBuddyHold = 1 AND SM1.userKey = 0
                                        INNER JOIN users U  ON SMH.userKey = U.userKey
                                        INNER JOIN buddy B  ON ((U.userKey = B.user1Key AND B.user2Key = @userKey) OR (U.userKey = B.user2Key AND B.user1Key = @userKey))
                                    WHERE v.sessionKey = SMH.sessionKey
                                )
                            )
                       
                    )
                        and not EXISTS (
                            select top 1 ssmm.sessionKey
                            from sessionMap ssmm
                            where ssmm.sessionKey = v.sessionKey
                            and ssmm.type = 'SBW'
                        )



                    
                       
                        AND NOT EXISTS (
                            SELECT top 1 SMX.sessionKey
                            FROM sessionMap SMX
                            WHERE SMX.userKey = @userKey
                                AND v.sessionKey = SMX.sessionKey
                        )
                       
                       
                            AND (
                                SELECT COUNT(1)
                                FROM sessionMap SM
                                WHERE v.sessionKey = SM.sessionKey
                            ) = 0
                        

						 

                        


                            

                          and v.locationkey is not null
						  and isNULL(v.suppressShowing,0) = 0
			

						
						   
						   


						   
						  OR EXISTS (
                            SELECT top 1 SMA.sessionKey
                            FROM SessionMap SMA WITH (NOLOCK)
                            INNER JOIN Session S2 WITH (NOLOCK) on S2.sessionKey = SMA.sessionKey
                           -- INNER JOIN Product P  WITH (NOLOCK) ON S2.productKey = P.productKey AND P.productTypeKey IN (2,4)
                            INNER JOIN SessionUnit SU3 WITH (NOLOCK) on SU3.sessionKey = SMA.sessionKey
                            WHERE SMA.SessionKey = v.sessionKey
                            AND (isNULL(SMA.repost,0) = 1 
							and isNULL(SMA.buyOut,0) = 0)
                          
                                and datediff(MINUTE, getdate(), SU3.sessionStart)/ 60 > 23
                           and S2.officeStateKey = 2
                           
                                    AND SMA.type = 'BW'
                               
                           
                         )
                )T
            )F

			WHERE 

			

           
                    ((F.age  = 0) OR (F.searcherAge < 18 and F.age < 18) OR (F.searcherAge >=18 AND F.age > = 18))


					 

                ORDER BY sessionStart
               

Open in new window

Once you remove the OR statement then everything is working. The OR statement produce 2 records even if I put select top 1 sessionKey OR select TOP 1
Avatar of Aneesh
Aneesh
Flag of Canada image

OR  EXISTS (   SELECT  1
                                               FROM SessionMap SMA WITH ( NOLOCK )
                                               INNER JOIN Session S2 WITH ( NOLOCK ) ON S2.sessionKey = SMA.sessionKey
                                               -- INNER JOIN Product P  WITH (NOLOCK) ON S2.productKey = P.productKey AND P.productTypeKey IN (2,4)
                                               INNER JOIN SessionUnit SU3 WITH ( NOLOCK ) ON SU3.sessionKey = SMA.sessionKey
                                               WHERE SMA.SessionKey = v.sessionKey
                                                     AND ( ISNULL( SMA.repost, 0 ) = 1 AND  ISNULL( SMA.buyOut, 0 ) = 0 )
                                                     AND DATEDIFF( MINUTE, GETDATE(), SU3.sessionStart ) / 60 > 23
                                                     AND S2.officeStateKey = 2
                                                     AND SMA.type = 'BW' )
Avatar of erikTsomik

ASKER

Still the same error
First of this is a very complex query and there are many OR clause in this.  Can you confirm which OR clause you removed?
OR EXISTS (
                            SELECT top 1 SMA.sessionKey
                            FROM SessionMap SMA WITH (NOLOCK)
                            INNER JOIN Session S2 WITH (NOLOCK) on S2.sessionKey = SMA.sessionKey
                           -- INNER JOIN Product P  WITH (NOLOCK) ON S2.productKey = P.productKey AND P.productTypeKey IN (2,4)
                            INNER JOIN SessionUnit SU3 WITH (NOLOCK) on SU3.sessionKey = SMA.sessionKey
                            WHERE SMA.SessionKey = v.sessionKey
                            AND (isNULL(SMA.repost,0) = 1
                                          and isNULL(SMA.buyOut,0) = 0)
                         
                                and datediff(MINUTE, getdate(), SU3.sessionStart)/ 60 > 23
                           and S2.officeStateKey = 2
                           
                                    AND SMA.type = 'BW'
                               
                           
                         )
Are there any updates
I feel this query is very complex to decipher, but what if you enclose the initial where clause up to the OR EXISTS (
                             SELECT top 1 SMA.sessionKey
Starting right after Where in line 1 of the code snippet below and closing it at line 53 of th snippet.
Not sure it that will work, but it was worth a shot being that there is no data to test against.

  WHERE (1=1  -- <-- opening  
					and v.sessionKey = 290504
					and isNULL(v.suppressShowing,0) = 0
					and v.status = 'enabled'
					and datediff(hh, getdate(), v.sessionStart) > 72
					AND (
                            (
                                SELECT COUNT(1)
                                FROM sessionMap SM
                                WHERE v.sessionKey = SM.sessionKey
                                 and sm.userKey <> 0
                            ) < ISNULL(v.btwSeatsOverride, v.seats)

                        
                       
                            OR (
                                EXISTS (
                                    SELECT top 1 SMH.sessionKey
                                    FROM sessionMap SMH
                                        INNER JOIN sessionMap SM1  ON SMH.sessionKey = SM1.sessionKey
                                            AND SM1.isBuddyHold = 1 AND SM1.userKey = 0
                                        INNER JOIN users U  ON SMH.userKey = U.userKey
                                        INNER JOIN buddy B  ON ((U.userKey = B.user1Key AND B.user2Key = @userKey) OR (U.userKey = B.user2Key AND B.user1Key = @userKey))
                                    WHERE v.sessionKey = SMH.sessionKey
                                )
                            )
                       
                    )
                        and not EXISTS (
                            select top 1 ssmm.sessionKey
                            from sessionMap ssmm
                            where ssmm.sessionKey = v.sessionKey
                            and ssmm.type = 'SBW'
                        )
						                   
                       
                        AND NOT EXISTS (
                            SELECT top 1 SMX.sessionKey
                            FROM sessionMap SMX
                            WHERE SMX.userKey = @userKey AND v.sessionKey = SMX.sessionKey
									   )
                       
                       
                            AND (
                                SELECT COUNT(1)
                                FROM sessionMap SM
                                WHERE v.sessionKey = SM.sessionKey
								) = 0
                               

                          and v.locationkey is not null
						  and isNULL(v.suppressShowing,0) = 0
							)   -- <--- Closing  
						  OR EXISTS (
                            SELECT top 1 SMA.sessionKey
                            FROM SessionMap SMA WITH (NOLOCK)
                            INNER JOIN Session S2 WITH (NOLOCK) on S2.sessionKey = SMA.sessionKey
                           -- INNER JOIN Product P  WITH (NOLOCK) ON S2.productKey = P.productKey AND P.productTypeKey IN (2,4)
                            INNER JOIN SessionUnit SU3 WITH (NOLOCK) on SU3.sessionKey = SMA.sessionKey
                            WHERE SMA.SessionKey = v.sessionKey
                            AND (isNULL(SMA.repost,0) = 1 
							and isNULL(SMA.buyOut,0) = 0)
                          
                                and datediff(MINUTE, getdate(), SU3.sessionStart)/ 60 > 23
                           and S2.officeStateKey = 2
                           
                                    AND SMA.type = 'BW'
                               
                           
									)

Open in new window

I am not sure how an exists condition throws error due to duplicate records. Can you comment out each of these conditions one by one and find the part thats causing the error.
@Aneesh.

This is a tough one to follow, but to me it looks like he has a bunch of AND clauses w/ OR, but then there is the final OR clause that is causing the error.  

I am having trouble following this statement, but I did my best.
Can you double check the code below
 
case WHEN T.availSeats = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
					FROM users U inner join sessionMap SM2 on SM2.userKey = U.userKey WHERE SM2.sessionKey = T.sessionKey)
                    WHEN T.repost = 1 THEN
                    (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
					FROM users U inner join sessionMap SM3 on SM3.userKey = U.userKey WHERE SM3.sessionKey = T.sessionKey and SM3.repost = 1)
                    ELSE 0
                    END as age
                    ,NULL as multiLoc

Open in new window


Are you sure that the sub-query always returns one value?
ASKER CERTIFIED SOLUTION
Avatar of erikTsomik
erikTsomik
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial