Database Administrators Asked by Dallincha on December 8, 2021
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
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
Answered by Balazs Papp on December 8, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP