Subquery multiple value error...why? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subquery multiple value error...why?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2010-01-28 : 09:47:22

When I run the query below, I get:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I'm not using equality operators, I'm using the set operator IN, so not sure why it give this error? Any ideas?

select * from [dbo].[OrderItem] o
where o.ORDERITEM_ID IN
(
CASE WHEN @selectdate is NULL
THEN
(SELECT ORDERITEM_ID from [dbo].[OrderItem] WHERE UPDATEDDATE is not null)
ELSE
(SELECT ORDERITEM_ID FROM [dbo].[OrderItem]
WHERE CAST(FLOOR( CAST( @selectdate AS FLOAT ) )AS DATETIME) = CAST(FLOOR( CAST( LOGDATE AS FLOAT ) )AS DATETIME)
AND UPDATEDDATE is not null)
END
)

--PhB

phrankbooth
Posting Yak Master

162 Posts

Posted - 2010-01-28 : 10:01:10

Never mind, looks like CASE as subquery is not good.

Did this instead:

select * from [dbo].[OrderItem] o
where o.ORDERITEM_ID in
(SELECT ORDERITEM_ID from [dbo].[OrderItem] WHERE UPDATEDDATE is not null
AND (CAST(FLOOR( CAST( @selectdate AS FLOAT ) )AS DATETIME) = CAST(FLOOR( CAST( LOGDATE AS FLOAT ) )AS DATETIME))
OR @selectdate IS NULL)

--PhB

Kristen
Test

22859 Posts

Posted - 2010-01-28 : 10:01:38

Your SELECT is returning multiple values, and after CASE ... THEN you can only have a single value.

You clearly want multiple values in your IN clause, so you need to code it differently.


select * from [dbo].[OrderItem] o
where o.ORDERITEM_ID IN
(
	SELECT ORDERITEM_ID 
	from [dbo].[OrderItem] 
	WHERE	(@selectdate is NULL AND UPDATEDDATE is not null)
		OR (
			CAST(FLOOR( CAST( @selectdate AS FLOAT ) )AS DATETIME) = CAST(FLOOR( CAST( LOGDATE AS FLOAT ) )AS DATETIME)
			AND UPDATEDDATE is not null)
		)
)

and that can be slimmed to:


select * from [dbo].[OrderItem] o
where o.ORDERITEM_ID IN
(
	SELECT ORDERITEM_ID 
	from [dbo].[OrderItem] 
	WHERE	UPDATEDDATE is not null
		AND (
			   @selectdate is NULL
			OR CAST(FLOOR( CAST( @selectdate AS FLOAT ) )AS DATETIME) = CAST(FLOOR( CAST( LOGDATE AS FLOAT ) )AS DATETIME)
		)
)

and


CAST(FLOOR( CAST( @selectdate AS FLOAT ) )AS DATETIME) = CAST(FLOOR( CAST( LOGDATE AS FLOAT ) )AS DATETIME)
[code]
is more efficiently done as
[code]
LOGDATE >= DateAdd(Day, DateDiff(Day, 0, @selectdate), 0)
AND LOGDATE < DateAdd(Day, DateDiff(Day, 0, @selectdate), 1)

phrankbooth
Posting Yak Master

162 Posts

Posted - 2010-01-28 : 10:16:54

You are a scholar and a gentle lady!

Thank you!

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources