Oracle在查询中使用CLOB获取DISTINCT数字 [英] Oracle get DISTINCT numeric with a CLOB in the query

问题描述

编辑：我正在寻找 DISTINCT NUMERIC ，同时在查询中包含CLOB。

EDIT: I am looking for a DISTINCT NUMERIC while including a CLOB within the query.

我有两个关系。

关系一：

LOGID_NBR           NUMBER (12)
APPID_NBR           NUMBER (2)      
EVENTID_NBR         NUMBER (10)     
KEYID_NBR           NUMBER (8)      
KEYVALUE            VARCHAR2 (100 Byte)     
ARGUMENTSXML        VARCHAR2 (4000 Byte)    
SENTINDICATOR       CHAR (5 Byte)       
RECEIVED_DATEDATE           DATE sysdate    
LAST_UPDATED        DATE    sysdate 
TEXTINDICATOR       VARCHAR2 (5 Byte)       
UPSELL_ID           VARCHAR2 (5 Byte)       
GECKOIMAGEIND       CHAR (1 Byte)       
DELIVERYTYPE        VARCHAR2 (30 Byte)      

关系二：

LOGID_NBR       NUMBER (12)     
INPUT_ARGS      CLOB    

我已经查询了r如下所示：

I have queried the relations as follows:

SELECT EVENTID_NBR, INPUT_ARGS 

FROM RELATION_ONE, RELATION_TWO 

WHERE RELATION_ONE.LOGID_NBR = RELATION_TWO.LOGID_NBR AND 
   EVENTID_NBR BETWEEN 143 AND 192 AND 
   EVENTID_NBR != 172 AND SYSDATE - 7 >= RELATION_ONE.LAST_UPDATED 
   ORDER BY EVENTID_NBR;

我在我的帐户中收到了相同的 EVENTID_NBR 结果集过于频繁，仅对 DISTINCT 结果感兴趣。但是，将 DISTINCT 关键字添加到查询中，如下所示：

I am receiving the same EVENTID_NBR in my result set too often and only interested in DISTINCT results. However, adding the DISTINCT keyword to the query as in:

SELECT DISTINCT EVENTID_NBR, INPUT_ARGS ...

产生以下错误结果：

[Error] Execution (8: 32): ORA-00932: inconsistent datatypes: expected - got CLOB

因此，我开始在网上搜索如何解决此问题，并发现此，甚至此。我将这个CLOB转换成什么，可以在结果集中显示 DISTINCT EVENTID_NBR 及其关联的 CLOB ？

So, I started searching the net for how to solve this problem and found this and even this. What am I converting this CLOB to that enables a DISTINCT EVENTID_NBR and its' associated CLOB to be present within my result set?

推荐答案

如果对Clob进行哈希处理，则可以在子查询中使用它来为每个eventid_nbr获取相同的最大rowid Clob哈希值。然后，您只需在where子句中过滤您的Relation_Two表。

If you hash the clob, you can use it in a subquery to grab the max rowid for each eventid_nbr with the same clob hash value. Then you just filter your relation_two table in the where clause.

SELECT EVENTID_NBR, INPUT_ARGS 

FROM RELATION_ONE, RELATION_TWO 

WHERE RELATION_ONE.LOGID_NBR = RELATION_TWO.LOGID_NBR AND 
  EVENTID_NBR BETWEEN 143 AND 192 AND 
  EVENTID_NBR != 172 AND SYSDATE - 7 >= RELATION_ONE.LAST_UPDATED

  AND (RELATION_TWO.EVENTID_NBR, RELATION_TWO.ROWID) IN
      (SELECT DISTINCT EVENTID_NBR,
       MAX(ROWID) OVER (PARTITION BY EVENTID_NBR, DBMS_HASH(INPUT_ARGS,3))
       FROM RELATION_TWO);

HASH中指定SHA的3，但您也可以使用MD4（1）或MD5（2

The 3 in the HASH specified SHA, but you can also use MD4 (1) or MD5 (2) if you like.

如果您在RELATION_TWO表中有很多行，我想这可能会很慢，而且我敢肯定这可以写入表现更好，但概念是合理的。

I'm thinking this could be very slow if you have a lot of rows in the RELATION_TWO table and I'm sure this can be written to perform better, but the concept is sound.

这篇关于Oracle在查询中使用CLOB获取DISTINCT数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！