problem running query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem running query

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-30 : 02:50:01

update att
set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60)-FLOOR(((m.first_in-FLOOR(m.first_in))*60)))*60),2),EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60)-FLOOR(((m.first_out-FLOOR(m.first_out))*60)))*60),2) from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID
else if(select Weekdayname from AttendanceResult)in ('Thursday')
update att
set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)-FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)))*60),2),EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)-FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)))*60),2) from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID
else
update att
set EmpSIN='00:00:00',EmpSOut='00:00:00' from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(248 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2010-10-30 : 02:56:26

"Subquery returned more than 1 value"

is the message and your only sub query is:

"select Weekdayname from AttendanceResult"

so that is presumably returning more than one row? (which is not permitted)

Did you mean this:

else if EXISTS (select * from AttendanceResult WHERE Weekdayname in ('Thursday'))

perhaps?

pnasz
Posting Yak Master

101 Posts

Posted - 2010-10-30 : 03:04:09

I have a table which has timing for employees normal days and thursday.

from this table i want to update main table which has a coloumn weekdays
so i am checking if week day is =normalday i am updating timein and timeout for normal day
if it is thursday i am updating time for thursday
and when it is friday i am updating it with 00:00:00

this query is not working as it is updating all records by 00:00:00

and this error
can u help me still i am confuse with error

if(select Weekdayname from AttendanceResult)in ('Saturday','Sunday','Monday','Tuesday','Wednesday')
update att
set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in-FLOOR(m.first_in))*60)-FLOOR(((m.first_in-FLOOR(m.first_in))*60)))*60),2),EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out-FLOOR(m.first_out))*60)-FLOOR(((m.first_out-FLOOR(m.first_out))*60)))*60),2) from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID
else if(select Weekdayname from AttendanceResult)in ('Thursday')
update att
set EmpSIN=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_in1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)-FLOOR(((m.first_in1-FLOOR(m.first_in1))*60)))*60),2),EmpSOut=RIGHT('00' + CONVERT(varchar(2),FLOOR(m.first_out1)),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60))),2) + ':' + RIGHT('00' + CONVERT(varchar(2),FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)-FLOOR(((m.first_out1-FLOOR(m.first_out1))*60)))*60),2) from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID
else
update att
set EmpSIN='00:00:00',EmpSOut='00:00:00' from EmpTimeSchedule$ as m inner join AttendanceResult as att
on m.empno=att.EmpID

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Msg 512, Level 16, State 1, Line 5
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(248 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2010-10-30 : 03:40:34

"Subquery returned more than 1 value"

is the message and your have the same sub query as above, which you have not fixed, plus also this additional one:

"f(select Weekdayname from AttendanceResult)in ('Saturday','Sunday','Monday','Tuesday','Wednesday')"

which may be returning more than one row (which is not permitted), and which you can fix the same way as I recommended above

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-01 : 07:31:20

How are you relating the two tables?

Madhivanan

Failing to plan is Planning to fail

shaggy
Posting Yak Master

248 Posts

Posted - 2010-11-01 : 07:43:51

instead if this if(select Weekdayname from AttendanceResult)in ('Saturday','Sunday','Monday','Tuesday','Wednesday')

use below

if exists (select 1 from AttendanceResult where Weekdayname in ('Saturday','Sunday','Monday','Tuesday','Wednesday'))
begin

end
else
begin

end

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-11-01 : 08:24:04

Something like the following should be more readable:


UPDATE att
SET EmpSIN =
	CASE
		WHEN att.Weekdayname = 'Thursday' THEN CONVERT(char(8), DATEADD(second, m.first_in1 * 3600, 0), 108)
		WHEN att.Weekdayname = 'Friday' THEN '00:00:00'
		ELSE CONVERT(char(8), DATEADD(second, m.first_in * 3600, 0), 108)
	END
	,EmpSOut = 
	CASE
		WHEN att.Weekdayname = 'Thursday' THEN CONVERT(char(8), DATEADD(second, m.first_out1 * 3600, 0), 108)
		WHEN att.Weekdayname = 'Friday' THEN '00:00:00'
		ELSE CONVERT(char(8), DATEADD(second, m.first_out * 3600, 0), 108)
	END
FROM EmpTimeSchedule$ AS m
	JOIN AttendanceResult AS att
		ON m.empno = att.EmpID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources