1

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: "IDF2" invalid identifier.

Anyone as an idea of why it is not working ? Thanks a lot

1

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.