I have a WHERE condition which works for the type STRING but fails for an of type INT
isnull(emp.name, 'x') <> isnull(mst.name, 'x') -- works
But this condition below throws an error:
isnull(emp.age, 'x') <> isnull(mst.age, 'x') -- fails
Conversion failed when converting the varchar value 'x' to data type int.
where name is a STRING and age is of INT type.
How to rectify this?
Don't use isnull() for this purpose -- or even coalesce(). Just expand out the logic:
where (emp.age = mst.age or emp.age is null and mst.age is null)
You could put in a fake value and use coalesce(), but the types need to be consistent. However, I think you are better off with explicit logic that does what you want and works for all data types.
ISNULL will attempt to convert the datatype of second parameter to that of first parameter. The string x cannot be converted to int (assuming age is int). Use an integer value that, ideally, does not exist in your data:
isnull(emp.age, -1) <> isnull(mst.age, -1)
Note that in your code, 'x' will effectively be considered the same as NULL, which is unlikely to be what you want. Fixing the error you get for age will not correct that problem.
Consider instead using the operator IS DISTINCT FROM, which is similar to <> but considers NULL as a "known value" (NULL IS DISTINCT FROM NULL = FALSE for example).
emp.name IS DISTINCT FROM mst.name
emp.age IS DISTINCT FROM mst.age
If your database engine does not support IS DISTINCT FROM then this related question will be helpful: How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM?
