我如何使用COALESCE函数用字符串替换NULL值?

Question

尝试在select语句中使用coalesce函数将'unassigned'设置为空值。下面是代码:

SELECT ORDER_ID, ORDER_DATE,
coalesce (SALES_REP_ID,'NA') REP
FROM ORDERS```
[But having error "inconsistent datatype"]
How do I fix it?
[Data, in which sales_rep_id have null values which i want to change it to 'unassigned']

Answer 1

错误提到,你是混合数据类型。在coalesce函数中，您需要首先将sales_rep_id转换为varchar。

SELECT ORDER_ID, ORDER_DATE,
coalesce (to_char(SALES_REP_ID), 'NA') REP
FROM ORDERS

Answer 2

你需要确保你没有混合数据类型

SELECT ORDER_ID, ORDER_DATE,
case when SALES_REP_ID is null 
then 'NA'
else to_char(SALES_REP_ID)
end REP
FROM ORDERS