February 26, 2009 at 10:58 pm
I have the following query that I am trying to optimize without a cursor.
I am getting the following error message:
Msg 512, Level 16, State 1, Line 26
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
I know it is the subquery. I need it to return one row at a time for the orderid from the @CaracasOrders table. This can be ran against the Northwind sample database. At the end of this, I will post the orginal query with the cursor. I need to optimize this without the cursor. Am I on the right track?
Can someone help, PLEASE
DECLARE @OrderId int
DECLARE @IMAX int
DECLARE @i int
DECLARE @Orders TABLE (
OrderId int,
ProductId int,
ItemTotal money
)
DECLARE @CaracasOrders TABLE (
OrderID int
)
SET @i = 1
INSERT @CaracasOrders
SELECT OrderID FROM Orders WITH (NOLOCK)
WHERE ShipCity = 'Caracas'
--WHILE (@i <= @@ROWCOUNT)
--BEGIN
INSERT @Orders
SELECT @OrderId, ProductId, (Quantity * UnitPrice) As ItemTotal
FROM [Order Details]
WHERE @OrderID = (select orderID from @CaracasOrders)
set @i = @i + 1
--END
select * from @orders
___________________________________________________
Here is the orginal query:
DECLARE @OrderId int
DECLARE @Orders TABLE (
OrderId int,
ProductId int,
ItemTotal money
)
DECLARE CaracasOrders CURSOR FAST_FORWARD READ_ONLY
FOR SELECT OrderID FROM Orders WITH (NOLOCK) WHERE ShipCity = 'Caracas'
OPEN CaracasOrders
FETCH NEXT FROM CaracasOrders INTO @OrderId
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @Orders
SELECT @OrderId, ProductId, (Quantity * UnitPrice) As ItemTotal FROM [Order Details] WHERE OrderID = @OrderID
FETCH NEXT FROM CaracasOrders INTO @OrderId
END
CLOSE CaracasOrders
DEALLOCATE CaracasOrders
SELECT * FROM @Orders
February 27, 2009 at 2:08 am
Asha Richardson (2/26/2009)
INSERT @OrdersSELECT @OrderId, ProductId, (Quantity * UnitPrice) As ItemTotal
FROM [Order Details]
WHERE @OrderID IN (select orderID from @CaracasOrders)
Use = when the subquery will return only one value for a match. When it can return multiple, use IN.
You're kinda on the right track, but not quite. There's no need for the @CaracasOrders table and you never assign a value to @OrderID (which you're using in the top query). I would do something like this.
DECLARE @OrderId int
DECLARE @Orders TABLE (
OrderId int,
ProductId int,
ItemTotal money
)
INSERT INTO @Orders (OrderID, ProductID, ItemTotal)
SELECT OrderID , ProductId, (Quantity * UnitPrice) As ItemTotal FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE ShipCity = 'Caracas')
SELECT * FROM @Orders
Do you use @Orders anywhere else in that procedure? If not, then the who table var thing is unnecessary, and the entire piece can be reduced to
SELECT OrderID , ProductId, (Quantity * UnitPrice) As ItemTotal FROM [Order Details]
WHERE OrderID IN (SELECT OrderID FROM Orders WHERE ShipCity = 'Caracas')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2009 at 12:11 pm
Thanks so much! You guys are great!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply