How to use GROUP BY on a CLOB column with Oracle?

user_1112901 0
Viewed 42437

I'm trying to combine the GROUP BY function with a MAX in oracle. I read a lot of docs around, try to figure out how to format my request by Oracle always returns:

ORA-00979: "not a group by expression"

Here is my request:

SELECT A.T_ID, B.T, MAX(A.V) 
FROM bdd.LOG A, bdd.T_B B
WHERE B.T_ID = A.T_ID
GROUP BY A.T_ID
HAVING MAX(A.V) < '1.00';

Any tips ?

EDIT It seems to got some tricky part with the datatype of my fields.

  • T_ID is VARCHAR2
  • A.V is VARCHAR2
  • B.T is CLOB
max group-by oracle sql
7 Answers

you can if possible transform the clob into the PK if possible and than to an select on the PK. This is even faster according to execution plan then rowid. I this case i need the first not empty clob. So i say if clob is not empty use the pk else null. The result is not an clob and i can fetch the clob in an outer query.

select a.* ,r.DESCRIPTION 
from (
select distinct
FIRST_VALUE(
  case 
     when a.DESCRIPTION is null then null 
     else PK_COL 
  end 
  IGNORE NULLS) 
  OVER (ORDER BY a.sort_col desc ROWS between UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) DESCRIPTION_PK, 
group_column
from reporting a where group_column='xyz) a
join reporting r on (r.PK_COL=a.DESCRIPTION_PK);
user_19317740

This doesn't solve OP's problem, but may help in some cases.

Let we have

CREATE TABLE "TEST" (   
  ID NUMBER, 
  DATA CLOB
);

If actual CLOB data doesn't exceed 4000 chars, we can convert it to VARCHAR32 using TO_CHAR and then perform grouping we need:

SELECT 
  DATA_AS_CHAR,
  COUNT(*)
FROM
(
  SELECT
    ID,
    TO_CHAR(DATA) DATA_AS_CHAR
  FROM TEST
)
GROUP BY
  DATA_AS_CHAR;

If it exceeds, we have to apply some heuristics, e.g. substring first 4000 chars:

SELECT 
  DATA_AS_CHAR,
  COUNT(*)
FROM
(
  SELECT
    ID,
    TO_CHAR(DBMS_LOB.SUBSTR(DATA, 4000)) DATA_AS_CHAR
  FROM TEST
)
GROUP BY
  DATA_AS_CHAR;

Like any other hashing technique, this may lead to collisions (having less number of groups than expected).

user_11929870
Related