October 13, 2009 at 8:06 am
I have a temporary table being filled with a long select statement. From this table I want to do this:
select * from @GlueRoomStock order by case_code, scheduleddate, shift
select scheduleddate, shift from @GlueRoomStock where currentgluedinvpieces<(select sum(cases_scheduled) from @glueroomstock group by case_code)
What I need to do is get a sum of the cases_scheduled as right now they are being returned for each date, but I want a total sum by case_code and then I need to find the date and shift in the table of when the value of currentgluedinvpieces is less than that value.
When I run this, I get the following error:
Msg 512, Level 16, State 1, Line 83
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
What would be the best way to handle this? Should I create another temporary table based on the first primary temporary table that just holds the run dates of the case_codes and then do the query based on that secondary temp table?
Thanks
October 13, 2009 at 8:40 am
I guess these are the two different queries that you are trying, (sorry if I've not understood ur requirement)
select * from @GlueRoomStock order by case_code, scheduleddate, shift
Should not it read something like this?
select scheduleddate, shift
from @GlueRoomStock G1
where currentgluedinvpieces in
(select sum(cases_scheduled)
from @glueroomstock G2
Where G2.Case_Code = G1.Case_Code
group by case_code)
---------------------------------------------------------------------------------
October 13, 2009 at 9:10 am
Thanks so much! That seemed to work.
October 13, 2009 at 11:07 am
your welcome 🙂
---------------------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply