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
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
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.