SQL Server - Error converting data type varchar to bigint

0
votes

I am not sure what I am doing wrong but I am getting the following error in a subquery

Select*
from
(select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where [AXOBJ] >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%')a
where a.[Account]>=510980

It returns Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.

I ran following to capture the data type of this table

select * from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='y'

| COLUMN_NAME | DATA_TYPE |
|-------------|-----------|
| AXOBJ       | char      |
| AX$AC1      | char      |

The following code return 39 integer values

select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where [AXOBJ] >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%' GROUP BY AX$AC1

I am not sure where it going wrong.

2
sql-server
Without sample data we can but guess. Replace CONVERT (BIGINT,AX$AC1) with TRY_CONVERT(BIGINT,AX$AC1). I bet you get some NULL values. But why are you storing numerical data as a varchar anyway? A value like '1.0' cannot be converted to a bigint. - Larnu
AXOBJ & AX$AC1 are of type char, what is the length of that column ? - Sats
The simple answer is to know your datatypes, understand the rules for implicit conversion, and understand how to write / use literals of a specific datatype. - SMor
@Larnu many thanks. Yes, TRY_CONVERT(BIGINT,AX$AC1) returns bunch of null values. I modified my query as where [AXOBJ] >399999 AND [AX$AC1] NOT like '%NON BENCHMARK% AND TRY_CONVERT(BIGINT, AX$AC1)>=510980. It is giving me what I need. But why are you storing numerical data as a varchar anyway? _ I am not the DBA. I am just accessing the view. Thanks a lot again. - smpa01

2 Answers

0
votes

I think the error is in your where condition where [AXOBJ] >399999, also the last where condition where a.[Account]>=510980.

Try the below code

Select*
from
(select 

CONVERT (BIGINT,AX$AC1) as [Account]
from 
[x].[y]
where CONVERT(bigint,[AXOBJ]) >399999 AND [AX$AC1] NOT like '%NON BENCHMARK%')a
where CONVERT(bigint,a.[Account]) >= 510980
0
votes

You can use try_convert() :

select [AX$AC1], [Account]
from [x].[y] cross apply
     ( values (try_convert(BIGINT, AX$AC1), try_convert(BIGINT, [AXOBJ])
              ) 
     ) a([Account])
where a.[AXOBJ] > 399999 AND 
      [y].[AX$AC1] NOT like '%NON BENCHMARK%' and
      a.[Account] > 510980;

You will find null values where conversation will fail by removing [Account] > 510980.