SELECT A CLOB - GROUP BY

Hi,

I am having trouble selecting a CLOB (CASE_LIST), here is what happens:

SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases Created",
CASE_LIST
FROM PEOPLESOFTBUCUSTPROD
GROUP BY PRODUCT_NAME, CASE_LIST
ORDER BY PRODUCT_NAME I get this error: ORA-00932: inconsistent datatypes: expected - got CLOB Then I did this, using TO_CHAR:

SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases Created",
TO_CHAR(CASE_LIST)
FROM PEOPLESOFTBUCUSTPROD
GROUP BY PRODUCT_NAME, TO_CHAR(CASE_LIST) ORDER BY PRODUCT_NAME I get this error: ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 4997, maximum: 4000)

So lastly I did this, using DBMS_LOB.SUBSTR:

SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases Created",
DBMS_LOB.SUBSTR(CASE_LIST, 4000, 1) CASE_LIST FROM PEOPLESOFTBUCUSTPROD
GROUP BY PRODUCT_NAME, DBMS_LOB.SUBSTR(CASE_LIST, 4000, 1) ORDER BY PRODUCT_NAME I am now able to get results, but my data is truncated at 4000 characters, which will not work

Any ideas on how to get the full clob results in one query? Received on Thu Apr 03 2008 - 16:56:42 CDT