Group by with a substr that is part of a clob

Question

I need your help with the following sql:

SELECT 
  substr(nd.nne_def,4,3) as bzn
FROM 
    core_def.nne_def nd
group by substr(nd.nne_def,4,4);

I got the error:

ORA-00932: 00932. 00000 -  "inconsistent datatypes: expected - got CLOB"

I need the sql just for analysis not for production code.

How can I group by a substring that is part of a CLOB?

Thx for your help.

Stefan

Answer 1

SUBSTR doesn't work with CLOBs - you need DBMS_LOB.SUBSTR:

SELECT 
  dbms_lob.substr(nd.nne_def,4,4) as bzn
FROM 
  core_def.nne_def nd
group by dbms_lob.substr(nd.nne_def,4,4);