erikTsomik
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.
Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Subquery
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
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
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?
ASKER
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'
)
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'
)
ASKER
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.
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'
)
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.
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
Are you sure that the sub-query always returns one value?
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
Are you sure that the sub-query always returns one value?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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' )