error 512- newbie

[thminer]

i am now having difficulty with error 512,
"Server: Msg 512, Level 16, State 1, Procedure WeeklyBonus, Line 40
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

the issue is that there are multiple records for each employee, and i want them condensed into one, but do not know the syntax to do this. for example, in line 40,
"Set @totalhours = (Select ldRegHrs From LD) + (Select ldOvtHrs From LD)"

in this line, there may be 25 lines for one employee, and i want them added up. this error is dislayed for these lines;


Set @totalhours = (Select ldRegHrs From LD) + (Select ldOvtHrs From LD)


If (Select emSelect1 From EM) = "salary"
Set @paidhours = @weeksworked * 40
Else
Set @paidhours = @totalhours


If (Select emStatus From EM) = "A" and (Select emSelect18 From EM) = 0
Set @weeksworked = @weeknumber
Else
If @weeknumber < (Select emSelect18 From EM)
Set @weeksworked = 0
Else
If @weeknumber = (Select emSelect18 From EM)
Set @weeksworked = 1

these are copied out of a stored procedure, so they may not have the correct beginning and ending syntax.

thanks for any help. i am stumped.

 

[SQLDenis]

maybe this
Set @totalhours = (Select sum(ldRegHrs) From LD) + (Select sum(ldOvtHrs) From LD)



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[thminer]

thnx! it worked great for that line, i will try all of the other lines with the same logic.

 

[thminer]

how about on a line like this? the line is question is the first, in bold. there are many records where emselect1 = salary.

If (Select emSelect1 From EM) = "salary"
Set @paidhours = @weeksworked * 40
Else
Set @paidhours = @totalhours

i think the problem is i have not distinguished that certain records may be combined(same employee) and others may not. any suggestions?

 

[SQLDenis]

If exists (Select * From EM where emSelect1 ='Salary' )
Set @paidhours = @weeksworked * 40
Else
Set @paidhours = @totalhours

Something like this?, it's hard to answer your questions without seeing DDL and actual data

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[thminer]

ok- the whole code is included, and the lines that return errors are bold. thanks a lot.

CREATE PROCEDURE [dbo].[WeeklyBonus] AS

/* declare all variables*/
Begin
Declare @target int
Declare @vacused int
Declare @weeklydirecthours int
Declare @totalhoursworked int
Declare @bonuspayout money
Declare @directhours int
Declare @productivity int
Declare @jobcostrate int
Declare @weeksworked int
Declare @paidhours int
Declare @weeknumber int
Declare @termweekadj int
Declare @totalhours int
Declare @weeklytotalover45 int
/* Define week number*/
Select @weeknumber = 37

/* Define taget variable*/
Select @target = emSelect16 * 100 From EM

/*Define job rate variable*/
Select @jobcostrate = emJobCostRate From EM

/* define total hours worked*/
Select @totalhoursworked = ldRegHrs + ldOvtHrs From LD
Where LD.ldDate Between 09-03-2005 and 09-10-2005

/*define vacused variable*/
Select @vacused = ldRegHrs + ldOvtHrs From LD
Where LD.ldLaborCode like "312??"

/* Define totalhours*/
Set @totalhours = (Select sum(ldRegHrs) From LD) + (Select sum(ldOvtHrs) From LD)

/* Define paid hours*/
If exists (Select * From EM where emSelect1 = "Salary")
Set @paidhours = @weeksworked * 40
Else
Set @paidhours = @totalhours

/* Define Direct Hours*/
Select @directhours = ldRegHrs + ldOvtHrs From LD
Where Not LD.ldProject Like "3000010??"
And
Not LD.ldLaborCode Like "999??"
And
Not LD.ldLaborCode Like "???99"
And
Not LD.ldLaborCode Like "3????"
And
Not LD.ldLaborCode Like "500??"

/*Define weekly direct hours*/
Select @weeklydirecthours = ldRegHrs + ldOvtHrs From LD
Where Not LD.ldProject Like "3000010??"
And
Not LD.ldLaborCode Like "999??"
And
Not LD.ldLaborCode Like "???99"
And
Not LD.ldLaborCode Like "3????"
And
Not LD.ldLaborCode Like "500??"
And LD.ldDate between 09-03-2005 and 09-10-2005

/* Define productivity*/
If (Select emEmployee from EM) = " " and @paidhours = 0
Set @productivity = 0
Else
If exists (Select * From EM where emSelect1 = "Salary")
Set @productivity = (@directhours - 0.0) / ((@weeksworked * 40) - @Vacused)
Else
Set @productivity = (@directhours) / (@paidhours)

If @paidhours = 0
Set @productivity = 0
Else
If (Select emselect1 From EM) = "Salary"
Set @productivity = (@directhours - (Select emSelect21 From EM)) / ((@weeksworked * 40) - @vacused)
Else
Set @productivity = (@directhours) / (@paidhours)
End

