• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1430
  • Last Modified:

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

0
kdeutsch
Asked:
kdeutsch
  • 3
  • 3
1 Solution
 
chapmandewCommented:
anywhere you use a LEFT() function, wrap a cast around it.

select cast(left() as int)
0
 
kdeutschAuthor Commented:
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'.
0
 
chapmandewCommented:
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)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
kdeutschAuthor Commented:
Ok, its working somewhat.

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '31.' to data type int.
0
 
chapmandewCommented:
it has a period....you'll have to wrap a replace around it like I did the cast

Select
      CAST(
            REPLACE(
            (
       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(
            REPLACE(
            (
         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)
0
 
kdeutschAuthor Commented:
Thanks for the learning lesson.  Works great
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now