5 Replies Latest reply on Jul 28, 2010 3:51 PM by 558383

Problem with inconsistent data type

782809 Jul 28, 2010 3:30 PM

Hi all,

I have written this query and trying to execute this and i had the following error.

I had this error when i am adding description column.if i dont add description column query is working fine._

Tried to resolve in other ways but didnt worked out.

Please help on this.

SELECT description, Product, /*Defect.Product*/
to_char(bg_detection_date,'yyyy') Year,
to_char(bg_detection_date,'Q')||'Q'Quarter
FROM bug
where service_type like 'Incident'
and product in ('Siebel','Intg_Mgmt','SOA','NICE','Genesys','Analytics DW')
AND DETECTION_DATE
BETWEEN TO_DATE('01-01-2009','MM-DD-YYYY')
AND TO_DATE ('10-30-2010','MM-DD-YYYY')

group by to_char(bg_detection_date,'yyyy'),to_char(bg_detection_date,'Q'),
product, description

order by product,year desc,quarter asc

error :ORA-00932:Inconsistent data type.expected get clob_

1. Re: Problem with inconsistent data type

558383 Jul 28, 2010 3:36 PM (in response to 782809)

SQL> create table t(x int, y clob);

Table created.


SQL> select x, count(*) from t group by x;

no rows selected

SQL> select y, count(*) from t group by y;
select y, count(*) from t group by y
                                   *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

SQL> select y from t order by y;
select y from t order by y
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

You cannot use GROUP BY or ORDER BY with a CLOB column.

2. Re: Problem with inconsistent data type

782809 Jul 28, 2010 3:36 PM (in response to 558383)

Hi..

Thanks for the reply.

Is there any work around for this..

Please advice me on this
Like Show 0 Likes(0)

Actions

3. Re: Problem with inconsistent data type

558383 Jul 28, 2010 3:43 PM (in response to 782809)

You can try to group or sort on CLOB part:

SQL> select dbms_lob.substr(y,100,1), count(*) from t group by dbms_lob.substr(y,100,1);

no rows selected

SQL> select dbms_lob.substr(y,100,1) from t order by dbms_lob.substr(y,100,1);

no rows selected

4. Re: Problem with inconsistent data type

782809 Jul 28, 2010 3:46 PM (in response to 558383)

hi..thanks for that...

can you help with my code.

how should i make my code work.i cant drop the description column from the query.

Please help me on tht...
Like Show 0 Likes(0)

Actions
5. Re: Problem with inconsistent data type

558383 Jul 28, 2010 3:51 PM (in response to 782809)

Try to replace in your query each occurence of "description" by "dbms_lob.substr(description,100,1)" for example.

This is OK if you how only want to display and group by using the first 100 characters of "description" column.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.