Solved

Varchar is invalid for subtract operator

Posted on 2009-05-18
6
1,308 Views
Last Modified: 2012-05-07
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
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415431
anywhere you use a LEFT() function, wrap a cast around it.

select cast(left() as int)
0
 

Author Comment

by:kdeutsch
ID: 24415492
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 24415563
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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:kdeutsch
ID: 24415597
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 400 total points
ID: 24415627
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
 

Author Closing Comment

by:kdeutsch
ID: 31582747
Thanks for the learning lesson.  Works great
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question