oracle 不一致的数据类型：预期 - 获得表连接的 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37931856/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
inconsistent datatypes: expected - got CLOB for table join
提问by Dave
Oracle XE 11. a very simple join query gave me the following error:
Oracle XE 11.一个非常简单的连接查询给了我以下错误：
ORA-00932: inconsistent datatypes: expected - got CLOB
Tables:
表格：
Product
----------------------------------
id, name, description, categoryId
Catetory
------------------
id, name
The product description is CLOB.
产品描述为 CLOB。
SQL> desc Product;
SQL> desc 产品；
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(19)
NAME NOT NULL VARCHAR2(30 CHAR)
CATEGORYID NUMBER(19)
DESCRIPTION CLOB
SQL> desc Category;
SQL> desc 类别；
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(19)
NAME NOT NULL VARCHAR2(30 CHAR)
Query:
询问：
SELECT DISTINCT t1.ID, t1.DESCRIPTION, t1.NAME, t0.name FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got CLOB
第 1 行的错误：ORA-00932：不一致的数据类型：预期 - 得到 CLOB
IF I remove the t0.name from selection, it will work. weird.
如果我从选择中删除 t0.name，它将起作用。奇怪的。
SELECT DISTINCT t1.ID, t1.DESCRIPTION, t1.NAME FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
Thanks.
谢谢。
回答by dcieslak
The DISTINCTkeyword cannot be used for CLOBdatatypes.
The workaround is :
该DISTINCT关键字不能用于CLOB数据类型。解决方法是：
SELECT a.*
, b.clob
FROM (SELECT DISTINCT
... /* columns list wihtout clob columns */
FROM ...
) a
JOIN
table_with_clobs b
ON ...
Going to your sample it would be:
转到您的样本，它将是：
SELECT Po.ID, Po.DESCRIPTION, Po.NAME, PC.CatName
FROM
( SELECT DISTINCT t1.ID, t0.name CatName
FROM Product t1
LEFT OUTER JOIN Category t0
ON t0.ID = t1.categoryId
) PC
join Product PO
on PO.ID = PC.ID
回答by cableload
If your clob column does not contain more than 4000 characters, you could try this..
如果您的 clob 列不包含超过 4000 个字符，您可以试试这个..
SELECT DISTINCT t1.ID, to_char(t1.DESCRIPTION), t1.NAME FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
回答by Krzysztof Kosmatka
As @dcieslak has already stated, The DISTINCTkeyword cannot be used for CLOBdatatypes.
正如@dcieslak 已经声明的那样，DISTINCT关键字不能用于CLOB数据类型。
IF I remove the t0.name from selection, it will work. weird.
如果我从选择中删除 t0.name，它将起作用。奇怪的。
I guess there is an unique index on Product.idcolumn. When you remove t0.namefrom SELECTclause, all selected columns com from the same table. So when one of those columns have unique values (t1.idin your case), then all rows will always be unique. And that implies that there is no need for any comparison of CLOBcolumns.
我猜Product.id列上有一个唯一索引。当您删除t0.namefromSELECT子句时，所有选定的列都来自同一个表。因此，当其中一列具有唯一值（t1.id在您的情况下）时，所有行将始终是唯一的。这意味着不需要对CLOB列进行任何比较。
BUT
if there is an unique index on Product.id, then you do not need DISTINCTkeyword at all. For query:
但是，如果 上有唯一索引Product.id，那么您根本不需要DISTINCT关键字。查询：
SELECT t1.ID, t1.DESCRIPTION, t1.NAME, t0.name FROM Product t1
LEFT OUTER JOIN Category t0 ON (t0.ID = t1.categoryId);
you will get exactly one row for each row from Producttable, which must be distinct due to distinct t1.IDvalues.
对于Product表中的每一行，您将获得准确的一行，由于不同的t1.ID值，这些行必须是不同的。
