如何在 CLOB 列上使用 GROUP BY

我正在嘗試使用包含 Clob 列 (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 

當我執行這個查詢時，我得到了錯誤

ORA-00932: 不一致的數據類型預期得到 CLOB

這是因為 flow.IDFONCTIONNEL 列是 CLOB 數據類型。如果我從 select 子句中評論此列，它可以正常工作，但我需要在輸出中使用此列。

我看到一篇文章告訴我嘗試使用 DBMS_LOB.SUBSTR 來避免這個問題，所以我嘗試了：

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 

但我得到 ORA-00904: “IDF2” 無效標識符。

任何人都知道它為什麼不起作用？非常感謝

GROUP BY不能使用在同一個查詢塊中定義的別名。

你可以使用這個：

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 

或者是這樣的：

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 

引用自：https://dba.stackexchange.com/questions/271483