Solved

Varchar is invalid for subtract operator

Posted on 2009-05-18
6
1,288 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
  • 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now