求救：存储过程SQL语句错误

最新推荐文章于 2021-07-22 10:30:49 发布

prof_wang

最新推荐文章于 2021-07-22 10:30:49 发布

阅读量535

点赞数

文章标签：存储 sql date sms user

本文链接：https://blog.csdn.net/prof_wang/article/details/549341

版权

,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

上面这句放在存储过程中要执行的字符串的select语句中存在错误，但是把上面放在select语句中，直接拿出来执行，是没有问题的。如

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

,case when months_between(to_date('200510','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))<=1 then
      b6.dim_id else bb6.dim_id end   as Create_Lev_Id


    ,nvl(b7.Dim_Id,9999)             as CallPay_Change_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 ='200510'
   and a1.msisdn=a2.msisdn(+)
   and a1.comp_net_type in (1001,3001,3002)

   and months_between(To_date('200510','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 a1.msisdn=b8.msisdn(+)
and a1.Sum_Month=b8.sum_month(+)

and b6.dim_code(+)=200023
and (to_date('20051020','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('20051020','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('200510','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('200510','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
--如果recall_time是空的话就取first_call_time，否则取recall_time

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 用于处理除零