Change null values to string based on value in another column

Question

I want to replace NULL values in an integer column (pr_id) with text "N/A" if the value in a different column (type) = X. If the value in pr_id is not NULL, then the query should return the value in pr_id.

SELECT p_id,
       pr_id,
       type,
       CASE
         WHEN type = 'X' THEN COALESCE(CAST(pr_id AS VARCHAR(255)), 'N/A')
         ELSE pr_id
       END AS pr_id
FROM   a 

I'm getting the error: CASE types bigint and character varying cannot be matched

I thought that by using case + coalesce, I would be able to circumvent the mismatch between bigint and varchar, but doesn't seem to be working.

Answer 1

All branches of the case expression should return the same type (or at least, types that can be implicitly converted to each other). Here, the when branch returns a varchar but the else branch returns a bigit. You can solve this error by adding a cast to the else branch too:

CASE
  WHEN type = 'X' THEN COALESCE (CAST(pr_id AS VARCHAR(255)), 'N/A')
  ELSE CAST(pr_id AS VARCHAR(255) -- Here!
END AS pr_id