SQLite Forum

Using coalesce in where clause
I was trying to do something like
a. is base data, b are updates

select coalesce(b.col1, a.col1), coalesce (b.col1, a.col1) as var, a.col3
from a
left join b
on coalsece (a.col, 'X') = coalesce (b.col1, 'X') -- could be null
and a.col3 = b.col3

-- up to here thare are no problems and the results are correct and I have many rows with var = 7

where coalesce(b.col1, a.col1) = 7

-- I get no results whatever

The question is:  Can I use coalesce in the where clause or whatI an doing is too confusing to SQLite.  I yhink that this would work on SQL Server