December 7, 2010 at 5:08 am
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.
December 7, 2010 at 5:27 am
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.
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
December 7, 2010 at 5:40 am
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply