I am trying to compare records between two different SQL tables/databases in my Node project and have to do some transformation in order to compare the values.
In one database (MariaDB) the value is of type INT, and looks like this: 516542
In the other (SQL Server) database the value I need to match to is of type char(21), and looks like this: 00000516542-000
What I tried doing was this:
WHERE (REPLACE(LEFT( LM301.DOCNUMBR, CHARINDEX('-', LM301.DOCNUMBR)), '-', '')) = 516542
This works for some records, but for others I get this error:
“The conversion of the varchar value ‘0004000009123’ overflowed an int column.”
If I pass the first value in as a string (‘516542’) it doesn’t match at all.
How can I handle this scenario?
Advertisement
Answer
The error you’re getting is at least correct. But from your example i can’t determine whether the conversion is right or not.
Basically, somewhere in your CHAR(21). There a value which is greater than int32, or SQL Server int type, in value. This value is: 2,147,483,648. 4,000,009,123 is greater than this max value as specified by the error message.
The DBMS, with this where statement, will try to do the operation and compare to all records, and it runs into an overflow. You could do a string compare instead. Or try an explicit conversion and convert it to bigint.
WHERE CONVERT(BIGINT, (REPLACE(LEFT( LM301.DOCNUMBR, CHARINDEX('-', LM301.DOCNUMBR)), '-', ''))) = 516542
It’s doing an implicit cast to INT because that’s your compare type, then overflows. Making the conversion explicit allows you to determine the datatype instead.
Basically what’s happening:
IF ('21474836480' >= 100) --Implicit conversion: Error and prints false
PRINT 'True'
ELSE
PRINT 'False'
IF ('214748364' >= 100) --Implicit Conversion: True
PRINT 'True'
ELSE
PRINT 'False'
IF (CONVERT(BIGINT, '21474836480') >= 100) --Explicit Conversion: Prints True
PRINT 'True'
ELSE
PRINT 'False'
So wrapping your value in an explicit conversion should resolve your error.