Help with converting varchare value error

Transact-SQL
1

Have a simple query to provide lists of security id's based on which type of sec id that is returned (cusip, ticker etc). The application that I need to feed the report into needs each return to include the type of security id it's reading in so for example an ISIN would return something like "USIBMXXXXXXX ISIN". For cusips we have some that are bank loans which begin with BL but application only reads the first 8 out of 9 characters (alphanumeric) and then add CORP to instead of CUSIP to these. I'm using a case statement for this, but get this error, "Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' CORP' to data type int."
Here is my sql and I'm using MS SQL Sever Mgmt Studio.

select distinct (s.UDF_CHAR6 + ' BUID') 'BB_Unique_ID', (s.ISIN_NO + ' ISIN') 'ISIN',
case when convert(varchar,s.cusip) like 'BL%' then LEFT (s.cusip,8 + ' CORP') else (s.CUSIP + ' CUSIP') end 'CUSIP',
(s.SEDOL + ' SEDOL') 'SEDOL', s.TICKER
from CS_POSITION p join CSM_SECURITY s on p.SEC_ID = s.SEC_ID
where p.QTY_SOD <> 0

Thanks..any help would be appreciated.

2 
SELECT DISTINCT 
	s.UDF_CHAR6 + ' BUID' AS "BB_Unique_ID" ,
	s.ISIN_NO + ' ISIN' AS "ISIN" ,
	CASE WHEN s.cusip LIKE 'BL%' THEN
		LEFT(s.cusip, 8)  + ' CORP'
	ELSE
		s.CUSIP + ' CUSIP'
    END AS "CUSIP" ,
    s.SEDOL + ' SEDOL' AS "SEDOL" ,
    s.TICKER
FROM
	CS_POSITION p
    JOIN CSM_SECURITY s ON p.SEC_ID = s.SEC_ID
WHERE  
	p.QTY_SOD <> 0;
1 Like
3

Thank you..it looks like you simplified it a great deal and this runs perfectly.