SELECT A CLOB - GROUP BY

[trp...@gmail.com]

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?

[trp...@gmail.com]

Is it possible to Select a CLOB in a GROUP BY? Is there a work around
for when TO_CHAR exceeds 4000 characters?

[Shakespeare]

<trp...@gmail.com> schreef in bericht
news:132260f0-7ad9-4fe6...@24g2000hsh.googlegroups.com...

Don't put your CLOB in the group by clause, just in the select and only
group by product_name. I don't expect you really want to group by a CLOB, do
you?

Shakespeare

[trp...@gmail.com]

On Apr 4, 9:21 am, "Shakespeare" <what...@xs4all.nl> wrote:
> <trp...@gmail.com> schreef in berichtnews:132260f0-7ad9-4fe6...@24g2000hsh.googlegroups.com...

> Shakespeare- Hide quoted text -
>
> - Show quoted text -

If I do that I get an error:

SELECT PRODUCT_NAME AS "Product Name", SUM(CASES_CREATED) AS "Cases
Created",
CASE_LIST
FROM PEOPLESOFTBUCUSTPROD
GROUP BY PRODUCT_NAME

ORDER BY PRODUCT_NAME

ORA-00979: not a GROUP BY expression

[Shakespeare]

<trp...@gmail.com> schreef in bericht
news:76ea9e0e-3bd4-489e...@8g2000hse.googlegroups.com...

------------------------------------------------
You're right, my mistake

Shakespeare

[trp...@gmail.com]

Is there anything that can be done with a PLSQL function to convert a
CLOB to a VARCHAR2 to a size larger than 4000 characters? I read that
PLSQL can handle a VARCHAR2 with 32767 charcaters, so is it possible
to write a function to return this conversion?

I found the following, but couldn't get it to compile:

CREATE OR REPLACE FUNCTION GetVarchar2 (iclCLOB IN OUT CLOB)
return VARCHAR2
IS

cnuMAX_LENGTH Constant number := 32767 ;
nuLength Number := DBMS_LOB.getlength(iclCLOB);
sbBuffer varchar2(32767);

DBMS_LOB.read(iclCLOB,nuLength,1,bBuffer);
return sbBuffer;
END

Thanks

[Ed Prochak]

If you are returning it to SQL, then no this doesn't work.

You never really answered the question: why would you want to group by
the CLOB column?
What makes you think the value of one CLOB will match another?
You know your data. I'm just asking because often CLOBs are used for
notes, comments and other text that is unlikely to match another entry
exactly.

I suspect what you will have to do is redesign your tables. You CLOB
column seems to be a list of some sort. Perhaps it could be normalized
to its own table, splitting the data in the CLOB to different rows.
Getting the grouping you want may still be troublesome but more likely
to be doable. But it depends on the nature of the data in the CLOB
column.

HTH,
Ed

--
Magic Interface, Ltd.
www.magicinterface.com
440-498-3700
Hardware/Software Alchemy

[Shakespeare]

<trp...@gmail.com> schreef in bericht
news:e2cb990f-1c6f-40ac...@n58g2000hsf.googlegroups.com...

Is the case_list the same for every 'PRODUCT_NAME'?
If it is, don't select it on forehand, but do the group by first and then
look up the case list
If it's not, it makes no sense to do a group by at all.

Shakespeare

[saluj...@gmail.com]

I want to use group by function onm CLOB as I want to match part of one CLOB data to another.Can anyone please help

[ddf]

I'm lost -- how does group by match partial strings?

Here's an example of matching part of a string in a CLOB field:

SQL> create table mylob (myid number not null,
2 mytext clob);

Table created.

SQL>
SQL> begin
2 for i in 1..100 loop
3 insert into mylob
4 values(i, 'Text: '||to_char(to_date(i, 'J'), 'JSP')||' and a partridge in a pear tree');
5 end loop;
6
7 commit;
8
9 end;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.substr(mytext, dbms_lob.instr(mytext, 'ONE'), 8)
2 from mylob
3 where dbms_lob.instr(mytext, 'ONE') > 0;

DBMS_LOB.SUBSTR(MYTEXT,DBMS_LOB.INSTR(MYTEXT,'ONE'),8)
-----------------------------------------------------------------------------------------------------------------------------
ONE and
TWENTY-ONE and
THIRTY-ONE and
FORTY-ONE and
FIFTY-ONE and
SIXTY-ONE and
SEVENTY-ONE and
EIGHTY-ONE and
NINETY-ONE and
ONE HUND

10 rows selected.

SQL>


David Fitzjarrell