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()转为字符串便没有报错了。
转载请注明原文地址: https://ju.6miu.com/read-1303328.html
