Run SQL Query With Len() In Join Condition

Question

I am trying to run a sql query but I am getting the error

Conversion failed when converting the varchar value 'Sample-XYZ' to data type int

I am not certain why the query is trying to convert to int as it should be varchar

This is sample DDL

Create Table Sample ( oli varchar(100), qs int )
Create Table Sample1 ( onum varchar(100), sd datetime2 )

Insert Into Sample (oli, qs) Values
('Sample-XYZ-3', 22), ('Sample-CCC-1', 11), ('Test-ABC-34', 10)

Insert Into Sample1 (onum, sd) Values
('Sample-XYZ', '2020-04-03 14:17:33.8530000'), 
('Sample-CCC', '2020-04-03 14:17:33.8530000'),
('Test-ABC', '2020-04-03 14:17:33.8530000');

Select
CAST(left(oli, len(oli) - charindex('-', reverse(oli) + '-')) As INT) As oli
,SUM(COALESCE(qs,0)) As qs
FROM Sample sl
INNER JOIN Sample1 sp
ON len(sl.oli) - charindex('-', reverse(sl.oli) + '-') = sp.onum
GROUP BY sl.oli

What must I change so that the query executes succesfully?

Answer 1

I don't know why you are trying to cast to an int in the first place, maybe your real life problem is more confusing than this. But removing the cast simply works:

SELECT      LEFT(sl.oli, LEN(sl.oli) - CHARINDEX('-', REVERSE(sl.oli) + '-')) AS oli
,           SUM(COALESCE(sl.qs, 0)) AS qs
FROM        Sample sl
INNER JOIN  Sample1 sp
            ON LEFT(sl.oli, LEN(sl.oli) - CHARINDEX('-', REVERSE(sl.oli) + '-')) = sp.onum
GROUP BY    sl.oli


+------------+----+
|    oli     | qs |
+------------+----+
| Sample-CCC | 11 |
| Sample-XYZ | 22 |
| Test-ABC   | 10 |
+------------+----+