Solved

Varchar is invalid for subtract operator

Posted on 2009-05-18
6
1,324 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

627 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