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.
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#f5ce136 923ebffc3>
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
Edward 3 4188
Line 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Michelle |
last post by:
hi,
i have created an array from recordset containing user names
eg. (davidp, davidp, evenf, patricka, rebeccah)
which i have sorted in alphabetical order, but i need to
identify duplicates...
|
by: Marlene |
last post by:
Hi All
I have the following scenario, where I have found all the duplicates
in a table, based on an order number and a part number (item).I might
have something like this:
Order PODate Rec...
|
by: tyrfboard |
last post by:
I've been searching for awhile now on how to remove duplicates from a
table within an Access db and have found plenty of articles on finding
or deleting duplicates. All I want to do is remove them...
|
by: AK |
last post by:
Hi
Our product uses MS-SQL Server 2000. One of our customer has 10
installations with each installation stroring data in its own database.
Now the customer wants to consolidate these databases...
|
by: vsgdp |
last post by:
I have a container of pointers. It is possible for two pointers to point to
the same element. I want to remove duplicates.
I am open to which container is best for this.
I thought of using...
| |
by: ak |
last post by:
Is it possible to find repeated(duplicate) element in an array in
single loop ?
AK
|
by: ryan.paquette |
last post by:
In the table there are 2 fields in which I wish to limit (i.e. No
Duplicates)
Although I do not want to limit them to "No Duplicates" separately.
I need them to be limited to "No Duplicates" as...
|
by: john.cole |
last post by:
I have searched all the groups I can, and I still haven't been able to
come up the solution I need. I have the following problem.
In my form named sbfrmSpoolList, I am entering a job, spool and...
|
by: tskmjk55 |
last post by:
Recently, I have a requirement to develop a vb.net application wherein the input excel sheet data which has an average of 5000 records should be checked for Internal duplicates (duplicates within the...
|
by: Thekid |
last post by:
I'm trying to figure out a way to find if there are duplicates in an array. My idea was to take the array as 'a' and make a second array as 'b' and remove the duplicates from 'b' using 'set' and then...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |