Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error turning Varchar into Numeric Expand / Collapse
Author
Message
Posted Tuesday, December 7, 2010 5:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 6:19 AM
Points: 1, Visits: 3
Hi All. I am newish to SQL and I have seen a few similar posts but am still struggling.

I am basically trying to convert a varchar variable into a number so that I can extract it. It works when I do 1 sector but then when I add another it comes up with the error "Error converting data type varchar to bigint." or "Error converting data type varchar to numeric".

here are 2 ways I have run the code; (I left out the froms and joins)

select BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, cast(QR_FreeText as bigint) as Turnover

where isnumeric(QR_FreeText)=1

and ((BSU_MarketSector='C') and cast(QR_FreeText as bigint)>100000000)
or ((BSU_MarketSector='D') and cast(QR_FreeText as bigint)>50000000)
--or ((BSU_MarketSector='M') and cast(QR_FreeText as bigint)>1000000000)
--or ((BSU_MarketSector='R') and cast(QR_FreeText as bigint)>75000000)

group by BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, cast(QR_FreeText as bigint )


The other way

declare @turnover2 as numeric (30)

set @turnover2='QR_FreeText'

select BSU_BranchCode, BSU_CompanyName, BSU_MarketSector, isnumeric(@turnover2)


where
((BSU_MarketSector='C') and cast(@turnover2 as numeric)>100000000)
--or ((BSU_MarketSector='D') and cast(QR_FreeText as bigint)>50000000)
--or ((BSU_MarketSector='M') and cast(QR_FreeText as bigint)>1000000000)

group by BSU_BranchCode, BSU_CompanyName, BSU_MarketSector

Any suggestions?

Thanks.
Post #1031145
Posted Tuesday, December 7, 2010 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
Hi James

Firstly, ISNUMERIC() has limitations - it returns true (1) if the value can be converted into a number. SSC has a recent article covering its limitations.
Secondly, the filters in the expression may be evaluated by SQL Server in any order, not necessarily the logical order which you've carefully calculated should work.
Thirdly, a few rows of column QR_FreeText should help folks determine the best way to solve this problem. A few rows which should convert, and a few rows which should not.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1031149
Posted Tuesday, December 7, 2010 5:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:27 PM
Points: 7,164, Visits: 13,257
I'm expecting some non-numeric values in [QR_FreeText] for [BSU_MarketSector]='M'.
It seems like query optimizer decides to filter on [BSU_MarketSector] [QR_FreeText] first, therewith ignoring the invalid column values of [QR_FreeText].

The article Chris mentioned can be found here.

Use the method described in the article above to find the values that cannot be converted into bigint.




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #1031154
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse