oracle异常之-ORA-00932:数据类型不一致:应为-,但却获得CLOB

谦奕爸爸 2016-08-15 14:39:14

48689

分类专栏： # Oracle 文章标签：数据 ORA-00932

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/weinichendian/article/details/52210938

版权

SELECT * FROM
(
       SELECT SUM(debitquantity) AS "debitquantity",
              SUM(debitamount) AS "debitamount",
              SUM(localdebitamount) AS "localdebitamount",
              SUM(creditquantity) AS "creditquantity",
              SUM(creditamount) AS "creditamount",
              SUM(localcreditamount) AS "localcreditamount",
              TO_CHAR(WM_CONCAT(DISTINCT tablex.pkAccsubj)) AS pkAccsubj
        FROM (SELECT SUM(glDetail.creditamount) AS creditamount,
                     SUM(glDetail.debitamount) AS debitamount,
                     SUM(glDetail.localcreditamount) AS localcreditamount,
                     SUM(glDetail.localdebitamount) AS localdebitamount,
                     SUM(glDetail.creditquantity) AS creditquantity,
                     SUM(glDetail.debitquantity) AS debitquantity,
                     glDetail.pk_accsubj AS pkAccsubj
               FROM gl_detail glDetail
               LEFT JOIN bd_accsubj bdAccsubj
               ON glDetail.pk_accsubj = bdAccsubj.pk_accsubj
               LEFT JOIN gl_voucher glVoucher
               ON glDetail.pk_voucher = glVoucher.pk_voucher
               WHERE 1 = 1
                   AND glDetail.yearv = '2014'
                   AND glDetail.periodv = '01'
                   AND bdAccsubj.subjcode LIKE CONCAT(1001, '%')
                   AND glVoucher.pk_corp = '1012'
                   AND glVoucher.isdifflag = 'N'
                   AND (glVoucher.pk_manager IS NOT NULL AND
                       glVoucher.pk_manager != 'N/A')
         GROUP BY glDetail.yearv, glDetail.periodv, glDetail.pk_accsubj) tablex
) tableA 
LEFT JOIN
(
     SELECT (SUM(debitquantity) - SUM(creditquantity)) AS "initamount",
            (SUM(debitamount) - SUM(creditamount)) AS "initcreditamount",
            (SUM(localdebitamount) - SUM(localcreditamount)) AS "initdebitamount",
            TO_CHAR(WM_CONCAT(DISTINCT tabley.pkAccsubj)) AS pkAccsubj
     FROM (SELECT SUM(glDetail.creditamount) AS creditamount,
                  SUM(glDetail.debitamount) AS debitamount,
                  SUM(glDetail.localcreditamount) AS localcreditamount,
                  SUM(glDetail.localdebitamount) AS localdebitamount,
                  SUM(glDetail.creditquantity) AS creditquantity,
                  SUM(glDetail.debitquantity) AS debitquantity,
                  glDetail.pk_accsubj AS pkAccsubj
          FROM gl_detail glDetail
          LEFT JOIN bd_accsubj bdAccsubj
            ON glDetail.pk_accsubj = bdAccsubj.pk_accsubj
          LEFT JOIN gl_voucher glVoucher
            ON glDetail.pk_voucher = glVoucher.pk_voucher
         WHERE 1 = 1
           AND glDetail.yearv = '2014'
           AND bdAccsubj.subjcode LIKE CONCAT(1001, '%')
           AND glVoucher.pk_corp = '1012'
           AND glVoucher.isdifflag = 'N'
           AND (glVoucher.pk_manager IS NOT NULL AND
               glVoucher.pk_manager != 'N/A')
         GROUP BY glDetail.yearv, glDetail.periodv, glDetail.pk_accsubj) tabley
) tableB ON tableA.pkAccsubj = tableB.pkAccsubj

我出现上面这个异常的原因是：WM_CONCAT()函数返回的是文本类型，作为关联字段时取得CLOB没有，没有取到真正的字段值，将字段TO_CHAR()转为字符串便没有报错了。