如何按表达式在组中制作CLOB列?任何工作吗?

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

ORA-00932:预期不一致的数据类型导致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.

这是因为Test_Case_Description列是一个CLOB数据类型。如果我从select子句注释这一列，它可以正常工作，但是我需要输出这一列。

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

上面的查询是原始查询的简化版本

2 个解决方案

#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..

由于您担心在4000个字符后不会丢失数据，我的建议是将该列划分为如下所示。

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

#2

I just changed my query to as mentioned below and it worked:

我只是将我的查询改为如下所述，它是有效的:

SELECT 
    InnerTable.Test_Case_Name,
    Test.Test_Case_Description,
    InnerTable.Test_Case_Status,
FROM 
    (   SELECT
    Test_Case_ID
    Test_Case_Name,
    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_Status ) AS InnerTable INNERJOIN TEST ON InnerTable.Test_Case_ID = Test.Test_Case_ID

#1