Error casting varchar to decimal even though SQL query doesn’t return any results

I have a SQL query which fetches no rows. I am using cast function in select statement to cast from varchar to decimal, I am getting exception saying couldn’t cast varchar to decimal.

This is my query

select distinct 
    order_id, order_amt, 
    cast(replace(order_amt, ',', '') as decimal(11, 2)) as amt 
from 
    table1 
left join 
    table2 on table1.order_id = table2.order_num
where 
    table2.order_num is null
    and table1.order_id not in (123, 456)

Not sure why I am getting error, even though query returns no results.

Any inputs are highly appreciated.

You don’t have control over where SQL Server decides to evaluate expressions. So, expressions might be evaluated (and generate an error), even on rows that are filtered out.

In this case, there is a very simply solution: try_cast():

Try_Cast(Replace(order_amt, ',', '') as decimal(11,2)) as amt