,case when months_between(to_date('|| CUR_MONTH ||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 then
b6.dim_id else bb6.dim_id end as Create_Lev_Id
上面语句存在问题,注释后存储过程可以执行,否则提示-6502ORA-06502: PL/SQL: 数字或值错误
Str := 'CREATE TABLE Temp_F_Cmp_Sms_Analysis_M1 Nologging As
Select /*+full(a1)full(a2)full(b2) full(b3) full(b4) full(b5) full(b6) full(bb6) full(b7) full(b8)*/
a1.msisdn as msisdn
,a1.Sum_Month as Month_Id
,case nvl(a1.home_county,9999) when 595 then 500 else nvl(a1.home_county,9999) end as Home_Area_Code
,nvl(a1.comp_net_type,9999) as Trader_Type_Id
,case when nvl(a1.To_GoTone_AMT,0) >0 then 1 else 0 end as To_GoTone_Cnt
,case when nvl(a1.To_M_Zone_AMT,0) >0 then 1 else 0 end as To_M_Zone_Cnt
,case when nvl(a1.To_Pop_AMT,0) >0 then 1 else 0 end as To_Pop_Cnt
,case when nvl(a1.GoTone_To_AMT,0) >0 then 1 else 0 end as GoTone_To_Cnt
,case when nvl(a1.M_Zone_To_AMT,0) >0 then 1 else 0 end as M_Zone_To_Cnt
,case when nvl(a1.Pop_To_AMT,0) >0 then 1 else 0 end as Pop_To_Cnt
,nvl(a1.Sms_Pcall_Cnt,0) as Sms_Pcall_Cnt
,nvl(a1.Sms_Acall_Cnt,0) as Sms_Acall_Cnt
,nvl(b2.Dim_Id,9999) as To_Mcc_Send_Id
,nvl(b3.Dim_Id,9999) as To_Com_Send_Id
,nvl(b4.Dim_Id,9999) as Cur_Send_Id
,nvl(b5.Dim_Id,9999) as Last_Send_Id
,nvl(b7.Dim_Id,9999) as CallPay_Change_Id
,case when (months_between(to_date('|| CUR_MONTH ||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 )
then b6.dim_id else bb6.dim_id end as Create_Lev_Id
from
Temp_FT_MID_COMP_SMS_MONTH a1,
Temp_FT_MID_COMP_SMS_MONTH a2,
qzbase.pre_cfg_dim_value b2,
qzbase.pre_cfg_dim_value b3,
qzbase.pre_cfg_dim_value b4,
qzbase.pre_cfg_dim_value b5,
qzbase.pre_cfg_dim_value b6,
qzbase.pre_cfg_dim_value bb6,
qzbase.pre_cfg_dim_value b7,
Temp_Ft_mid_comp_user b8
where
a1.Sum_Month ='|| CUR_MONTH ||'
and a1.msisdn=a2.msisdn(+)
and a1.comp_net_type in (1001,3001,3002)
--and add_months(To_Date(a2.Sum_Month(+) ,''yyyymm'') ,-1) = add_months(To_date(' || CUR_MONTH || ',''yyyymm''),-2)
and months_between(To_date(' || CUR_MONTH || ',''yyyymm''),To_Date(a2.Sum_Month(+) ,''yyyymm'') )=1
and b2.dim_code(+)=200004
and a1.Sms_Pcall_Cnt between b2.min_value(+) and b2.max_value(+)-1
and b3.dim_code(+)=200004
and a1.Sms_Acall_Cnt between b3.min_value(+) and b3.max_value(+)-1
and b4.dim_code(+)=200004
and nvl(a1.Sms_Acall_Cnt,0) +nvl(a1.Sms_Pcall_Cnt ,0) between b4.min_value(+) and b4.max_value(+)-1
and b5.dim_code(+)=200004
and nvl(a2.Sms_Acall_Cnt,0) +nvl(a2.Sms_Pcall_Cnt ,0) between b5.min_value(+) and b5.max_value(+)-1
--如果recall_time是空的话就取first_call_time,否则取recall_time
and b6.dim_code(+)=200023
and (to_date('||Stat_Date||',''yyyymmdd'')-(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMMDD''),''YYYYMMDD'')
else trunc(b8.recall_time,''dd'') end))>=b6.min_value(+)
and (to_date('||Stat_Date||',''YYYYMMDD'')-(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMMDD''),''YYYYMMDD'')
else trunc(b8.recall_time,''DD'') end))< b6.max_value(+)
and b6.dim_code(+)<1063
and bb6.dim_code(+)=200023
and months_between(to_date('||CUR_MONTH||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else trunc(b8.recall_time,''dd'') end))>=bb6.min_value(+)
and months_between(to_date('||CUR_MONTH||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else trunc(b8.recall_time,''dd'') end))<bb6.max_value(+)
and bb6.dim_code(+)>1063
and b7.dim_code(+)=200011
and ( ( nvl(a1.Sms_Acall_Cnt,0) +nvl(a1.Sms_Pcall_Cnt ,0)
-nvl(a2.Sms_Acall_Cnt,0)-nvl(a2.Sms_Pcall_Cnt,0))
/(nvl(a2.Sms_Acall_Cnt,0) +nvl(a2.Sms_Pcall_Cnt,0)+0.00000001))*100
between b7.min_value and b7.max_value-1
--加上0.00000001 用于处理除零
'; Execute Immediate Str;
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 then
b6.dim_id else bb6.dim_id end as Create_Lev_Id
上面语句存在问题,注释后存储过程可以执行,否则提示-6502ORA-06502: PL/SQL: 数字或值错误
Str := 'CREATE TABLE Temp_F_Cmp_Sms_Analysis_M1 Nologging As
Select /*+full(a1)full(a2)full(b2) full(b3) full(b4) full(b5) full(b6) full(bb6) full(b7) full(b8)*/
a1.msisdn as msisdn
,a1.Sum_Month as Month_Id
,case nvl(a1.home_county,9999) when 595 then 500 else nvl(a1.home_county,9999) end as Home_Area_Code
,nvl(a1.comp_net_type,9999) as Trader_Type_Id
,case when nvl(a1.To_GoTone_AMT,0) >0 then 1 else 0 end as To_GoTone_Cnt
,case when nvl(a1.To_M_Zone_AMT,0) >0 then 1 else 0 end as To_M_Zone_Cnt
,case when nvl(a1.To_Pop_AMT,0) >0 then 1 else 0 end as To_Pop_Cnt
,case when nvl(a1.GoTone_To_AMT,0) >0 then 1 else 0 end as GoTone_To_Cnt
,case when nvl(a1.M_Zone_To_AMT,0) >0 then 1 else 0 end as M_Zone_To_Cnt
,case when nvl(a1.Pop_To_AMT,0) >0 then 1 else 0 end as Pop_To_Cnt
,nvl(a1.Sms_Pcall_Cnt,0) as Sms_Pcall_Cnt
,nvl(a1.Sms_Acall_Cnt,0) as Sms_Acall_Cnt
,nvl(b2.Dim_Id,9999) as To_Mcc_Send_Id
,nvl(b3.Dim_Id,9999) as To_Com_Send_Id
,nvl(b4.Dim_Id,9999) as Cur_Send_Id
,nvl(b5.Dim_Id,9999) as Last_Send_Id
,nvl(b7.Dim_Id,9999) as CallPay_Change_Id
,case when (months_between(to_date('|| CUR_MONTH ||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 )
then b6.dim_id else bb6.dim_id end as Create_Lev_Id
from
Temp_FT_MID_COMP_SMS_MONTH a1,
Temp_FT_MID_COMP_SMS_MONTH a2,
qzbase.pre_cfg_dim_value b2,
qzbase.pre_cfg_dim_value b3,
qzbase.pre_cfg_dim_value b4,
qzbase.pre_cfg_dim_value b5,
qzbase.pre_cfg_dim_value b6,
qzbase.pre_cfg_dim_value bb6,
qzbase.pre_cfg_dim_value b7,
Temp_Ft_mid_comp_user b8
where
a1.Sum_Month ='|| CUR_MONTH ||'
and a1.msisdn=a2.msisdn(+)
and a1.comp_net_type in (1001,3001,3002)
--and add_months(To_Date(a2.Sum_Month(+) ,''yyyymm'') ,-1) = add_months(To_date(' || CUR_MONTH || ',''yyyymm''),-2)
and months_between(To_date(' || CUR_MONTH || ',''yyyymm''),To_Date(a2.Sum_Month(+) ,''yyyymm'') )=1
and b2.dim_code(+)=200004
and a1.Sms_Pcall_Cnt between b2.min_value(+) and b2.max_value(+)-1
and b3.dim_code(+)=200004
and a1.Sms_Acall_Cnt between b3.min_value(+) and b3.max_value(+)-1
and b4.dim_code(+)=200004
and nvl(a1.Sms_Acall_Cnt,0) +nvl(a1.Sms_Pcall_Cnt ,0) between b4.min_value(+) and b4.max_value(+)-1
and b5.dim_code(+)=200004
and nvl(a2.Sms_Acall_Cnt,0) +nvl(a2.Sms_Pcall_Cnt ,0) between b5.min_value(+) and b5.max_value(+)-1
--如果recall_time是空的话就取first_call_time,否则取recall_time
and b6.dim_code(+)=200023
and (to_date('||Stat_Date||',''yyyymmdd'')-(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMMDD''),''YYYYMMDD'')
else trunc(b8.recall_time,''dd'') end))>=b6.min_value(+)
and (to_date('||Stat_Date||',''YYYYMMDD'')-(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMMDD''),''YYYYMMDD'')
else trunc(b8.recall_time,''DD'') end))< b6.max_value(+)
and b6.dim_code(+)<1063
and bb6.dim_code(+)=200023
and months_between(to_date('||CUR_MONTH||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else trunc(b8.recall_time,''dd'') end))>=bb6.min_value(+)
and months_between(to_date('||CUR_MONTH||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else trunc(b8.recall_time,''dd'') end))<bb6.max_value(+)
and bb6.dim_code(+)>1063
and b7.dim_code(+)=200011
and ( ( nvl(a1.Sms_Acall_Cnt,0) +nvl(a1.Sms_Pcall_Cnt ,0)
-nvl(a2.Sms_Acall_Cnt,0)-nvl(a2.Sms_Pcall_Cnt,0))
/(nvl(a2.Sms_Acall_Cnt,0) +nvl(a2.Sms_Pcall_Cnt,0)+0.00000001))*100
between b7.min_value and b7.max_value-1
--加上0.00000001 用于处理除零
'; Execute Immediate Str;
解决方案 »
- 请教高手一个SQL语句
- ORACLE 8.1在执行存储过程中,执行到最后就超时了,请教高手
- 调用存储过程出错了,这是什么错误?
- 每隔0.5秒从服务器数据库的TabA表中提数据刷新客户端界面上的MSHFlexGrid1控件。有24个客户端,TabA表中的记录由于各个客户端的操作而频繁变动,变动的范围为0~2000条左右记录,请审查这个方案是否可行。见主要代码:
- bdump没有"分布式 死锁信息"
- update问题何在
- PL/SQL DEVELOPER 不能登录是怎么回事呀?在SQL PLUS中是可以的呀,请高手赐教!
- 我编写触发器,怎么老是提示我没有权限呢?
- 一个存储过程,本身就是一个事物吗?
- 求一北京oracleDBA的职位
- 更新表SQL语句
- 在oracle中如何取两个日期的天数差?
(case when months_between(to_date('|| CUR_MONTH ||',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'')
when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2
then b6.dim_id
else bb6.dim_id
end) as Create_Lev_Id
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 then
b6.dim_id else bb6.dim_id end as Create_Lev_Id
改为
,case when months_between(to_date('''|| CUR_MONTH ||''',''YYYYMM''),
(case nvl(to_char(b8.recall_time),''0'') when ''0'' then to_date(to_char(b8.first_call_time,''YYYYMM''),''YYYYMM'')
else TO_DATE(TO_CHAR(b8.recall_time,''YYYYMM''),''YYYYMM'') end))<=2 then
b6.dim_id else bb6.dim_id end as Create_Lev_Id