How to make CLOB column in group by expression? Any work around?
13,575
Solution 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
Solution 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
Related videos on Youtube
Comments
-
javanoob over 1 year
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 aCLOB
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
-
Mari over 11 yearsyeah in such case you can print that column in a plsql block. PL SQL block has limition of more than 4000 characters..
-
javanoob over 11 yearsCould you please let me know how to do that? Any link? Thanks so much
-
Mari over 11 yearsI give you one more idea. can you tell me the max length of that column
-
javanoob over 11 yearsCurrently, It is 4292..but in future it may increase
-
Mari over 11 yearsDid you see my edited answer. Splitting and selecting. that will be a best option.. You don need to worry about your column size increase
-
javanoob over 11 years