伺服器: 訊息 8115，層級 16，狀態 5，行 177 Arithmetic overflow error converting numeric to data type varchar. The statement has been terminated. 但我的第177行為blank...不懂....哪錯了呢？ 發表人 - Donna 於 2003/06/05 18:54:55

把你的script 貼來看看

是你的SQL 177行 有錯誤ㄇ~~^o^~~ 猜的 應該是你的ORACLE 型態存取錯誤ㄅ 可以方便傳你的SQL SCRIPT 上來ㄇ 別嚇人ㄋ 有一百多行ㄌ 上次看過一ㄍSQL 足足寫了九十幾行 到現在陰影還在~~嚇人了

小姐.你貼一堆程式碼上來...卻沒有把最重要的177行指出來... 建議你先將 > 永遠追不上技術更新的速度~~< src="http://140.134.4.20/~g8905202/pucca.gif">

------
星期一,二...無窮迴圈＠＠

站長原諒我阿~~我不是故意的~ 會不會問題出在 DMSD422,DMSD432 跟你create table 不一樣 1: set nocount on 2: declare @sys_date char(8),@ten_date char(8), @sys_time char(8) , @systime char(6) 3: set @sys_date=convert(char(8),getdate(),112) 4: set @ten_date=convert(char(8),getdate()-10,112) 5: create table ##delivery_mail(form char(100)) --存放國內普限包裏暫存表(投遞) 6: create table ##delivery_express(form char(100)) --存放國內快捷暫存表(投遞) 7: create table ##delivery_dhl(form char(100)) --存放國際快捷暫存表(投遞) 8: create table #data1(cntr_no char(1),tour_no numeric(2),mail_no_srl char(1),mail_no varchar(20),proc_date char(7),proc_time char(6),dlv_date char(7),dlv_time char(6),acc_date char(7), 9: hndl_state char(1),undlv_id char(2),brh_no char(7),segc_no char(7),mail_state char(2),dlv_state char(2),snd_mark char(1),mlst_no char(11)) 10: create table #data2(cntr_no char(1),tour_no numeric(2),mail_no_srl char(1),mail_no varchar(20),proc_date char(7),proc_time char(6),dlv_date char(7),dlv_time char(6),acc_date char(7), 11: hndl_state char(1),undlv_id char(2),brh_no char(7),segc_no char(7),mail_state char(2),dlv_state char(2),snd_mark char(1),mlst_no char(11), dlvoff_no char(6), 12: mail_spc_type char(1), mail_amt char(6)) 13: declare @officecode char(6), @officecode_t char(6),@date1 char(8),@date2 char(8),@upload_date char(14),@flag1 char(1),@flag2 char(1),@flag3 char(1),@time1 char(6) 14: declare @tel_no char (18),@back_reason char(2),@back_id char(1),@inform_date char(12),@stay_reason char(1),@sign_name char(17),@tendate char(7), @sysdate_1 char(7) 15: declare @mail_spc_type char (1),@mail_amt char(6),@mail_amt_a char(6),@post_no char(5) 16: 17: set @tel_no='' ---招領局電話號碼 18: set @back_reason='' ---退件原因 19: set @back_id='' ---退回局代碼 20: set @inform_date='' ---通知日期時間 21: set @stay_reason='' ---留局原因 22: set @sign_name='' ---簽收人姓名 23: set @mail_amt_a='' ---報值保價或代收貨價金額 24: set @post_no=' ' ---信箱號碼 25: set @upload_date='00000000000000' --上傳日期時間設為0 26: set @flag1='0' --判斷是否有國內普限包裏資料輸出TXT 27: set @flag2='0' --判斷是否有國內快捷資料輸出TXT 28: set @flag3='0' --判斷是否有國際快捷資料輸出TXT 29: select @officecode_t= OFFICE_CODE from BSC_SYSINF --作業局號 30: set @officecode=@officecode_t 31: 32: if substring(@sys_date,1,4) < '2011' 33: set @sysdate_1='0' cast(cast(@sys_date as numeric)-19110000 as char(6)) 34: else 35: set @sysdate_1=cast(cast(@sys_date as numeric)-19110000 as char(7)) 36: 37: 38: set @sys_time=convert(char(8),getdate(),108) 39: set @systime = substring(@sys_time, 1, 2) substring(@sys_time, 4, 2) substring(@sys_time, 7, 2) 40: if substring(@ten_date,1,4) < '2011' 41: set @tendate='0' cast(cast(@ten_date as numeric)-19110000 as char(6)) 42: else 43: set @tendate=cast(cast(@ten_date as numeric)-19110000 as char(7)) 44: 45: 46: declare @cntr_no char(1),@tour_no numeric(2),@mail_no_srl char(1),@mail_no varchar(20),@proc_date char(7),@proc_time char(6),@dlv_date char(7),@dlv_time char(6),@acc_date char(7) 47: declare @hndl_state char(1),@undlv_id char(2),@brh_no char(7),@segc_no char(7),@mail_state char(2),@dlv_state char(2),@snd_mark char(1),@mlst_no char(11),@dlvoff_no char(6) 48: declare @rec char(1),@format char(100),@format_a char(100),@require char(62),@mail_final char(20),@office_no char(6) 49: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 50: ------------登錄種類=1 OR 2 且清單狀態=Y 上傳主機註記=N 依郵件狀態轉出EVENT 51: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 52: insert into #data1 53: select CNTR_NO,DMSD422.TOUR_NO,MAIL_NO_SRL,MAIL_NO,DMSD422.PROC_DATE,DMSD422.PROC_TIME,DLV_DATE,DLV_TIME,ACC_DATE, 54: HNDL_STATE,UNDLV_ID,BRHDLV_NO,SEGC_NO,MAIL_STATE,DLV_STATE,SND_HOST_MARK,MLST_NO 55: 56: from DMSD422,DMSD432 57: where @tendate<=DMSD422.PROC_DATE 58: and CNTR_NO_D430=CNTR_NO 59: and ACC_DATE_D430=ACC_DATE 60: and SEGC_NO_D430=SEGC_NO 61: and MLST_NO_D430=MLST_NO 62: and DMSD432.TOUR_NO=DMSD422.TOUR_NO 63: and (TRF_TYPE='1' or TRF_TYPE='2') 64: and MLST_STUS='Y' 65: and SND_HOST_MARK='N' 66: 67: insert into ##delivery_mail (form) 68: values ('') 69: insert into ##delivery_express (form) 70: values ('') 71: insert into ##delivery_dhl (form) 72: values ('') 73: 74: declare delivery1 cursor local read_only for 75: select * from #data1 76: 77: open delivery1 78: fetch delivery1 into @cntr_no,@tour_no,@mail_no_srl,@mail_no,@proc_date,@proc_time,@dlv_date,@dlv_time,@acc_date,@hndl_state, 79: @undlv_id,@brh_no,@segc_no,@mail_state,@dlv_state,@snd_mark,@mlst_no 80: 81: while @@fetch_status=0 82: begin -----依郵件狀態轉出EVENT 83: set @mail_no=rtrim(@mail_no) 84: exec sp_mail_class @mail_no,@rec output --input 郵件號碼 output: '0' , '1' , '2' , '3' 85: if @rec='1' --國內普限包裏 86: begin 87: set @date1=cast(cast(@proc_date as numeric) 19110000 as char(8)) --處理日期時間(登錄日期時間) 轉成西元格式YYYYMMDD 88: set @date2=cast(cast(@acc_date as numeric) 19110000 as char(8)) --帳務日期 轉成西元格式 89: if @mail_state in ('Z1','Z2','Z3') 90: begin 91: set @office_no='' 92: select @office_no=isnull(OFFICE_NO,'') from BSC_DLV_SSEG 93: where SEC_NO=substring(@segc_no,2,6) 94: and ( SEG_NO='4' OR SEG_NO='5' OR SEG_NO='6') 95: end 96: set @mail_final=@mail_no 97: set @require=@officecode @mail_final @date1 @proc_time @upload_date @date2 98: set @format=case 99: when @mail_state='P2' then '067P4' @require 100: when @mail_state='T4' then '070T4' @require @back_reason @back_id 101: when @mail_state='W2' then '080W2' @require @inform_date @stay_reason 102: when @mail_state='X2' then '067X2' @require 103: when @mail_state='Z1' then '084Z1' @require @office_no @mlst_no 104: when @mail_state='Z2' then '084Z2' @require @office_no @mlst_no 105: when @mail_state='Z3' then '084Z2' @require @office_no @mlst_no 106: -- when @mail_state='Z5' then '084Z5' @require @office_no @mlst_no 107: else '' 108: end 109: if @format<>'' 110: begin 111: set @flag1='1' 112: insert into ##delivery_mail (form) 113: values (@format) 114: update DMSD422 115: set SND_HOST_MARK='X', SND_HOST_DATE = @sysdate_1, SND_HOST_TIME = @systime 116: where CNTR_NO=@cntr_no 117: and ACC_DATE=@acc_date 118: and DMSD422.TOUR_NO=@tour_no 119: and SEGC_NO=@segc_no 120: and MLST_NO=@mlst_no 121: and MAIL_NO=@mail_no 122: and MAIL_NO_SRL=@mail_no_srl 123: end 124: end 125: if @rec='2' --國內快捷 126: begin 127: set @date1=cast(cast(@proc_date as numeric) 19110000 as char(8)) --處理日期時間(登錄日期時間) 轉成西元格式 128: set @date2=cast(cast(@acc_date as numeric) 19110000 as char(8)) --帳務日期 轉成西元格式 129: if @mail_state in ('Z1','Z2','Z3') 130: begin 131: set @office_no='' 132: select @office_no=isnull(OFFICE_NO,'') from BSC_DLV_SSEG 133: where SEC_NO=substring(@segc_no,2,6) 134: and ( SEG_NO='4' OR SEG_NO='5' OR SEG_NO='6') 135: end 136: set @mail_final=@mail_no 137: set @require=@officecode @mail_final @date1 @proc_time @upload_date @date2 138: set @format=case 139: when @mail_state='P2' then '067P4' @require 140: when @mail_state='T4' then '070T4' @require @back_reason @back_id 141: when @mail_state='W2' then '080W2' @require @inform_date @stay_reason 142: when @mail_state='X2' then '067X2' @require 143: when @mail_state='Z1' then '084Z1' @require @office_no @mlst_no 144: when @mail_state='Z2' then '084Z2' @require @office_no @mlst_no 145: when @mail_state='Z3' then '084Z2' @require @office_no @mlst_no 146: when @mail_state='Y2' then '067Y4' @require 147: -- when @mail_state='Z5' then '084Z5' @require @office_no @mlst_no 148: else '' 149: end 150: if @format<>'' 151: begin 152: set @flag2='1' 153: insert into ##delivery_express (form) 154: values (@format) 155: update DMSD422 156: set SND_HOST_MARK='X', SND_HOST_DATE = @sysdate_1, SND_HOST_TIME = @systime 157: where CNTR_NO=@cntr_no 158: and ACC_DATE=@acc_date 159: and DMSD422.TOUR_NO=@tour_no 160: and SEGC_NO=@segc_no 161: and MLST_NO=@mlst_no 162: and MAIL_NO=@mail_no 163: and MAIL_NO_SRL=@mail_no_srl 164: end 165: end 166: --------------國際快捷無郵件狀態EVENT轉出 167: fetch delivery1 into @cntr_no,@tour_no,@mail_no_srl,@mail_no,@proc_date,@proc_time,@dlv_date,@dlv_time,@acc_date,@hndl_state, 168: @undlv_id,@brh_no,@segc_no,@mail_state,@dlv_state,@snd_mark,@mlst_no 169: end 170: close delivery1 171: deallocate delivery1 172: ------------------------------------------------------------------------------------------------------------------------------ 173: ------------登錄種類=3 OR 4 且清單狀態=Y 上傳主機註記=N 依郵件狀態,妥投狀態轉出EVENT 174: ------------ X 依妥投狀態轉出EVENT 175: ------------------------------------------------------------------------------------------------------------------------------ 176: 177: insert into #data2 (CNTR_NO,TOUR_NO,MAIL_NO_SRL,MAIL_NO,PROC_DATE,PROC_TIME,DLV_DATE,DLV_TIME,ACC_DATE, 178: HNDL_STATE,UNDLV_ID,BRH_NO,SEGC_NO,MAIL_STATE,DLV_STATE,SND_MARK,MLST_NO, MAIL_SPC_TYPE, MAIL_AMT) 179: select CNTR_NO,DMSD422.TOUR_NO,MAIL_NO_SRL,MAIL_NO,DMSD422.PROC_DATE,DMSD422.PROC_TIME,DLV_DATE,DLV_TIME,ACC_DATE, 180: HNDL_STATE,UNDLV_ID,BRHDLV_NO,SEGC_NO,MAIL_STATE,DLV_STATE,SND_HOST_MARK,MLST_NO, MAIL_SPC_TYPE, MAIL_AMT 181: from DMSD422,DMSD432 182: where ((DMSD422. DLV_DATE is null and @tendate<=DMSD422.PROC_DATE) or ( DMSD422.DLV_DATE is not null and @tendate<=DMSD422.DLV_DATE)) 183: and CNTR_NO_D430=CNTR_NO 184: and ACC_DATE_D430=ACC_DATE 185: and SEGC_NO_D430=SEGC_NO 186: and MLST_NO_D430=MLST_NO 187: and DMSD432.TOUR_NO=DMSD422.TOUR_NO 188: and (TRF_TYPE='3' or TRF_TYPE='4') 189: and MLST_STUS='Y' 190: and (SND_HOST_MARK='N' or SND_HOST_MARK='X') 191:

177: 你先檢查 insert into (columns) 及 select (columns) 這兩個的columns 是否有型態不一至,或順序不對. 後面的where 檢查你在比對時 有沒有將不同型態的欄位相比較 永遠追不上技術更新的速度~~< src="http://140.134.4.20/~g8905202/pucca.gif">

------
星期一,二...無窮迴圈＠＠