CODEWITHSUNDEEP
sqloracle-databasegroup-byclob
nano_nano
nano_nano

Reputation: 12523

Group by with a substr that is part of a clob

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

Upvotes: 0

Views: 871

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30815

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

Upvotes: 5

Related Questions