| ORA-00932: inconsistent datatypes: expected - got CLOB [message #178629] |
Wed, 21 June 2006 23:38  |
lawyer0413
Messages: 3
Registered: June 2006
|
Junior Member |
|
|
There we have two tables: my_comment and sys_comment.
And field comment_desc in table my_comment is CLOB, and in table sys_comment is VARCHAR.
And I can run following two SQL successsfully:
SELECT comment_desc FROM my_comment;
SELECT TO_CLOB(comment_desc) FROM sys_comment;
But when I do UNION of them, execute following SQL:
SELECT comment_desc FROM my_comment
UNION
SELECT TO_CLOB(comment_desc) FROM sys_comment;
Error occurs:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
Can anyone tell me why and how to resolve it?
Many thanks.
|
|
|
|
| Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178657 is a reply to message #178629] |
Thu, 22 June 2006 02:19  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It's not a very helpful message. The problem is not that they are different data types, it is that UNION involves a SORT (to remove duplicates), and you cannot ORDER BY a CLOB.
You can see the same error if you:SELECT comment_desc FROM my_comment ORDER BY 1
If you know there will be no duplicates, use UNION ALL instead. Otherwise, look into the DBMS_LOB package (I haven't, but it might help).
Ross Leishman
|
|
|
|
|
|
| Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #178815 is a reply to message #178661] |
Thu, 22 June 2006 22:28 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you wanted just a distinct list of results (ie. if a value exists in both tables it is reported only once), then UNION ALL will not help, because it simply appends the results of the second query to the first.
If you are happy to have duplicates (or you know that no duplicates exist), then UNION ALL is easily the best and fastest method because it involves no sorting and no joins.
To eliminate duplicates, you could try something like:SELECT clob_col
FROM tab2
UNION ALL
(
SELECT vc_col
FROM tab1
MINUS
SELECT dbms_lob.substr(clob_col ...)
FROM tab2
)
You could also do something similar using a NOT IN sub-query that might perform a little better.
Ross Leishman
|
|
|
|
| Unique CLOB data [message #192378 is a reply to message #178629] |
Tue, 12 September 2006 01:42 |
thatsprem
Messages: 1
Registered: September 2006
|
Junior Member |
|
|
It may or may not help you out....
My problem was to remove the duplicates that contains in a CLOB data column.
I followed below approach.
select unique(to_char(clobdata)) from clobdatatable
Thank,
Prem
|
|
|
|
| Re: ORA-00932: inconsistent datatypes: expected - got CLOB [message #598966 is a reply to message #178629] |
Mon, 21 October 2013 02:33 |
 |
jayasimha
Messages: 1
Registered: October 2013
|
Junior Member |
|
|
Hi Experts ,
Am facing same issue while executing below query
select logs.countrycode,logs.endsystem,logs.merchantid from (select countrycode,endsystem,SUBSTR(part2, 1, INSTR(part2, ';') - 1)MerchantId from (select countrycode,endsystem,SUBSTR(part1, INSTR(part1,'=') + 1) part2 from (select countrycode,endsystem,SUBSTR(additionalinfo, INSTR(additionalinfo, ';') + 1) part1 from esb_logs_1b where logtype= 'AUDIT_6'and medlaststate='SUCCESS') dual)dual )logs order by logs.merchantid
Here in above query additionalinfo is the clob datatype
Can any one please advise to resolve the above issue.
[Updated on: Mon, 21 October 2013 02:40] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
