ORA-22922 错误 wm_concat 函数的替代方案
来源:互联网 发布:excel 没有数据分析包 编辑:程序博客网 时间:2024/06/03 20:57
CREATE OR REPLACE FORCE VIEW BEFORVIPRESET150630.V_YHJ_VIP_WX_XSMX
( ORDERDATE, MEMBER_CODE, INV_NUM, ZJE,
ZFJE, SHOP_CODE, SKU, C_SHORT_DE,
SELL_QTY, SERIAL_NO )
AS
SELECT
CASE
WHEN A.ISDEPOSIT<>1
THEN TO_CHAR(A.INV_DATE,'yyyy-mm-dd')
ELSE
(SELECT TO_CHAR(MAX(PAID_DATE),'yyyy-mm-dd') AS INV_DATE
FROM PAY_DEP E
WHERE E.INV_NUM = A.INV_NUM
)
END AS "ORDERDATE",
TRIM(A.MEMBER_CODE),
A.INV_NUM,
SUM(B.SELL_QTY)*ABS(SUM(B.DISC_AMT+B.SUBTOTAL)) "zje",
SUM(B.SELL_QTY)*ABS(SUM(B.DISC_AMT+B.SUBTOTAL)) "zfje",
(
CASE
WHEN A.INV_TYPE=0
THEN S.WARE_CODE
ELSE SR.STOREROOM_CODE
END
) AS SHOP_CODE,
B.BAR_CODE,
B.C_SHORT_DE,
SUM(B.SELL_QTY),
replace(wm_concat(DC.SERIAL_NO),' ','')
FROM MEMBERSHIP D
LEFT JOIN DINV_HDR A
ON D.COM_ID = A.COM_ID
AND D.MEMBER_CODE = A.MEMBER_CODE
AND
(
NOT trim(D.MEMBER_CODE) IS NULL
)
INNER JOIN DINV_DTL B
ON B.COM_ID = A.COM_ID
AND B.SHOP_CODE = A.SHOP_CODE
AND B.COUNTER = A.COUNTER
AND B.INV_NUM = A.INV_NUM
LEFT JOIN STOCK C
ON C.COM_ID = A.COM_ID
AND C.ITEM_CODE = B.ITEM_CODE
INNER JOIN WAREHOUSE S
ON S.COM_ID=A.COM_ID
AND S.WARE_CODE=A.SHOP_CODE
LEFT JOIN SHOP SH
ON SH.COM_ID=A.COM_ID
AND A.SHOP_CODE =SH.SHOP_CODE
LEFT JOIN STOREROOM SR
ON SR.COM_ID=A.COM_ID
AND SR.WARE_CODE=A.SHOP_CODE
AND SR.STOREROOM_CODE=A.WARE_DIST
LEFT JOIN dinv_coupon DC ON DC.INV_NUM=A.INV_NUM
WHERE (
A.ISDEPOSIT=0
AND A.VOIDED <> 1
)
GROUP BY
A.MEMBER_CODE,
A.INV_DATE,
A.ISDEPOSIT,
A.INV_NUM,
B.C_SHORT_DE,
B.BAR_CODE,
DC.serial_no,
CASE
WHEN A.ISDEPOSIT=1
THEN A.INV_DATE
ELSE NULL
END,A.INV_TYPE,
A.MEMBER_CODE,
S.WARE_CODE ,
SR.STOREROOM_CODE
ORDER BY
A.INV_DATE DESC
创建视图,及在视图里查询没问题,但是在C#一调用,就报ORA-22922 ,找了一些方法,包括用:
listagg(city,',') within GROUP (order by city) ,感觉多少有点问题,listagg列传行无分隔,也许我没理解透,项目着急先上能用的。
后来找到高手的代码参考复制,居然还能用,zh_concat列传行用:分隔,还凑合。
使用时,就把wm_concat用zh_concat替换了一下,就Ok了。
create or replace TYPE zh_concat_im
AUTHID CURRENT_USER AS OBJECT
(
CURR_STR VARCHAR2(32767),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
P1 IN VARCHAR2) RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im) RETURN NUMBER
);
/
create or replace TYPE BODY zh_concat_im
IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
RETURN NUMBER
IS
BEGIN
SCTX := zh_concat_im(NULL) ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
P1 IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF(CURR_STR IS NOT NULL) THEN
CURR_STR := CURR_STR || ':' || P1;
ELSE
CURR_STR := P1;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURNVALUE := CURR_STR ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
SCTX2 IN zh_concat_im)
RETURN NUMBER
IS
BEGIN
IF(SCTX2.CURR_STR IS NOT NULL) THEN
SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ;
END IF;
RETURN ODCICONST.SUCCESS;
END;
END;
/
函数:
create or replace FUNCTION zh_concat(P1 VARCHAR2)
RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;
/
参考自:
http://blog.csdn.net/cages/article/details/7457549
- ORA-22922 错误 wm_concat 函数的替代方案
- ORA:WMSYS.WM_CONCAT函数
- 自定义函数替代WMSYS.WM_CONCAT
- oralce函数wm_concat 替代办法
- DB2 没有函数索引的 替代方案
- oracle的wm_concat函数
- 替代UITabBarController的方案
- Spring的替代方案
- Spring的替代方案
- Spring的替代方案
- Spring的替代方案
- 被墙后的替代方案
- AsyncTask的替代方案
- Java 函数引用 替代方案
- WMSYS.WM_CONCAT 函数的用法
- wm_concat函数的排序问题
- oracle wm_concat函数的使用
- wm_concat函数的排序问题
- <工程数学>Alglib库,测试插值运算
- 游戏log格式的优化<<代替%d、%s
- 黑马程序员--反射总结--java
- 读书笔记:C++primer plus第十章
- 在android中画圆形图片的几种办法
- ORA-22922 错误 wm_concat 函数的替代方案
- jdk配置
- 读《数学之美》第四章 谈谈分词
- 内联函数,构造函数,静态函数都不能是虚函数
- 关于OC 语言中的协议
- storm单词计数 本地执行
- mysql/mariadb root user get ERROR 1045 (28000): Access denied without sudo
- samba service on ubuntu 14.10
- 程序员必须知道的10大基础实用算法及其讲解