執行SQL指令後有一錯誤訊息,能否請教一下 |
尚未結案
|
Donna
一般會員 發表:3 回覆:0 積分:0 註冊:2003-05-27 發送簡訊給我 |
|
softworker
一般會員 發表:0 回覆:4 積分:0 註冊:2002-10-07 發送簡訊給我 |
|
roger128
初階會員 發表:27 回覆:45 積分:25 註冊:2002-08-29 發送簡訊給我 |
|
pgdennis
資深會員 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
|
roger128
初階會員 發表:27 回覆:45 積分:25 註冊:2002-08-29 發送簡訊給我 |
站長原諒我阿~~我不是故意的~
會不會問題出在 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:
|
pgdennis
資深會員 發表:41 回覆:526 積分:443 註冊:2002-05-23 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |