Error with casting

Question

I have a query where I have to extract some number fields from varchar column. When i do some replace and substring in select statement and cast it to bigint everything works fine, but when I use this same cast in join it throws error 'Error converting data type varchar to bigint'. How is this possible?

select CAST(
        case when CHARINDEX('/',f.BML,1)>0 
            then substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') ,1,CHARINDEX('/',BML,1)-1)
        else replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') 
        end as bigint)
from TableN n join TableO o  on
    n.Id=o.Id 
    join TableF f on
        f.OId=o.OId and
        substring(cast(n.RJ as varchar(10)),1,3)=substring(CAST(f.MT AS varchar(10)),1,3) and
    CAST(
        case when CHARINDEX('/',f.BML,1)>0 then substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') ,1,CHARINDEX('/',f.BML,1)-1)
        else replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') 
        end as bigint) =n.mbr
order by n.Ident

Answer 1

As I indicated in my comment, there's no guarantee on the order in which conditions are assessed. So if you have:

    f.OId=o.OId and
    substring(cast(n.RJ as varchar(10)),1,3)=substring(CAST(f.MT AS varchar(10)),1,3) and
CAST(
    case when CHARINDEX('/',f.BML,1)>0 then substring(replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') ,1,CHARINDEX('/',f.BML,1)-1)
    else replace(replace(replace(replace(replace(replace(replace(replace(replace(f.BML,'A',''),'B',''),'C',''),'+',''),',',''),'S',''),'H',''),'P',''),'¸','') 
    end as bigint) =n.mbr

And either f.OId=o.OId or substring(cast(n.RJ as varchar(10)),1,3)=substring(CAST(f.MT AS varchar(10)),1,3) should eliminate rows with values of BML that are not convertible to bigint, that's no guarantee that the conversion will not be attempted.

---

You can attempt to move the filters that should eliminate bad BML values into a subquery or CTE, but that's still no guarantee that the query optimizer won't push the conversion operator down into the subquery and still cause an error.

The only real way to deal with this (unfortunately) is to split the query into two pieces, eliminating unconvertible values in the first query, and placing the result of this query in a temp table/table variable. Then build the second half of the query using this temp table.