How to use GROUP BY on a CLOB Column i'm trying to use this QUERY which contains a Clob Column (flow.IDFONCTIONNEL)
SELECT  
flow.flowid,   
min(flow.CONTEXTTIMESTAMP) contextTime,   
flow.STATUT,
flow.IDFONCTIONNEL,
flow.ETAT
FROM Flux flow 
WHERE  flow.FLOWCODE = 'HELLO' 
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000' 
GROUP BY flow.flowid, flow.STATUT , flow.ETAT, flow.IDFONCTIONNEL
ORDER BY contextTime desc 


When I run this query, I get the error
ORA-00932: inconsistent data types Expected got CLOB
This is because the column flow.IDFONCTIONNEL is a CLOB data type. If I comment this column from select clause it works fine but I need this column in the output.
I have seen a post telling to try to use DBMS_LOB.SUBSTR to try avoid this problem, so i tryed :
SELECT  
flow.flowid,   
min(flow.CONTEXTTIMESTAMP) contextTime,   
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow 
WHERE  flow.FLOWCODE = 'HELLO' 
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000' 
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, idf1 ,idf2 
ORDER BY contextTime desc 


But i get ORA-00904: &quot;IDF2&quot; invalid identifier.
Anyone as an idea of why it is not working ?
Thanks a lot
GROUP BY can not use aliases defined in the same query block.
You can use this:
SELECT  
flow.flowid,   
min(flow.CONTEXTTIMESTAMP) contextTime,   
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow 
WHERE  flow.FLOWCODE = 'HELLO' 
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000' 
GROUP BY flow.flowid, flow.STATUT,flow.ETAT, DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1),
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) 
ORDER BY contextTime desc 

Or something like this:
SELECT
flowid,
min(CONTEXTTIMESTAMP) contextTime,
STATUT,
idf1,
idf2,
ETAT
from
(
SELECT  
flow.flowid,   
flow.CONTEXTTIMESTAMP,   
flow.STATUT,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,4000,1) as idf1,
DBMS_LOB.SUBSTR(flow.IDFONCTIONNEL,8000,4001) as idf2,
flow.ETAT
FROM Flux flow 
WHERE  flow.FLOWCODE = 'HELLO' 
AND flow.CONTEXTTIMESTAMP BETWEEN '06/01/20 11:36:21,566000000' AND '06/07/20 11:36:21,566000000'
)
GROUP BY flowid, STATUT,ETAT, idf1 ,idf2 
ORDER BY contextTime desc