Error converting data type varchar to numeric

Hi, I'm writing SQL to be used in workflow monitor for our trading platform application. It will be used to identify bank loans in our bank loan tranche accounts that do not have a corresponding bank loan in our regular account. There are two parts to the monitor. One displays the number of exceptions (count) and the other part displays the exception bank loan id's. The count is working fine, but for the display portion of the query I'm getting an "Error converting data type varchar to numeric' error. I believe the error is coming from the return value which is a varchar value which would be something like 'AB1234567'. It runs fine in MS SQL Server (2008) but I get the error when selecting the query in the application. Here is the SQL:
select distinct a.ext_sec_id_root

from

(select p.ACCT_CD, left(s.ext_sec_id,9) ext_sec_id_root, s.EXT_SEC_ID, s.sec_name, p.QTY_SOD, p.MKT_VAL_SOD
from csm_security s join cs_position p on s.SEC_ID = p.SEC_ID
where s.SEC_TYP_CD = 'TERM'
and p.ACCT_CD like '%_BL') A

left outer join

(select p.ACCT_CD, left(s.ext_sec_id,9) ext_sec_id_root, s.EXT_SEC_ID, s.sec_name, p.QTY_SOD, p.MKT_VAL_SOD
from csm_security s join cs_position p on s.SEC_ID = p.SEC_ID
where s.SEC_TYP_CD = 'TERM'
and p.ACCT_CD not like '%_BL') B on a.ext_sec_id_root = b.ext_sec_id

where b.EXT_SEC_ID is NULL

Any help would be appreciated. Thanks.

Usually this error appears when you try to convert a string that cannot be converted to a number. For example, if you were to convert AB1234567 to an integer, you would get this error.

The conversion may not be explicit. Examples below demonstrate errors in implicit conversion.

CREATE TABLE #tmp( col1 VARCHAR(32));

INSERT INTO #tmp VALUES ('1'), ('2'), ('3'), ('a');

-- instead, if you were to use this insert statement, it will show error because of implicit conversion
--INSERT INTO #tmp VALUES (1), (2), (3), ('a');

SELECT * FROM #tmp WHERE col1 = '2'

-- instead if you were to use this select statement, it will show error because of implicit conversion
SELECT * FROM #tmp WHERE col1 = 2

I can't see anywhere in the code where that looks like its happening.

s.SEC_ID = p.SEC_ID

presumably those two columns are the same datatype (well .. they've got the same name so Here's Hoping!!)

s.SEC_TYP_CD = 'TERM'
p.ACCT_CD like '%_BL'

Those are going to fail every time if the column is a numeric data type

on a.ext_sec_id_root = b.ext_sec_id

Looks to me that [ext_sec_id_root] on A and [ext_sec_id_root] on B are using an identical formula:

left(s.ext_sec_id,9)

so can't see that comparing them could cause a different type error.

So by my reckoning there is some additional code, not posted here, and it is that which is triggering the error which you so clearly :slight_smile: described

1 Like

Thanks for taking a look Kristen..Like I explained it runs fine in MS SQL Server, so the additional code that you suspect must be coming from the interface I'm attempting to add it in and I suspect you're correct about that. I'll see what I can do within the app and see if I can get it to work.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

The O/P is tagged as SQL_2008 - if that is the version being used then TRY_CONVERT will not be available :frowning: