message. The Stored Procedure runs correctly about 1550 times, but
receive the following error three times:
Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
Line 68
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
I've done some digging, and the error message is moderately
self-explanatory.
The problem is that there is no Line 68 in the Stored Procedure. It's
the comment line:
-- Need to find out how many hours the employee is scheduled etc.
Also, there are no duplicate records in the Employee table nor the
WeeklyProfile table. At least I assume so - if the following SQL to
detect duplicates is correct!
SELECT E.*
FROM
Employee E
join
(select EmployeeID
from
Employee
Group by EmployeeID
having count(*) > 1) as E2
On
(E.EmployeeID = E2.EmployeeID)
SELECT
W.*
FROM
WeekProfile W
join
(Select
WeekProfileID
FROM
WeekProfile
GROUP BY
EmployeeID, MondayHours, WeekProfileID
HAVING COUNT(*) > 1) AS W2
ON
W.WeekProfileID = W2.WeekProfileID
NOTE: In the second statement, I have tried for MondayHours thru
FridayHours.
Anyone got any ideas? The TableDefs are set up in this thread:
<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3
The Stored Procedure that causes the error is here:
--************************************************** ***********
CREATE PROCEDURE BackFillNetworkHours
AS
DECLARE @.EmployeeID int
DECLARE @.TimesheetDate DateTime
DECLARE @.NumMinutes int
DECLARE @.NetworkCode int
-- Get the WorkID corresponding to Project Code 2002
SELECT
@.NetworkCode = WorkID
FROM
[Work]
WHERE
(WorkCode = '2002')
-- Open a cursor on a SELECT for all Network Support Employees where
any single workday comprises fewer than 7.5 hours
DECLARE TooFewHours CURSOR FOR
SELECT
EmployeeID,
CONVERT(CHAR(8), Start, 112) AS TimesheetDate,
SUM(NumMins) AS TotalMins
FROM
(SELECT
TI.EmployeeID,
W.WorkCode,
TI.Start AS Start,
SUM(TI.DurationMins) AS NumMins
FROM
TimesheetItem TI LEFT JOIN
[Work] W ON TI.WorkID = W.WorkID
WHERE EXISTS
(SELECT
*
FROM
Employee E
WHERE
((TI.EmployeeID = E.EmployeeID) AND
(E.DepartmentID = 2)))
GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS x
GROUP BY
EmployeeID,
CONVERT(char(8), Start, 112)
HAVING
SUM(NumMins) < 450
ORDER BY
EmployeeID,
CONVERT(CHAR(8), Start, 112)
-- Get the EmployeeID, Date and Number of Minutes from the cursor
OPEN TooFewHours
FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
WHILE (@.@.FETCH_STATUS=0)
BEGIN
DECLARE @.NewWorkTime datetime
DECLARE @.TimesheetString varchar(50)
DECLARE @.Duration int
DECLARE @.RequiredDuration int
-- Set the correct date to 08:30 - by default the cast from the
cursor's select statement is midday
SET @.TimesheetString = @.TimesheetDate + ' 08:30'
SET @.NewWorkTime = CAST(@.TimesheetString AS Datetime)
-- Need to find out how many hours the employee is scheduled to work
that day.
SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
WHEN 1 THEN
(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 2 THEN
(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 3 THEN
(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 4 THEN
(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 5 THEN
(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 6 THEN
(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
WHEN 7 THEN
(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
(EmployeeID = @.EmployeeID))
END
IF @.NumMinutes < @.RequiredDuration
BEGIN
-- Set the Start for the dummy work block to 08:30 + the number of
minutes the employee has already worked that day
SET @.NewWorkTime = DateAdd(minute, @.NumMinutes, @.NewWorkTime)
-- Set the duration for the dummy work block to be required duration
less the amount they've already worked
SET @.Duration = @.RequiredDuration - @.NumMinutes
-- Now we have the correct data - insert into table.
INSERT INTO TimesheetItem
(EmployeeID,
Start,
DurationMins,
WorkID)
VALUES
(@.EmployeeID,
@.NewWorkTime,
@.Duration,
@.NetworkCode)
END
FETCH NEXT FROM TooFewHours INTO @.EmployeeID, @.TimesheetDate,
@.NumMinutes
END
CLOSE TooFewHours
DEALLOCATE TooFewHours
GO
--************************************************** ***********
Thanks
EdwardLine 68 refers to the 68th line of text in a batch after a GO command;
this may cause you to miss where the failing subquery is. Obviously
it's the section where you set @.RequiredDuration; you mentioned that
you tested Monday-Friday, what about Saturday and Sunday?
Stu|||On 28 Jul 2005 07:03:11 -0700, Will wrote:
>I am attempting to execute the Stored Procedure at the foot of this
>message. The Stored Procedure runs correctly about 1550 times, but
>receive the following error three times:
>Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,
>Line 68
>Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as
>an expression.
(snip)
Hi Will/Edward,
Before I get to some other issues, let's first tackle this error. I
can't trace back where your line 68 is due to line breaks inserted by
either your Usenet posting software or my reader, but I'm willing to bet
that it is one of the seven subqueries in this part:
>SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
>WHEN 1 THEN
>(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))
(snippety)
>WHEN 7 THEN
>(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
>(EmployeeID = @.EmployeeID))
Checking the thread you refered to for the table definitions, I see that
WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
PeriodStart. The subselects above are only guaranteed to bring back one
value if the PK is on EmployeeID only. Since changes to the work profile
of employees tend to be rare, the subquery would still return 1 row for
most of your employees, but apparently, 3 out of your 1550 employees now
have a new WeekProfile and your subquery brings up both. Run this to
find the offenders:
SELECT EmployeeID, COUNT(*)
FROM WeekProfile
GROUP BY EmployeeID
HAVING COUNT(*) > 1
The thread you refered to was an interesting read in itself. This was
the first time I saw it, since I was on holiday when you first posted
it, and I decided to skip most unread messages when I came back from the
holiday and found well over a thousand new messages in the groups I
frequent.
Anyway, several things bother me. You asked for a way to avoid the
cursor in this code, Erland supplied you with one - and now, you are
busy solving bugs in the same cursor-based code you said you wanted to
replace. Why didn't you implement Erland's suggestion?
Also, Joe Celko has already given you his usual treatment so I won't
comment on the database design too much (and yes, I did read that you
are only the poor guy who inherited this DB). But I do agree with Joe:
the design has much room for improvement.
Finally:
>-- Set the correct date to 08:30 - by default the cast from the
>cursor's select statement is midday
>SET @.TimesheetString = @.TimesheetDate + ' 08:30'
Not midday, but mignight. I know it's "only" the comment, but small
errors in comments will waste many hours of your successor's time.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:
> (snip)
> Hi Will/Edward,
> Before I get to some other issues, let's first tackle this error. I
> can't trace back where your line 68 is due to line breaks inserted by
> either your Usenet posting software or my reader, but I'm willing to bet
> that it is one of the seven subqueries in this part:
> >SET @.RequiredDuration = CASE (DATEPART(dw, @.NewWorkTime))
> >WHEN 1 THEN
> >(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> (snippety)
> >WHEN 7 THEN
> >(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE
> >(EmployeeID = @.EmployeeID))
> Checking the thread you refered to for the table definitions, I see that
> WeekProfile has a compound PRIMARY KEY constraint on EmployeeID plus
> PeriodStart. The subselects above are only guaranteed to bring back one
> value if the PK is on EmployeeID only. Since changes to the work profile
> of employees tend to be rare, the subquery would still return 1 row for
> most of your employees, but apparently, 3 out of your 1550 employees now
> have a new WeekProfile and your subquery brings up both. Run this to
> find the offenders:
> SELECT EmployeeID, COUNT(*)
> FROM WeekProfile
> GROUP BY EmployeeID
> HAVING COUNT(*) > 1
You're right - there was an offender. Thank you.
> Anyway, several things bother me. You asked for a way to avoid the
> cursor in this code, Erland supplied you with one - and now, you are
> busy solving bugs in the same cursor-based code you said you wanted to
> replace. Why didn't you implement Erland's suggestion?
Well, the problem was (and is) that in order to have any hope of
getting an answer here, I felt it necessary to simplify matters
greatly. When it came to solving the actual problem, I couldn't work
out a way to do it with "raw" SQL. Plus, this is a one-time only data
cleansing exercise, so it can run in a batch over the weekend and
performance isn't an issue. I asked the question because I was
interested in an abstract way whether the problem could be solved.
> Also, Joe Celko has already given you his usual treatment so I won't
> comment on the database design too much (and yes, I did read that you
> are only the poor guy who inherited this DB). But I do agree with Joe:
> the design has much room for improvement.
What he probably doesn't appreciate (and why should he - I didn't tell
him!) is that this is purely an internal system, that is modelled on at
least two existing systems from which data must be extracted. Sure
there are kludges, but it does work (it's in Beta test at the moment
and seems remarkably stable and reliable, pace the legacy data which I
am in the process of cleaning up.)
> Finally:
> >-- Set the correct date to 08:30 - by default the cast from the
> >cursor's select statement is midday
> >SET @.TimesheetString = @.TimesheetDate + ' 08:30'
> Not midday, but mignight. I know it's "only" the comment, but small
> errors in comments will waste many hours of your successor's time.
I couldn't agree more - the comments have been changed. However, vide
supra, this is a one-time, throw away operation and the comments are
more for the benefit of the group than any later developer.
But many thanks to you and the others for your kind and helpful
suggestions.
Edward
No comments:
Post a Comment