July 14, 2011 at 1:58 am
Goodmorning,
I am having a problem with a query I wrote.
When I run it, the error:
Subquery returned more than 1 value
I can rewrite the query, by creating a while loop.
But I would like to understand why this one is not working...
Thanks in advance!
Regards Hans
This is the query:
select *
from tblWerknemerHistorie
WHERE wrhID =
(SELECT wrhid
FROM (
(SELECT wrkID, MAX(wrhid) as wrhid
FROM tblWerknemerHistorie
WHERE wrkID in (SELECT wrh.wrkid
FROM dbTR_20110708.dbo.vwWerknemers WRK
INNER JOIN dbTR_20110708.dbo.tblWerknemerHistorie WRH on WRK.wrkID=WRH.wrkID
where (wrh.wrhDatumEinde IS NULL OR
wrh.wrhDatumEinde >= getDate()) AND (wrh.wrhDatumStart <= getdate())
AND wrhADV = 1)
GROUP BY wrkid
)
) tab
GROUP BY wrhid)
July 14, 2011 at 2:17 am
run the inner query separately:
SELECT wrhid
FROM (
(SELECT wrkID, MAX(wrhid) as wrhid
FROM tblWerknemerHistorie
WHERE wrkID in (SELECT wrh.wrkid
FROM dbTR_20110708.dbo.vwWerknemers WRK
INNER JOIN dbTR_20110708.dbo.tblWerknemerHistorie WRH on WRK.wrkID=WRH.wrkID
where (wrh.wrhDatumEinde IS NULL OR
wrh.wrhDatumEinde >= getDate()) AND (wrh.wrhDatumStart <= getdate())
AND wrhADV = 1)
GROUP BY wrkid
)
) tab
GROUP BY wrhid
Most probably you'll get more than one row (based on more than one wrkID and different values for wrhid).
You might be able to use a TOP 1 ORDER BY (SELECT TOP 1 wrhid).
July 14, 2011 at 2:24 am
Thanks it's working fine now!!
Strange thing was that the subquery returned one column...
Maybe someting to do with the execution plan?
But again thanks!
Regards,
Hans
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply