Extract a value from another table if column is null in Oracle

Question

I have a table (table1) in below format

FN               DESC_TXT       ST_CD
123              Text1             Q
124              (null)            Q
125              Text3             Q
126              Text4             P
127              (null)            Q

I wanted to extract the records when ST_CD='Q' and then in case if the DESC_TXT is (null) - i need to check for another table (table2) and get the Desc.Another Table Structure is

FN     C_DESC_TXT       
124     Text2
127     Text5

So My output would be

FN               DESCRIPTION       
123              Text1             
124              Text2          
125              Text3         
127              Text5

My code below is:

SELECT T1.FN, COALESCE(T1.DESC_TXT, T2.C_DESC_TXT) AS DESCRIPTION
FROM table T1
LEFT JOIN table2 T2
ON T1.FN = T2.FN
where T1.ST_CD= 'Q'

However i get error - ORA-00932: inconsistent datatypes: expected NCHAR got NCLOB

as my DESC_TXT is NVARCHAR2 and C_DESC_TXT is NCLOB.

when I tried NVL

SELECT T1.FN, NVL(T1.DESC_TXT, T2.C_DESC_TXT) AS DESCRIPTION
FROM table T1
LEFT JOIN table2 T2
ON T1.FN = T2.FN
where T1.ST_CD= 'Q'

I get below error:

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 2506, maximum: 2000)
22835. 00000 -  "Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: %s, maximum: %s)"
*Cause:    An attempt was made to convert CLOB to CHAR or BLOB to RAW, where
           the LOB size was bigger than the buffer limit for CHAR and RAW
           types.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Do one of the following
           1. Make the LOB smaller before performing the conversion,
           for example, by using SUBSTR on CLOB
           2. Use DBMS_LOB.SUBSTR to convert CLOB to CHAR or BLOB to RAW.

Is there any other method to get the required output?

Answer 1

You can use NVL:

SELECT T1.FN, NVL(T1.DESC_TXT, T2.C_DESC_TXT) AS DESCRIPTION
FROM table T1
LEFT JOIN table2 T2
ON T1.FN = T2.FN
where T1.ST_CD= 'Q'

Another option is to convert the NCLOB to VARCHAR2 using DBMS_LOB.SUBSTR:

SELECT T1.FN, NVL(T1.DESC_TXT, DBMS_LOB.SUBSTR(T2.C_DESC_TXT, 4000, 1)) AS DESCRIPTION
FROM table T1
LEFT JOIN table2 T2
ON T1.FN = T2.FN
where T1.ST_CD= 'Q'

A third option is to use DECODE:

SELECT T1.FN, DECODE(T1.DESC_TXT, NULL, T2.C_DESC_TXT, T1.DESC_TXT) AS DESCRIPTION
FROM table T1
LEFT JOIN table2 T2
ON T1.FN = T2.FN
where T1.ST_CD= 'Q'