Matching on Values, but Erroring on New Value in SQL Server

I am comparing data from two different databases (one MariaDB and one SQL Server) within my Node project, and am then doing inserts and updates as necessary depending on the comparison results.

I have a question about this code that I use to iterate through results in Node, going one at a time and passing in values to check against (note – I am more familiar with Node and JS than with SQL, hence this question):

SELECT TOP 1
    CASE 
       WHEN RM00101.CUSTCLAS LIKE ('%CUSR%') 
          THEN CAST(REPLACE(LEFT(LR301.DOCNUMBR, CHARINDEX('-', LR301.DOCNUMBR)), '-', '') AS INT) 
          ELSE 0 
    END AS Id,
    CASE 
       WHEN LR301.RMDTYPAL = 7 THEN LR301.ORTRXAMT * -1
       WHEN LR301.RMDTYPAL = 9 THEN LR301.ORTRXAMT * -1
       ELSE LR301.ORTRXAMT
    END DocumentAmount,
    GETDATE() VerifyDate
FROM
    CRDB..RM20101
INNER JOIN 
    CRDB..RM00101 ON LR301.CUSTNMBR = RM00101.CUSTNMBR
WHERE 
    CONVERT(BIGINT, (REPLACE(LEFT(LR301.DOCNUMBR, CHARINDEX('-', LR301.DOCNUMBR)), '-', ''))) = 589091

Currently, the above works for me for finding records that match. However, if I enter a value that doesn’t yet exist – in this line below, like so:

WHERE CONVERT(BIGINT, (REPLACE(LEFT( LR301.DOCNUMBR, CHARINDEX('-', LR301.DOCNUMBR)), '-', ''))) = 789091

I get this error:

Error converting data type varchar to bigint.

I assume the issue is that, if the value isn’t found, it can’t cast it to an INTEGER, and so it errors out. Sound right?

What I ideally want is for the query to execute successfully, but just return 0 results when a match is not found. In JavaScript I might doing something like an OR clause to handle this:

const array = returnResults || [];

But I’m not sure how to handle this with SQL.

By the way, the value in SQL Server that’s being matched is of type char(21), and the values look like this: 00000516542-000. The value in MariaDB is of type INT.

So two questions:

Will this error out when I enter a value that doesn’t currently match?
If so, how can I handle this so as to just return 0 rows when a match isn’t found?

By the way, as an added note, someone suggested using TRY_CONVERT, but while this works in SQL Server, it doesn’t work when I use it with the NODE mssql package.

Answer

I think the issue is happening because the varchar value is not always made of numbers. You can make the comparison in varchar format itself to avoid this issue:

WHERE (REPLACE(LEFT( LR301.DOCNUMBR, CHARINDEX('-', LR301.DOCNUMBR)), '-', '')) = '789091'

Hope this helps.

Edit: based on the format in the comment, this should do the trick;

WHERE REPLACE(LTRIM(REPLACE(REPLACE(LEFT( LR301.DOCNUMBR, CHARINDEX('-', LR301.DOCNUMBR)),'0',' '),'-','')),' ','0') = '789091'

Advertisement

Answer