SQL Problem Nested Select 1

[TimothyCederdahl]

I get this error when using "> dateadd(day,7,(select TRDate from SC_Trophies Where UserId = 29))" the full query is below with the error. Any ideas on how I could resolve this problem would be most welcomed. The query works fine it seems with out the date part.

Thanks in Advance,

Tim



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

insert into SC_Trophies (
UserId,
SportId,
TypeID,
Value,
TDID,
TrDate
)

Select
us.userid as UserID,
1 as SportId,
2 as TypeId,
0 as Value,
9 as TDID,
getdate() as TrDate
FROM SC_UserStats us
WHERE not exists (select * from SC_TROPHIES where UserID = us.UserID and SportId=1) and SportId=1 and
UserID in (SELECT Top 10 (UserID) FROM SC_UserStats Order By LWOUP)


begin
Update SC_Trophies
Set Value = Value + 1
where SC_TROPHIES.USERID in (SELECT Top 10 (UserID) FROM SC_UserStats where SportId=1 Order By LWOUP) and SportId=1 and TDID=9 and getdate() > dateadd(day,7,(select TRDate from SC_Trophies Where UserId = 29))
end

 

[PWise]

try

dateadd(day,7,(select max(TRDate) from SC_Trophies Where UserId = 29))

 

[monksnake]

This query is returning more than 1 record:

select TRDate from SC_Trophies Where UserId = 29

Which date(s) in particular do you want returned from this query??



<.

 

[TimothyCederdahl]

I get this error now.

Thanks,

Tim

(4 row(s) affected)
(0 row(s) affected)
(4 row(s) affected)
Msg 8115, Level 16, State 2, Procedure TrophyCase_CalcWeeklyTopTen, Line 60
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

(0 row(s) affected)

(0 row(s) affected)

(1 row(s) affected)

 

[TimothyCederdahl]

it just suppose to check to see if a week has gone by if so it needs to update the data in the table. Meaning the update only runs if a week has gone by.

Thanks,

Tim

 

[gmmastros]

The problem is here:

dateadd(day,7,(select TRDate from SC_Trophies Where UserId = 29))

Or more specifically:

select TRDate from SC_Trophies Where UserId = 29

(as monksnake pointed out) Depending on the data, that last query could return multiple records. You could try changing this too...

Select Max(TRDate) form SC_Traphies Where UserId = 29

so, the whole thing becomes....

[COLOR=blue]Update[/color] SC_Trophies
[COLOR=blue]Set[/color] [COLOR=blue]Value[/color] = [COLOR=blue]Value[/color] + 1
[COLOR=blue]where[/color] SC_TROPHIES.USERID in ([COLOR=blue]SELECT[/color] Top 10 (UserID) [COLOR=blue]FROM[/color] SC_UserStats [COLOR=blue]where[/color] SportId=1 [COLOR=blue]Order[/color] [COLOR=blue]By[/color] LWOUP) and SportId=1 and TDID=9 and [COLOR=#FF00FF]getdate[/color]() > [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color],7,([COLOR=blue]select[/color] [COLOR=#FF00FF]Max[/color](TRDate) [COLOR=blue]from[/color] SC_Trophies [COLOR=blue]Where[/color] UserId = 29))

-George

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

 

[TimothyCederdahl]

Even when i add the max i still get the same error.


(0 row(s) affected)
Msg 8115, Level 16, State 2, Line 23
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.


begin
Update SC_Trophies
Set Value = Value + 1
where SC_TROPHIES.USERID in (SELECT Top 10 (UserID) FROM SC_UserStats where SportId=1 Order By LWOUP) and SportId=1 and TDID=9 and getdate() > dateadd(day,7,(select Max(TRDate) from SC_Trophies Where UserId = 29))
end

 

[gmmastros]

Do this...

Select Data_Type
From   Information_Schema.Columns
Where  Table_Name = 'SC_Trophies'
       And Column_Name = 'TRDate'

I suspect the TRDate is a varchar and that you have some bad data somewhere. To find it...

Select *
From   SC_Trophies
Where  IsDate(TRDate) = 0

-George

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

 

[TimothyCederdahl]

You were right thanks my date in my table was set to nvar so it was causing an error during the comparison.