1

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

I have two relations.

Relation One:

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)

Relation Two:

LOGID_NBR       NUMBER (12)     
INPUT_ARGS      CLOB

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;

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 ...

produces the following error results:

[Error] Execution (8: 32): ORA-00932: inconsistent datatypes: expected - got 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?

Community
  • 1
  • 1
Mushy
  • 2,535
  • 10
  • 33
  • 54
  • The duplicate should get you further, in working out how to get a DISTINCT data-set. However, I'd reconsider whether that is actually the problem and work out _why_ you need a DISTINCT in the first place. Could it be that your database isn't normalised correctly? – Ben Jun 12 '14 at 12:21
  • @Ben I need a distinct because for each `EVENTID_NBR` there is a different `CLOB`. Thus, I may have one hundred `EVENTID_NBR = 143` but each has a different `CLOB` and, hence, I am only interested in `DISTINCT` pairs. – Mushy Jun 12 '14 at 13:07
  • So you have a bunch of different input_args (CLOB) values for a single eventid_nbr and some of the input_args are the same within the same eventid_nbr? – Bob Jun 13 '14 at 18:54

1 Answers1

0

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);

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

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.

Bob
  • 1,045
  • 8
  • 10