SQLTeam.com | Weblogs | Forums

Error converting data type varchar to bigint

Hello everyone,
I'm hoping someone has faced this issue before. I'm getting an "Error converting data type varchar to bigint". When I look at the columns I'm pulling from both tables, the data type within both tables is varchar. the only difference I've found within both tables is one has the columns as NOT NULL, and the other has them as NULL. Here's the code snippet causing the error:

select b.*,wt.[PERNR] fromPernr, wt.[NTLOGIN] fromNTLogin, wt.[ROLE] fromjobfunction, wt2.[PERNR] ToPernr, wt2.[NTLOGIN] ToNTLogin, wt2.[ROLE] ToRole

into #tmp4o

from #tmp3o b inner join [ExternalUser].[iCoMP].[t_WSTCBSTransactionData] wt(nolock)
on b.fromresacctno = wt.[ACCOUNT_NUMBER]
inner join [ExternalUser].[iCoMP].[t_WSTCBSTransactionData] wt2(nolock)
on b.toresacctno = wt2.[ACCOUNT_NUMBER]

select * from #tmp4o

Thank you very much for any and all help!

For helpful responses include create table scripts and inserts with sample data.

Both columns can't be varchar or SQL wouldn't convert it to bigint. My best guess is that ACCOUNT_NUMBER is bigint but from/to resacctno are varchar. If so, you'll have to test the resacctno columns before your join on them, or cast the ACCOUNT_NUMBER to varchar, which could hurt performance, perhaps a lot.

select ...same...
into ...same...
from ...same...
--add this part
where b.fromresacctno not like '%[^0-9]%' and b.toresacctno not like '%[^0-9]%'

If you still get the error, that means a value is a resacctno is too large for bigint -- why, that would be unusual.