/* Define weeksworked */
If (Select emStatus From EM) = "A" and (Select emSelect18 From EM) = 0
Set @weeksworked = @weeknumber
Else
If @weeknumber < (Select emSelect18 From EM)
Set @weeksworked = 0
Else
If @weeknumber = (Select emSelect18 From EM)
Set @weeksworked = 1
Else
If @weeknumber > (Select emSelect18 From EM)
Set @weeksworked = @weeknumber - ((Select emSelect18 From EM) - 1)
Else
If @weeknumber < (Select emSelect18 From EM)
Set @weeksworked = 0
Else
If (Select emSelect18 From EM) = 0 and @weeknumber <= @termweekadj
Set @weeksworked = @weeknumber
Else
Set @weeksworked = @termweekadj

/* Define weeklytotalover45 */
if @totalhoursworked > 45
Set @Weeklytotalover45 = @totalhoursworked - 45
Else
Set @weeklytotalover45 = 0

/* Define bonus payout */
If @productivity > @target
Set @bonuspayout = @weeklytotalover45 * (Select emJobCostRate From EM)
Else
Set @bonuspayout = 0


/*Select Statement for whole query*/
select
LD.ldProject, LD.ldLaborCode, LD.ldDate, LD.ldRegHrs, LD.ldOvtHrs,
EM.emEmployee, EM.emLastName, EM.emFirstName, EM.emJobCostRate, EM.emStatus, EM.emProfCtr, EM.emSelect1, EM.emSelect3, EM.emSelect16, EM.emSelect18, EM.emSelect20, EM.emSelect21, EM.emTerminationDate,
CFGPCControl.Label
INTO table3
FROM
{ oj (Advantage.dbo.LD LD INNER JOIN Advantage.dbo.EM EM ON
LD.ldEmployee = EM.emEmployee)
INNER JOIN Advantage.dbo.CFGPCControl CFGPCControl ON
EM.emProfCtr = CFGPCControl.ProfCtr}

WHERE
LD.ldDate >= 09-03-2005 AND LD.ldDate <= 09-10-2005 AND
EM.emStatus = 'A' AND
EM.emSelect1 = 'Salary' AND
EM.emSelect3 = 'Engineer/Scientist 1' OR
EM.emSelect3 = 'Engineer/Scientist 2' OR

EM.emSelect3 = 'Project Engin/Scien.' OR
EM.emSelect3 = 'Field Supervisor' OR
EM.emSelect3 = 'Party Chief' OR
EM.emSelect3 = 'Drafter' OR
EM.emSelect3 = 'Drafting/Graphics PM' OR
EM.emSelect3 = 'Technician' AND
EM.emProfCtr <> 'COCO' AND
EM.emProfCtr <> 'COOF' AND
EM.emProfCtr <> 'COPE' AND
EM.emProfCtr <> 'NOAD' AND
EM.emProfCtr <> 'NOFI' AND
EM.emProfCtr <> 'NOMK' AND
EM.emProfCtr <> 'NOHR' AND
EM.emProfCtr <> 'NOGP' AND
EM.emProfCtr <> 'NOSY' AND
EM.emProfCtr <> 'EAAD' AND
EM.emProfCtr <> 'WMAD' AND
EM.emprofCtr <> 'HOAD'

ORDER BY
EM.emProfCtr ASC,
EM.emEmployee ASC,
LD.ldDate ASC
GO

 

[gmmastros]

The problem here is that you are comparing the results of a sql query with a single value.

If (Select emStatus From EM) = "A" and (Select emSelect18 From EM) = 0

(Select emStatus From EM) must be returning multipl records. So SQL Server doesn't know which record you want compared, so it gives you an error.

If you run 'Select emStatus From EM' and you receive multiple records.

EmStatus
--------
A
B
C
D

Which record should you compare?

You sql statement would work if there was only 1 record in the EM table.

Depending on your situation, there are ways to handle it.


If (Select emStatus From EM [red]Where IdColumn=Value[/red]) = 'A'

This makes sure that the query can only return 1 record. If you want to check all the records to see if any of them contain the value "A"

If Exists(Select emStatus From EM Where emStatus = 'A')

Exists will return true if any records are returned, otherwise it returns false.

Also, you should not use double quotes in your stored procedures. Use the apostrophe instead.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vongrunt]

And - be careful with AND/OR stuff. Perhaps WHERE clause needs some ()'s?
And - make it shorter by using IN() and NOT IN().

And - what is exact purpose of { oj(...) }?

And - if EM has more than one row everything will fall apart.
Oops, George already said that

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[thminer]

thanks everyone. i will do some revising and see what happens. thanks for your help.