How to make CLOB column in group by expression? Any work around?

Question

I have a below query:

    SELECT
    Test_Case_Name,
    Test_Case_Description,
    Test_Case_Status,
CASE WHEN Test_Case_Status = 'FAILED' THEN
    LISTAGG(LN.LN_BUG_ID,', ') WITHIN GROUP(ORDER BY LN.LN_BUG_ID)
END AS Defect_ID
FROM Test LEFT JOIN LINK LN ON
    LN.LN_ENTITY_ID=Test.TS_TEST_ID
GROUP BY
    Test_Case_Name,
    Test_Case_Description,
    Test_Case_Status

When I run this query, I get the error

ORA-00932: inconsistent data types Expected got CLOB

This is because the column Test_Case_Description is a CLOB data type. If I comment this column from select clause it works fine but I need this column in the output.

The above query is a simplified version of the original query here

Answer 1

Here is the syntax you might want to use for your requirement:

Syntax:

DBMS_LOB.SUBSTR (lob_loc, amount, offset)

Parameter Description 
lob_loc: Locator for the LOB to be read i.e CLOB column name. 
amount: Number of bytes (for BLOBs) or characters (for CLOBs) to be read. 
offset: Offset in bytes (for BLOBs) or characters (for CLOBs) from the start of the LOB (origin: 1). 

So your final query should be something like this,

    SELECT
    Test_Case_Name,
    DBMS_LOB.SUBSTR(Test_Case_Description,2000,1) as Test_Case_Description,
    Test_Case_Status,
CASE WHEN Test_Case_Status = 'FAILED' THEN
    LISTAGG(LN.LN_BUG_ID,', ') WITHIN GROUP(ORDER BY LN.LN_BUG_ID)
END AS Defect_ID
FROM Test LEFT JOIN LINK LN ON
    LN.LN_ENTITY_ID=Test.TS_TEST_ID
GROUP BY
    Test_Case_Name,
    Test_Case_Description,
    Test_Case_Status

Since you are concerned about not loosing data after 4000 characters, my suggestion is to divide the column and display it as below..

SELECT
    Test_Case_Name,
    DBMS_LOB.SUBSTR(Test_Case_Description,4000,1) as Test_Case_Description1,
    DBMS_LOB.SUBSTR(Test_Case_Description,8000,4001) as Test_Case_Description2
    Test_Case_Status,
CASE WHEN Test_Case_Status = 'FAILED' THEN
    LISTAGG(LN.LN_BUG_ID,', ') WITHIN GROUP(ORDER BY LN.LN_BUG_ID)
END AS Defect_ID
FROM Test LEFT JOIN LINK LN ON
    LN.LN_ENTITY_ID=Test.TS_TEST_ID
GROUP BY
    Test_Case_Name,
    Test_Case_Description1,
    Test_Case_Description2,
    Test_Case_Status