英文:
Change null values to string based on value in another column
问题
Here's the translated portion:
我想要在整数列（pr_id）中，当不同列（type）的值等于X时，将NULL值替换为文本“N/A”。如果pr_id中的值不是NULL，则查询应返回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
您遇到的错误是：
CASE类型bigint和character varying无法匹配
我认为通过使用case + coalesce，我可以规避bigint和varchar之间的不匹配，但似乎不起作用。
英文:
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.
答案1
得分: 2
所有case表达式的分支应该返回相同的类型（或者至少是可以隐式转换为彼此的类型）。在这里，when分支返回一个varchar，但是else分支返回一个bigit。你可以通过在else分支也加上一个cast来解决这个错误：
CASE
WHEN type = 'X' THEN COALESCE(CAST(pr_id AS VARCHAR(255)), 'N/A')
ELSE CAST(pr_id AS VARCHAR(255)) -- 这里！
END AS pr_id
英文:
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
答案2
得分: 2
我已经翻译了描述的文字:
情况
当类型 = 'X' 且 pr_id 为空时 THEN 'N/A'
否则将 pr_id 转换为 VARCHAR(20) 格式
END AS pr_id_str
英文:
I have translated the description literally:
CASE
WHEN type = 'X' AND pr_id IS NULL THEN 'N/A'
ELSE CAST(pr_id AS VARCHAR(20))
END AS pr_id_str
The important thing here is that all branches of CASE expressions need to return same data type so the bigint needs to be converted to string data type.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论