Error: Invalid operator for data type

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

Error: Invalid operator for data type

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-09 : 13:03:50

this is an error i am getting trying to run the query below.
Invalid operator for data type. Operator equals minus, type equals varchar

SELECT regardingobjectidname, actualend, owneridname, createdbyname, activitytypecodename
FROM (SELECT regardingobjectidname, actualend, Owneridname, createdbyname, activitytypecodename, row_number() OVER (Partition BY
regardingobjectid
ORDER BY actualend DESC) AS recid
FROM FilteredActivityPointer
WHERE statecodename = 'completed') AS d
WHERE recid = 1 AND (owneridname IN (@user)) AND (activitytypecodename = 'phone call' OR
activitytypecodename = 'e-mail' OR
activitytypecodename = 'fax') AND (actualend > dateadd(d, -'
+ CONVERT(nVarChar(20), @NeglectedDays) + ',GetUTCDate()
ORDER BY actualend

Compnetsyslc

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-09 : 13:05:28

your dateadd has some weird syntax

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 13:34:27

Not taken into account his previous postings. It looks like dynamic sql...

SET @SQL = '
SELECT		regardingobjectidname,
		actualend,
		owneridname,
		createdbyname,
		activitytypecodename
FROM		(
			SELECT	regardingobjectidname,
				actualend,
				Owneridname,
				createdbyname,
				activitytypecodename,
				row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid
			FROM	FilteredActivityPointer
			WHERE	statecodename = ''completed''
				AND owneridname in (' + @user + ')
				AND activitytypecodename IN (''phone call'', ''e-mail'', ''fax'') 
				AND actualend >= dateadd(d, - ' + CONVERT(NVARCHAR(20), @NeglectedDays) + ', GetUTCDate())
		) AS d
WHERE		recid = 1
ORDER BY	actualend'

PRINT @SQL

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 13:39:50

And just in case it is not dynamic sql

SELECT		regardingobjectidname,
		actualend,
		owneridname,
		createdbyname,
		activitytypecodename
FROM		(
			SELECT	regardingobjectidname,
				actualend,
				Owneridname,
				createdbyname,
				activitytypecodename,
				row_number() OVER (Partition BY regardingobjectid ORDER BY actualend DESC) AS recid
			FROM	FilteredActivityPointer
			WHERE	statecodename = 'completed'
				AND owneridname = @user
				AND activitytypecodename IN ('phone call', 'e-mail', 'fax') 
				AND actualend >= dateadd(d, - CAST(@NeglectedDays AS INT), GetUTCDate())
		) AS d
WHERE		recid = 1
ORDER BY	actualend

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-09 : 13:58:00

You are too good. wow. how did you come up with these things. i have just had to force to learn some SQL so i dont know much but you are good. Once again Thank you Much

Compnetsyslc

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-09 : 14:52:34

One more question. If i were to place a count(*) in there. where would i place it?

Compnetsyslc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-09 : 14:58:40

In the select list.
Don't forget to group by the other columns.

E 12°55'05.25"
N 56°04'39.16"

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-09 : 15:25:28

Thank you . Once again you did it

Compnetsyslc

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-09 : 15:55:51

Okay one more question:
i need to join this table with another table called
filteredcontact. and extract data from this table called new_ratingname.
Please help

Compnetsyslc

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-11-14 : 13:57:19

This is what finally worked after i tweaked it a bit

SELECT a.regardingobjectidname, a_1.regardingobjectidname AS Expr1, a.activitytypecodename, a_1.[Last Contact Date], a_1.new_ratingname,
a_1.owneridname
FROM FilteredActivityPointer AS a INNER JOIN
(SELECT MAX(FilteredActivityPointer.actualend) AS [Last Contact Date], FilteredActivityPointer.regardingobjectidname,
FilteredContact.new_ratingname, FilteredContact.owneridname
FROM FilteredActivityPointer INNER JOIN
FilteredContact ON FilteredContact.contactid = FilteredActivityPointer.regardingobjectid
WHERE (FilteredActivityPointer.statecodename = 'completed') AND (FilteredActivityPointer.activitytypecode IN ('4202', '4210')) AND
(FilteredContact.new_ratingname NOT IN ('dead', 'archive', 'continous updates', 'owner', 'reservation holder', 'new')) AND
(FilteredActivityPointer.createdbyname NOT IN ('melvin felicien', 'suzette collymore', 'gasper george', 'suzanne gryspeerdt'))
GROUP BY FilteredActivityPointer.regardingobjectidname, FilteredContact.owneridname, FilteredContact.new_ratingname) AS a_1 ON
a.regardingobjectidname = a_1.regardingobjectidname AND a.actualend = a_1.[Last Contact Date]
WHERE (a_1.owneridname IN (@user)) AND (a_1.[Last Contact Date] <= DATEADD(d, - CAST(@NeglectedDays AS INT), GetUTCDate()))
ORDER BY a.owneridname, a.regardingobjectidname

Compnetsyslc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources