Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!8g2000hse.googlegroups.com!not-for-mail
From: trpost@gmail.com
Newsgroups: comp.databases.oracle.misc
Subject: Re: SELECT A CLOB - GROUP BY
Date: Fri, 4 Apr 2008 08:49:42 -0700 (PDT)
Organization: http://groups.google.com
Lines: 71
Message-ID: <76ea9e0e-3bd4-489e-90e0-9981ed5d2203@8g2000hse.googlegroups.com>
References: <8d94c19f-ddf2-4b3b-92b9-bb0f4e747bd8@s50g2000hsb.googlegroups.com> 
 <132260f0-7ad9-4fe6-b9b1-e4c4904f366e@24g2000hsh.googlegroups.com> 
 <47f64770$0$14351$e4fe514c@news.xs4all.nl>
NNTP-Posting-Host: 169.143.0.103
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1207324183 23222 127.0.0.1 (4 Apr 2008 15:49:43 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 4 Apr 2008 15:49:43 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: 8g2000hse.googlegroups.com; posting-host=169.143.0.103; 
 posting-account=BRjxygoAAABJJ7iuyGDq8_qh7GZzFz6c
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET 
 CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30),gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.misc:252353
X-Received-Date: Fri, 04 Apr 2008 10:49:43 EST (text.usenetserver.com)

On Apr 4, 9:21=A0am, "Shakespeare" <what...@xs4all.nl> wrote:
> <trp...@gmail.com> schreef in berichtnews:132260f0-7ad9-4fe6-b9b1-e4c4904f=
366e@24g2000hsh.googlegroups.com...
> On Apr 3, 3:56 pm, trp...@gmail.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