Even the join clause of the cte selects only one row (the one from which could make the conversion to int), the server interrogates all of them.
The server should return ‘BRAVO’, but when trying to convert ‘ABC’ to int, obviously get conversion error.
I don’t understand this situation.

insert into A 
select 1,'25','ABC'
union
select 2,'99','50'
go

insert into B
select 1, 'ONE'
union
select 2, 'TWO'

;with dt as (
    select cast(CODEVALUE as int) VAL 
    from A 
    join B on B.CODEID=A.CODEID and B.CODENAME='TWO')

select case when VAL>25 then 'BRAVO' end VAL from dt

The same result with the where clause:

select cast(CODEVALUE as int) VAL 
from A, B
where B.CODENAME='TWO' and B.CODEID=A.CODEID