今天發現 一條sql在pl/sql中好着,系統中卻查詢報錯,
SELECT OLOH.ROUTE_NAME,
OLOH.FM_PLAN_ORDER_TIME,
OLTH.ROUTE_TYPE,
to_char(wm_concat(OLTH.TASK_ID)) as TASK_IDS
FROM OM_LOGISTICS_ORDER_HEADER OLOH
LEFT JOIN OM_LOGISTICS_TASK_HEADER OLTH
ON OLOH.ORDER_NO = OLTH.PARENT_ORDER_NO
WHERE 1 = 1
GROUP BY OLOH.ROUTE_NAME, OLOH.FM_PLAN_ORDER_TIME, OLTH.ROUTE_TYPE;
![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLiAzNfRHLGZkRGZkRfJ3bs92YsYTMfVmepNHL1x2RlBHatVGcwhVYv5kMMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnL5IjN4IDN1ATMzITMxgTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
經過測試發現是wm_concat引起的。網上說:
1.Oracle Database 10g Enterprise Edition Release 10.2.0.5.0以後的版本wmsys.wm_concat查詢出的是LOB類型
2.oralce的SQL語句中若查詢了LOB字段是不能使用distinct,union,和group by等關鍵字的。
wm_concat已經轉to_char了,但還是報錯???????
這sql必須有group by,沒辦法,那就不用wm_concat函數了,換種寫法。
SELECT OLOH.ROUTE_NAME,
OLOH.FM_PLAN_ORDER_TIME,
OLTH.ROUTE_TYPE,
--to_char(wm_concat(OLTH.TASK_ID)) as TASK_IDS,
listagg(OLTH.TASK_ID,',') within GROUP (order by OLTH.TASK_ID) as TASK_IDS
FROM OM_LOGISTICS_ORDER_HEADER OLOH
LEFT JOIN OM_LOGISTICS_TASK_HEADER OLTH
ON OLOH.ORDER_NO = OLTH.PARENT_ORDER_NO
WHERE 1 = 1
GROUP BY OLOH.ROUTE_NAME, OLOH.FM_PLAN_ORDER_TIME, OLTH.ROUTE_TYPE;
執行好了。
主要是列轉行的聚合函數不太熟悉:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX),等同于wm_concat(xxx)。