以前代码在一个库里面,没有建数据连接,系统上线后,把数据分成两个库。
代码如下:
INSERT INTO WI.A_TARGET_AC_DATA_DAY@DBL_B_WI
(TIME_ID,
AREA_ID,
TARGET_ID,
CHG_AC_TYPE_ID,
WARN_COND,
WARN_VALUE,
TARGET_VALUE,
TARGET_LVALUE,
TARGET_LMVALUE,
LY_PLAN_VALUE,
CY_PLAN_VALUE)
SELECT 20100303,
VA.AREA,
'15',
A.CHG_AC_TYPE_ID,
B.WARN_COND,
B.VALUE_LIMIT,
COUNT(DISTINCT(A.CI_NO)),
NVL(D.TARGET_VALUE, 0),
NVL(E.TARGET_VALUE, 0),
NVL(F.PLAN_VAL, 0),
NVL(C.PLAN_VAL, 0)
FROM (((SELECT FH.CI_NO,
(CASE TX_MMO
WHEN 'NTP' THEN
'902'
WHEN 'CTP' THEN
'901'
ELSE
'903'
END) CHG_AC_TYPE_ID,
NVL(FH.CI_AREA_ID, '40') AREA_ID
FROM DW.M_FIN_HIS_D_201003 FH
WHERE FH.TX_MMO IN ('NTP', 'CTP', 'LOA')
AND FH.AC_DT = 20100303) A LEFT JOIN SJZF.V_AREA@DBL_B_SJZF VA ON
VA.AREA = A.AREA_ID AND VA.UPAREA <> VA.AREA and
va.UPAREA <> '999') LEFT JOIN WI.A_TARGET_WARNING@DBL_B_WI B ON
A.AREA_ID = B.AREA_ID AND B.TARGET_ID = '15' AND
B.TARGET_CYCLE = 'D' AND B.PTYPE = A.CHG_AC_TYPE_ID)
LEFT JOIN WI.A_TARGET_PLAN@DBL_B_WI C ON A.AREA_ID = C.AREA_ID
AND C.TARGET_ID = '15'
AND C.TIME_ID = 20100303
LEFT JOIN WI.A_TARGET_AC_DATA_DAY@DBL_B_WI D ON D.TIME_ID =
WI.GETDAY(20100303, -1)
AND D.AREA_ID = VA.AREA
AND D.TARGET_ID = '15'
AND A.CHG_AC_TYPE_ID =
D.CHG_AC_TYPE_ID
LEFT JOIN WI.A_TARGET_AC_DATA_DAY@DBL_B_WI E ON E.TIME_ID =
TO_CHAR(ADD_MONTHS(TO_DATE(20100303,
'YYYYMMDD'),
-1),
'YYYYMMDD')
AND E.AREA_ID = VA.AREA
AND E.TARGET_ID = '15'
AND A.CHG_AC_TYPE_ID =
E.CHG_AC_TYPE_ID
LEFT JOIN WI.A_TARGET_PLAN@DBL_B_WI F ON F.TIME_ID =
TO_CHAR(ADD_MONTHS(TO_DATE(20100303,
'YYYYMMDD'),
-12),
'YYYYMMDD')
AND F.AREA_ID = VA.AREA
AND F.TARGET_ID = '15'
GROUP BY C.TIME_ID,
VA.AREA,
A.CHG_AC_TYPE_ID,
B.WARN_COND,
B.VALUE_LIMIT,
NVL(D.TARGET_VALUE, 0),
NVL(E.TARGET_VALUE, 0),
NVL(F.PLAN_VAL, 0),
NVL(C.PLAN_VAL, 0)报错信息是:
ORA-02069: global_names parameter must be set to TRUE for this operation
代码如下:
INSERT INTO WI.A_TARGET_AC_DATA_DAY@DBL_B_WI
(TIME_ID,
AREA_ID,
TARGET_ID,
CHG_AC_TYPE_ID,
WARN_COND,
WARN_VALUE,
TARGET_VALUE,
TARGET_LVALUE,
TARGET_LMVALUE,
LY_PLAN_VALUE,
CY_PLAN_VALUE)
SELECT 20100303,
VA.AREA,
'15',
A.CHG_AC_TYPE_ID,
B.WARN_COND,
B.VALUE_LIMIT,
COUNT(DISTINCT(A.CI_NO)),
NVL(D.TARGET_VALUE, 0),
NVL(E.TARGET_VALUE, 0),
NVL(F.PLAN_VAL, 0),
NVL(C.PLAN_VAL, 0)
FROM (((SELECT FH.CI_NO,
(CASE TX_MMO
WHEN 'NTP' THEN
'902'
WHEN 'CTP' THEN
'901'
ELSE
'903'
END) CHG_AC_TYPE_ID,
NVL(FH.CI_AREA_ID, '40') AREA_ID
FROM DW.M_FIN_HIS_D_201003 FH
WHERE FH.TX_MMO IN ('NTP', 'CTP', 'LOA')
AND FH.AC_DT = 20100303) A LEFT JOIN SJZF.V_AREA@DBL_B_SJZF VA ON
VA.AREA = A.AREA_ID AND VA.UPAREA <> VA.AREA and
va.UPAREA <> '999') LEFT JOIN WI.A_TARGET_WARNING@DBL_B_WI B ON
A.AREA_ID = B.AREA_ID AND B.TARGET_ID = '15' AND
B.TARGET_CYCLE = 'D' AND B.PTYPE = A.CHG_AC_TYPE_ID)
LEFT JOIN WI.A_TARGET_PLAN@DBL_B_WI C ON A.AREA_ID = C.AREA_ID
AND C.TARGET_ID = '15'
AND C.TIME_ID = 20100303
LEFT JOIN WI.A_TARGET_AC_DATA_DAY@DBL_B_WI D ON D.TIME_ID =
WI.GETDAY(20100303, -1)
AND D.AREA_ID = VA.AREA
AND D.TARGET_ID = '15'
AND A.CHG_AC_TYPE_ID =
D.CHG_AC_TYPE_ID
LEFT JOIN WI.A_TARGET_AC_DATA_DAY@DBL_B_WI E ON E.TIME_ID =
TO_CHAR(ADD_MONTHS(TO_DATE(20100303,
'YYYYMMDD'),
-1),
'YYYYMMDD')
AND E.AREA_ID = VA.AREA
AND E.TARGET_ID = '15'
AND A.CHG_AC_TYPE_ID =
E.CHG_AC_TYPE_ID
LEFT JOIN WI.A_TARGET_PLAN@DBL_B_WI F ON F.TIME_ID =
TO_CHAR(ADD_MONTHS(TO_DATE(20100303,
'YYYYMMDD'),
-12),
'YYYYMMDD')
AND F.AREA_ID = VA.AREA
AND F.TARGET_ID = '15'
GROUP BY C.TIME_ID,
VA.AREA,
A.CHG_AC_TYPE_ID,
B.WARN_COND,
B.VALUE_LIMIT,
NVL(D.TARGET_VALUE, 0),
NVL(E.TARGET_VALUE, 0),
NVL(F.PLAN_VAL, 0),
NVL(C.PLAN_VAL, 0)报错信息是:
ORA-02069: global_names parameter must be set to TRUE for this operation
把参数设为true 看看。
ALTER SESSION SET GLOBAL_NAMES = TRUE;------------------------------------------------------------------------------
Blog: http://blog.csdn.net/tianlesoftware
网上资源: http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群:62697716