asked on

Varchar is invalid for subtract operator

I am running some code on a sharepoint DB, the Size field is an Integer field but when I try to do calcs on it I get the follwoing error.

Msg 8117, Level 16, State 1, Line 1
Operand data type varchar is invalid for subtract operator.

Select
(Select Left(((Sum([Size])*0.0009765625)*0.0009765625), 3) as 'Size combined in MB'
	FROM SharePoint_WSS_Content.dbo.AllDocs Where DirName not like '%/masterpage%'
		and DirName not like '%Reporting Templates%'
		and DirName not like '%/_themes%' 
		and DirName not like '%_catalogs%' 
		and DirName not like '%Style Library%'
		and DirName not like '%/forms%' 
		and DirName not like '%_cts%'
		and DirName not like '%FormServerTemplates%'  
		and DirName not like '%IWConvertedForms%'  
		and DirName not like '%/_t%'
		and DirName not like '%/_w%'
		and leafname not like '%.aspx'
		and leafname not like '%.xsl'
		and leafname not like '%.000'
		and Leafname <> 'Thumbs.db'
		and leafname like '%.%'
		and TimeCreated > convert(char(10), getdate(), 1) 
		and Len(DeleteTransactionId) = 0) - 
(Select Left(((Sum([Size])*0.0009765625)*0.0009765625), 3) as 'Size combined in MB'
FROM SharePoint_WSS_Content.dbo.AllDocs Where DirName not like '%/masterpage%'
		and DirName not like '%Reporting Templates%'
		and DirName not like '%/_themes%' 
		and DirName not like '%_catalogs%' 
		and DirName not like '%Style Library%'
		and DirName not like '%/forms%' 
		and DirName not like '%_cts%'
		and DirName not like '%FormServerTemplates%'  
		and DirName not like '%IWConvertedForms%'  
		and DirName not like '%/_t%'
		and DirName not like '%/_w%'
		and leafname not like '%.aspx'
		and leafname not like '%.xsl'
		and leafname not like '%.000'
		and Leafname <> 'Thumbs.db'
		and leafname like '%.%'
		and TimeLastModified > convert(char(10), getdate(), 1) 
		and Len(DeleteTransactionId) <> 0)

Open in new window

chapmandew

anywhere you use a LEFT() function, wrap a cast around it.

select cast(left() as int)

kdeutsch

ASKER

HI,
I tried this (Select cast(Left()as int(((Sum([Siz
but received these errors, do i have to wrap whole code in this??

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'Sum'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'Sum'.

chapmandew

it should look like this:

Select
CAST((
Select
Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
FROM
SharePoint_WSS_Content.dbo.AllDocs
Where
DirName not like '%/masterpage%'
and DirName not like '%Reporting Templates%'
and DirName not like '%/_themes%'
and DirName not like '%_catalogs%'
and DirName not like '%Style Library%'
and DirName not like '%/forms%'
and DirName not like '%_cts%'
and DirName not like '%FormServerTemplates%'
and DirName not like '%IWConvertedForms%'
and DirName not like '%/_t%'
and DirName not like '%/_w%'
and leafname not like '%.aspx'
and leafname not like '%.xsl'
and leafname not like '%.000'
and Leafname <> 'Thumbs.db'
and leafname like '%.%'
and TimeCreated > convert(char(10), getdate(), 1)
and Len(DeleteTransactionId) = 0
) AS INT)
- CAST((
Select
Left(((Sum([Size]) * 0.0009765625) * 0.0009765625), 3) as 'Size combined in MB'
FROM
SharePoint_WSS_Content.dbo.AllDocs
Where
DirName not like '%/masterpage%'
and DirName not like '%Reporting Templates%'
and DirName not like '%/_themes%'
and DirName not like '%_catalogs%'
and DirName not like '%Style Library%'
and DirName not like '%/forms%'
and DirName not like '%_cts%'
and DirName not like '%FormServerTemplates%'
and DirName not like '%IWConvertedForms%'
and DirName not like '%/_t%'
and DirName not like '%/_w%'
and leafname not like '%.aspx'
and leafname not like '%.xsl'
and leafname not like '%.000'
and Leafname <> 'Thumbs.db'
and leafname like '%.%'
and TimeLastModified > convert(char(10), getdate(), 1)
and Len(DeleteTransactionId) <> 0
) AS INT)

kdeutsch

ASKER

Ok, its working somewhat.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '31.' to data type int.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kdeutsch

ASKER

Thanks for the learning lesson. Works great