Subquery returned more than 1 value - SQL 2000

Question

Post reply

Subquery returned more than 1 value - SQL 2000

Kicking_Fish0713

SSC Eights!

Points: 896
More actions
October 22, 2008 at 2:39 am

#96562

Hi,
What's wrong with my code?
DECLARE @ILN varchar(20),@oper varchar(4),@CT_perStation numeric(21,7) ,@CTField varchar(20),@strSqlFetch nvarchar(200)
Set @CTField = ''
DECLARE CT_Cursor CURSOR FOR
SELECT Internal_Lot_Number,Station_Code,Cycle_Time_perStation
FROM ##tmpP1WIP_MFG_LotMaster
OPEN CT_Cursor
FETCH NEXT FROM CT_Cursor INTO @ILN,@oper,@CT_perStation
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CTField = (SELECT Cycle_Time_perStation
FROM ##tmpP1WIP_MFG_LotMaster
WHERE Station_Code IN (SELECT DISTINCT Field_Value_1
FROM ##P1WIP_MFG_CT_Table))
SET @strSqlFetch = 'INSERT INTO ##P1WIP_MFG_STN (Internal_Lot_Number,Station,Cycle_Time_perStation)
VALUES('+''''+@ILN+''''+','+''''+@oper+''''+','+ '+' +@CTField+')'
-- print @strSqlFetch
EXEC sp_executesql @strSqlFetch
FETCH NEXT FROM CT_Cursor INTO @ILN,@oper,@CTField
END
CLOSE CT_Cursor
DEALLOCATE CT_Cursor
Here's error I encountered:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

This subquery " (SELECT Cycle_Time_perStation

FROM ##tmpP1WIP_MFG_LotMaster

WHERE Station_Code IN (SELECT DISTINCT Field_Value_1

FROM ##P1WIP_MFG_CT_Table))" is returning multiple values.

Since you are using this query to assign value to a variable the sub query should return only one value.

"Keep Trying"

Kicking_Fish0713 SSC Eights! Points: 896 More actions · Answer 2

Hi,

Thanks for a quick response.

I've added below conditions on my program:

Internal_Lot_Number = @ILN

AND Station_Code = @oper

Thanks again.Good day and God Bless!

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

ChiragNS

One Orange Chip

Points: 26137

October 23, 2008 at 2:26 am

#888501

Welcome...:)

"Keep Trying"

Scott D-810830 SSC-Addicted Points: 494 More actions · Answer 4

I don't see the need for a cursor at all here. I don't know enough about your tables, and I also don't know where you added your extra conditions, to be sure of what the proper structure should be, but these are some initial ideas.

The cursor simply walks the entire ##tmpP1WIP_MFG_LotMaster table. For each record, it grabs Internal_Lot_Number and Station_Code. It then uses those to filter for just that record from the very same ##tmpP1WIP_MFG_LotMaster table, but now just filtering with the WHERE clause for just some Station_Codes.

It seems like this would accomplish the exact same thing.

INSERT INTO ##P1WIP_MFG_STN (Internal_Lot_Number, Station, Cycle_Time_perStation)

SELECT m.Internal_Lot_Number,

m.Station_Code,

m.Cycle_Time_perStation

FROM ##tmpP1WIP_MFG_LotMaster m

INNER JOIN (SELECT DISTINCT Field_Value_1

FROM ##P1WIP_MFG_CT_Table) d ON m.Station_Code = d.Field_Value_1

Scott