SELECT A CLOB - GROUP BY

#1

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?

#2

On Apr 3, 3:56*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

#3

On Apr 3, 3:56*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

#4

On Apr 3, 3:56*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

#5

On Apr 3, 3:56*pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

#6

<trpost (AT) gmail (DOT) com> schreef in bericht
news:132260f0-7ad9-4fe6-b9b1-e4c4904f366e (AT) 24g2000hsh (DOT) googlegroups.com...
On Apr 3, 3:56 pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

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

#7

<trpost (AT) gmail (DOT) com> schreef in bericht
news:132260f0-7ad9-4fe6-b9b1-e4c4904f366e (AT) 24g2000hsh (DOT) googlegroups.com...
On Apr 3, 3:56 pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

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

#8

<trpost (AT) gmail (DOT) com> schreef in bericht
news:132260f0-7ad9-4fe6-b9b1-e4c4904f366e (AT) 24g2000hsh (DOT) googlegroups.com...
On Apr 3, 3:56 pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

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

#9

<trpost (AT) gmail (DOT) com> schreef in bericht
news:132260f0-7ad9-4fe6-b9b1-e4c4904f366e (AT) 24g2000hsh (DOT) googlegroups.com...
On Apr 3, 3:56 pm, trp... (AT) gmail (DOT) com wrote:

Quote:

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?

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

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

#10

On Apr 4, 9:21*am, "Shakespeare" <what... (AT) xs4all (DOT) nl> wrote:

Quote:

trp... (AT) gmail (DOT) com> schreef in berichtnews:132260f0-7ad9-4fe6-b9b1-e4c4904f366e (AT) 24g2000hsh (DOT) googlegroups.com...
On Apr 3, 3:56 pm, trp... (AT) gmail (DOT) com wrote:

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?
Is it possible to Select a CLOB in a GROUP BY? Is there a work around
for when TO_CHAR exceeds 4000 characters?

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