ORA-22922: 不存在的 LOB 值解决办法

编程如人生

于 2024-03-11 15:57:08 发布

阅读量407

点赞数 2

文章标签：数据库

本文链接：https://blog.csdn.net/qqq8724/article/details/136626278

版权

出错语句：

SELECT 
  T1.CUST_NAME,
  T1.DEPT_NAME,
  T1.PROD_LINE_NAME
FROM (

select T896.CUST_NAME   ,
          TO_CHAR(WM_CONCAT(DISTINCT DEPT_NAME)) DEPT_NAME,
          TO_CHAR(WM_CONCAT(DISTINCT PROD_LINE_NAME)) PROD_LINE_NAME,
         SUM(T1640.SALES_AMOUNT) SALES_AMOUNT   
    from 
         CHITDW.D_CUSTOMER        T896,
         CHITDW.D_PRODUCT         T1052,
         CHITDW.D_DEPARTMENT      T934,
         CHITDW.F_SALES_CUR       T1640
   where 
         T896.CUST_KEY = T1640.CUST_KEY and 
         T1052.PROD_KEY = T1640.PROD_KEY and
         T934.DEPT_KEY = T1640.DEPT_KEY and 
         T934.cadre_name = 'xxx' and 
         T1640.ADJUST_FLAG = 0
  group by
        T896.CUST_NAME
) T1

因为WM_CONCAT的结果为clob，无法直观看到数据，我转为了VARCHAR类型，单独执行里面的子查询是没问题的，加了外层语句就会报错。
然后尝试在最外层转换类型，问题解决：

SELECT 
  T1.CUST_NAME,
  TO_CHAR(T1.DEPT_NAME) DEPT_NAME,
  TO_CHAR(T1.PROD_LINE_NAME) PROD_LINE_NAME
FROM (

select T896.CUST_NAME   ,
          WM_CONCAT(DISTINCT DEPT_NAME) DEPT_NAME,
          WM_CONCAT(DISTINCT PROD_LINE_NAME) PROD_LINE_NAME,
         SUM(T1640.SALES_AMOUNT) SALES_AMOUNT   
    from 
         CHITDW.D_CUSTOMER        T896,
         CHITDW.D_PRODUCT         T1052,
         CHITDW.D_DEPARTMENT      T934,
         CHITDW.F_SALES_CUR       T1640
   where 
         T896.CUST_KEY = T1640.CUST_KEY and 
         T1052.PROD_KEY = T1640.PROD_KEY and
         T934.DEPT_KEY = T1640.DEPT_KEY and 
         T934.cadre_name = 'xxx' and 
         T1640.ADJUST_FLAG = 0 
  group by
        T896.CUST_NAME
) T1