SELECT A.PACK_NO,
(
/*-- plsql中执行时提示错误“ORA-00904:"A"."PACK_NO":标识符无效”
SELECT LISTAGG(ORDER_REMARK, '; ') WITHIN GROUP(ORDER BY ORDER_REMARK)
FROM (SELECT DISTINCT ORDER_REMARK
FROM T_BUSIS_PACKING_SLIP_DETAIL
WHERE PACK_NO = A.PACK_NO)*/
/*PACK_NO ORDER_REMARKS TOTAL_EXPORT_PRICE
PN000008532 从2009年起订 9645
PN000008500 从2012年起订购,从2014年起按长期订购,长期订单 从2013年起订 52356*/
SELECT WM_CONCAT(DISTINCT TSD.ORDER_REMARK)
FROM T_BUSIS_PACKING_SLIP_DETAIL TSD
WHERE TSD.PACK_NO = A.PACK_NO) AS ORDER_REMARKS,
(SELECT SUM(TSD.SETTLE_ACTURE_AMOUNT * TSD.PRO_NUM)
FROM T_BUSIS_RELEASE_ORDER_DETAIL TRD
JOIN T_BUSIS_PACKING_SLIP_DETAIL TSD
ON TSD.RELEASE_DETAIL_ID = TRD.ID
WHERE TSD.PACK_NO = A.PACK_NO
GROUP BY TSD.PACK_NO) AS TOTAL_EXPORT_PRICE
FROM T_BUSIS_PACKING_SLIP A
WHERE A.PACK_NO IN ('PN000008532', 'PN000008500')
ORDER BY A.CUST_ACCT_CODE, A.PACK_NO ASC
/*
-- 单独摘出来执行可以
-- LISTAGG(ORDER_REMARK,';')WITHI
-- 从2012年起订购; 从2014年起按长期订购; 长期订单 从2013年起订
SELECT LISTAGG(ORDER_REMARK, '; ') WITHIN GROUP(ORDER BY ORDER_REMARK)
FROM (SELECT DISTINCT ORDER_REMARK
FROM T_BUSIS_PACKING_SLIP_DETAIL
WHERE PACK_NO = 'PN000008500')
*/