How do I replace NULL values with string using COALESCE function?-CodePudding

Trying to put 'unassigned' to the null values using coalesce function within the select statement. Here is the code:

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']

CodePudding user response：

You need to make sure you don't mix data types

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

CodePudding user response：

As the error mentions, you are mixing data types. Inside the coalesce function you need to convert sales_rep_id to a varchar first.

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