帮助摆脱查询中的临时表。 [英] Help in getting rid of temp tables in a query.
本文介绍了帮助摆脱查询中的临时表。的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
全部，
我有以下查询，它使用多个临时表：
IF OBJECT_ID （' RPTUSR.SP_Track_Record2'） IS NOT NULL
BEGIN
< span class =code-keyword> DROP PROCEDURE RPTUSR.SP_Track_Record2
IF OBJECT_ID （' RPTUSR.SP_Track_Record2' ） IS NOT NULL
PRINT ' <<<失败的删除程序RPTUSR.SP_Track_Record2>>>'
ELSE
PRINT ' <<<删除程序RPTUSR.SP_Track_Record2>>>'
END
go
CREATE PROCEDURE SP_Track_Record2
AS
BEGIN
set compatibility_mode on
选择 a.POLICY_NO，a.CERTIFICATE_NO，a.RENL_CERT_NO，a.EFF_DT_SEQ_NO，
a .remarks_txt_id，max（b.text_seq_no）text_seq_no，
text_seq_no1 = case 何时 count（*）> 1 然后 max（b.text_seq_no）-1 else 0 end ，
text_seq_no2 = case 当 count（*）> 2 然后 max（b.text_seq_no）-2 else 0 end ，
text_seq_no3 = case 当 count（*）> 3 然后 max（b.text_seq_no） - 3 else 0 end ，
text_eff_date
into #GetRemarkSeqNo
来自
AUM_Reporting..Tpolicy a noholdlock，
AUM_Reporting..ttext b noholdlock，
G3 .. Track_Record_REPORT dd（ index sla_policy_indx）noholdlock
其中
- dd.accounting_period ='2010年7月31日'
< span class =code-comment> - b.text_type_cd =4and - （由amol041010评论）
b.text_id = a.remarks_txt_id
AND dd.POLICY_NO = a.POLICY_NO
AND dd.CERTIFICATE_NO = a.CERTIFICATE_NO
AND dd.RENL_CERT_NO = a.RENL_CERT_NO
AND dd.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
和 convert （ smalldatetime ， convert （ varchar ，text_eff_date，< span class =code-digit> 111 ））> = convert （ smalldatetime ， convert （ varchar ，dd.WIP_CREATION_DT， 111 ））
和 a.pol_office_cd = ' 064001'
group by a.POLICY_NO，a.CERTIFICATE_NO，a.RENL_CERT_NO，a.EFF_DT_SEQ_NO，a.remarks_txt_id，b.text_eff_date
- - 47045行受影响。
创建 INDE X WIP3in1_GetRemarkSeqNo ON #GetRemarkSeqNo（remarks_txt_id，text_seq_no，text_seq_no1，text_seq_no2，text_seq_no3）
- drop table＃GetRemark3
SELECT ＃getremarkseqno.POLICY_NO，＃getremarkseqno.CERTIFICATE_NO，＃getremarkseqno.RENL_CERT_NO，＃getremarkseqno.EFF_DT_SEQ_NO，a.text_detail AS User_Remarks，a.text_type_cd ，＃getremarkseqno.text_eff_date
into #GetRemark
FROM ＃getremarkseqno，AUM_Reporting..ttext
其中＃getremarkseqno.remarks_txt_id = a.text_id
AND #getremarkseqno.text_seq_no = a.text_seq_no
- 和a.text_type_cd =4
- CREATE INDEX WIP3in1_GetRemark ON #GetRemark（WIP_NO）
- GetRemark
- GetRemark1
SELECT ＃getremarkseqno.POLICY_NO，＃getremarkseqno.CERTIFICATE_NO，＃getremarkseqno.RENL_CERT_NO，＃getremarkseqno.EFF_DT_SEQ_NO，a.text_detail AS User_Remarks1，a.text_type_cd，＃getremarkseqno.text_eff_date
into #GetRemark1
FROM #getremarkseqno ，AUM_Reporting..ttext
其中＃getremarkseqno.text_seq_no1 = a.text_seq_no
AND ＃getremarkseqno.remarks_txt_id = a.text_id
- 和a.text_type_cd =4
- CREATE INDEX WIP3in1_GetRemark1 ON＃GetRemark1（WIP_NO ）
- GetRemark1
- GetRemark2
< span class =code-keyword> SELECT ＃getremarkseqno.POLICY_NO，＃getremarkseqno.CERTIFICATE_NO，＃getremarkseqno.RENL_CERT_NO，＃getremarkseqno.EFF_DT_SEQ_NO，a.text_detail AS User_Remarks2，a.text_type_cd，＃getremarkseqno.text_eff_dat e
into #GetRemark2
FROM ＃ getremarkseqno，AUM_Reporting..ttext
其中＃getremarkseqno.text_seq_no2 = a.text_seq_no
AND #getremarkseqno.remarks_txt_id = a.text_id
- 和a.text_type_cd =4
- CREATE INDEX WIP3in1_GetRemark2 ON＃GetRemark2（WIP_NO）
- GetRemark2
- < span class =code-comment> UPDATE G3..Track_Record_REPORT
- SET User_Remarks = b.User_Remarks
/ * Adaptive Server扩展了以下语句中的所有'*'元素* / 选择 a.policy_no，a.certificate_no，a .renl_cert_no，a.eff_dt_seq_no，a.branch_off_cd，a.prdr_branch_sub_cd，a.Branch，a.product_name，a.major_line_cd，a.ProductGroup，a.pol_incept_date，a.pol_exp_date，a.ins_name_client_no，a.ClientName，a.Client_city_name ，a.pol_status_cd，a.record_type_desc，a.policy_counter，a.accounting_period，a.ProducerName，a.PremiumAmount，a.o_prem_amt，a.SUM_INSURED，a.record_type_cd，a.producer_cd，a.EntryDate，a.EntryMonth，a .EntryDay，a.EntryWeek，a.InceptionMonth，a.InceptionDay，a.InceptionWeek，a.CoverNoteNumber，a.Cover_Note_date，a.Cov_nt_iss_month，a.Application_no，a。 TIMESTAMP ，a.watts_policy_no，a.Producer_type，a.times_renewed_cnt，a.Producer_Category，a .receipt_date，a.rcpt_branch_cd，a.rcpt_branch_sub_cd，a.receipt_no，a.product_cd，a.new_producer_cd，a.watts_issue_dt，a.Channel，a.prem_eff_date，a.orig_curr_cd，a.LOB，a.devel_det，a.PIN ，a.WIP_NO，a.POL_TYPE，a.APPLICATION_RECEIVED_DT，a.WIP_CREATION_DT，a.POL_PRINT_DT，a.POL_DELIVERY_DT，a.QC_STATUS_CD，a.QC_REMARK_ID，a.QC_USERID_CD，a.QC_TIMESTAMP，a.QC_TIMES_REJECT，a.pol_chnge_eff_date，a .tpol_rcpt_dets，a.REFUNDEE_ID，b.User_Remarks，b.text_eff_date into #Track_Record_report
FROM #GetRemark B，G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
- 和accounting_period ='2010年7月31日'
- 更新G3..Track_Record_REPORT
- SET User_Remarks1 = b.User_Remarks1
/ * Adaptive Server扩展了所有' *'以下语句中的元素* / 插入 进入 #Track_Record_report
选择 a.policy_no，a.certificate_no，a.renl_cert_no，a.eff_dt_seq_no，a.branch_off_cd，a.prdr_branch_sub_cd，a.Branch，a.product_name， a.major_line_cd，a.ProductGroup，a.pol_incept_date，a.pol_exp_date，a.ins_name_client_no，a.ClientNam e，a.Client_city_name，a.pol_status_cd，a.record_type_desc，a.policy_counter，a.accounting_period，a.ProducerName，a.PremiumAmount，a.o_prem_amt，a.SUM_INSURED，a.record_type_cd，a.producer_cd，a.EntryDate， a.EntryMonth，a.EntryDay，a.EntryWeek，a.InceptionMonth，a.InceptionDay，a.InceptionWeek，a.CoverNoteNumber，a.Cover_Note_date，a.Cov_nt_iss_month，a.Application_no，a。 TIMESTAMP ，a.watts_policy_no，a.Producer_type，a.times_renewed_cnt，a.Producer_Category，a.receipt_date，a.rcpt_branch_cd，a.rcpt_branch_sub_cd，a.receipt_no，a.product_cd，a.new_producer_cd，a。 watts_issue_dt，a.Channel，a.prem_eff_date，a.orig_curr_cd，a.LOB，a.devel_det，a.PIN，a.WIP_NO，a.POL_TYPE，a.APPLICATION_RECEIVED_DT，a.WIP_CREATION_DT，a.POL_PRINT_DT，a.POL_DELIVERY_DT， a.QC_STATUS_CD，a.QC_REMARK_ID，a.QC_USERID_CD，a.QC_TIMESTAMP，a.QC_TIMES_REJECT，a.pol_chnge_eff_date，a.tpol_rcpt_dets，a.REFUNDEE_ID，b.User_Remarks1，b.tex t_eff_date
FROM #GetRemark1 B，G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
- 和accounting_period ='2010年7月31日'
- 更新G3..Track_Record_REPORT
- SET User_Remarks2 = b.User_Remarks2
/ * A daptive Server已在以下语句中扩展了所有'*'元素* / insert 进入＃ Track_Record_report
选择 a.policy_no，a.certificate_no，a.renl_cert_no，a.eff_dt_seq_no，a.branch_off_cd，a.prdr_branch_sub_cd，a。 Branch，a.product_name，a.major_line_cd，a.ProductGroup，a.pol_incept_date，a.pol_exp_date，a.ins_name_client_no，a.ClientName，a.Client_city_name，a.pol_status_cd，a.record_type_desc，a.policy_counter，a.accounting_period， a.ProducerName，a.PremiumAmount，a.o_prem_amt，a.SUM_INSURED，a.record_type_cd，a.producer_cd，a.EntryDate，a.EntryMonth，a.EntryDay，a.EntryWeek，a.InceptionMonth，a.InceptionDay，a。 InceptionWeek，a.CoverNoteNumber，a.Cover_Note_date，a.Cov_nt_iss_month，a.Application_no，a。 TIMESTAMP ，a.watts_policy_no，a.Producer_type，a.times_renewed_cnt，a。 Producer_Category，a.receipt_date，a.rcpt_branch_cd ，a.rcpt_branch_sub_cd，a.receipt_no，a.product_cd，a.new_producer_cd，a.watts_issue_dt，a.Channel，a.prem_eff_date，a.orig_curr_cd，a.LOB，a.devel_det，a.PIN，a.WIP_NO，a .POL_TYPE，a.APPLICATION_RECEIVED_DT，a.WIP_CREATION_DT，a.POL_PRINT_DT，a.POL_DELIVERY_DT，a.QC_STATUS_CD，a.QC_REMARK_ID，a.QC_USERID_CD，a.QC_TIMESTAMP，a.QC_TIMES_REJECT，a.pol_chnge_eff_date，a.tpol_rcpt_dets，a.REFUNDEE_ID ，b.User_Remarks2，b.text_eff_date
FROM #GetRemark2 B，G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
- 和accounting_period ='2010年7月31日'
IF OBJECT_ID （' tempdb..Track_Record'） IS 不 NULL
BEGIN
DROP TABLE tempdb..Track_Record
END
/ * Adaptive Server扩展了以下语句中的所有'*'元素* / 选择＃Track_Record_report.policy_no， ＃Track_Record_report.certificate_no，＃Track_Record_report.renl_cert_no，＃Track_Record_report.eff_dt_seq _No，＃Track_Record_report.branch_off_cd，＃Track_Record_report.prdr_branch_sub_cd，＃Track_Record_report.Branch，＃Track_Record_report.product_name，＃Track_Record_report.major_line_cd，＃Track_Record_report.ProductGroup，＃Track_Record_report.pol_incept_date，＃Track_Record_report.pol_exp_date，＃Track_Record_report.ins_name_client_no，#Track_Record_report。 CLIENTNAME，＃Track_Record_report.Client_city_name，＃Track_Record_report.pol_status_cd，＃Track_Record_report.record_type_desc，＃Track_Record_report.policy_counter，＃Track_Record_report.accounting_period，＃Track_Record_report.ProducerName，＃Track_Record_report.PremiumAmount，＃Track_Record_report.o_prem_amt，＃Track_Record_report.SUM_INSURED，#Track_Record_report。 record_type_cd，＃Track_Record_report.producer_cd，＃Track_Record_report.EntryDate，＃Track_Record_report.EntryMonth，＃Track_Record_report.EntryDay，＃Track_Record_report.EntryWeek，＃Track_Record_report.InceptionMonth，＃Track_Record_report.InceptionDay，＃Track_Record_report.InceptionWeek ，＃Track_Record_report.CoverNoteNumber，＃Track_Record_report.Cover_Note_date，＃Track_Record_report.Cov_nt_iss_month，＃Track_Record_report.Application_no，＃Track_Record_report。 TIMESTAMP ，＃Track_Record_report.watts_policy_no，＃Track_Record_report.Producer_type， ＃Track_Record_report.times_renewed_cnt，＃Track_Record_report.Producer_Category，＃Track_Record_report.receipt_date，＃Track_Record_report.rcpt_branch_cd，＃Track_Record_report.rcpt_branch_sub_cd，＃Track_Record_report.receipt_no，＃Track_Record_report.product_cd，＃Track_Record_report.new_producer_cd，＃Track_Record_report.watts_issue_dt，＃Track_Record_report.Channel， ＃Track_Record_report.prem_eff_date，＃Track_Record_report.orig_curr_cd，＃Track_Record_report.LOB，＃Track_Record_report.devel_det，＃Track_Record_report.PIN，＃Track_Record_report.WIP_NO，＃Track_Record_report.POL_TYPE，＃Track_Record_report.APPLICATION_RECEIVED_DT，＃Track_Record_report.WIP_CREATION_DT，＃Track_Record_report.POL_PRINT_DT， #Track_R ecord_report.POL_DELIVERY_DT，＃Track_Record_report.QC_STATUS_CD，＃Track_Record_report.QC_REMARK_ID，＃Track_Record_report.QC_USERID_CD，＃Track_Record_report.QC_TIMESTAMP，＃Track_Record_report.QC_TIMES_REJECT，＃Track_Record_report.pol_chnge_eff_date，＃Track_Record_report.tpol_rcpt_dets，＃Track_Record_report.REFUNDEE_ID，＃Track_Record_report.User_Remarks，＃ Track_Record_report.text_eff_date into tempdb..Track_Record from #Track_Record_report
delete 来自 tempdb..Track_Record 其中 user_remarks 喜欢 ' ％clean％'
end
go
IF OBJECT_ID （' RPTUSR.SP_Track_Record2'） IS NOT NULL
PRINT ' <<<创建过程RPTUSR.SP_Track_Record2>>>'
ELSE
PRINT ' <<<失败的创建程序RPTUSR.SP_Track_Record2>>>'
现在我想摆脱所有暂时的上面查询中的表：
下面是我编写的查询，但它不返回任何行。有人可以帮忙吗？
我尝试过：
选择a.policy_no，a.certificate_no，a.renl_cert_no，a.eff_dt_seq_no，a.branch_off_cd，a.prdr_branch_sub_cd，a.Branch，a.product_name，a.major_line_cd，
a .ProductGroup，a.pol_incept_date，a.pol_exp_date，a.ins_name_client_no，a.ClientName，a.Client_city_name，a.pol_status_cd，a.record_type_desc，
a.policy_counter，a.accounting_period，a.ProducerName ，a.PremiumAmount，a.o_prem_amt，a.SUM_INSURED，a.record_type_cd，a.producer_cd，a.EntryDate，
a.EntryMonth，a.EntryDay，a.EntryWeek，a.InceptionMonth，a .InceptionDay，a.InceptionWeek，a.CoverNoteNumber，a.Cover_Note_date，a.Cov_nt_iss_month，
a.Application_no，a.TIMESTAMP，a.watts_policy_no，a.Producer_type，a.times_renewed_cnt，a.Producer_Category ，a.receipt_date，a.rcpt_branch_cd，
a.rcpt_branch_sub_cd，a.receipt_no，a.product_cd，a.new_producer_cd，a.watts_issue_dt，a.Channel，a.prem_ef f_date，a.orig_curr_cd，a.LOB，a.devel_det，
a.PIN，a.WIP_NO，a.POL_TYPE，a.APPLICATION_RECEIVED_DT，a.WIP_CREATION_DT，a.POL_PRINT_DT，a.POL_DELIVERY_DT， a.QC_STATUS_CD，a.QC_REMARK_ID，
a.QC_USERID_CD，a.QC_TIMESTAMP，a.QC_TIMES_REJECT，a.pol_chnge_eff_date，a.tpol_rcpt_dets，a.REFUNDEE_ID，b.text_eff_date
--b.User_Remarks1，b.text_eff_date，a.Entry
FROM
（选择x.POLICY_NO，x.CERTIFICATE_NO，x .RENL_CERT_NO，x.EFF_DT_SEQ_NO，
x.remarks_txt_id，max（y.text_seq_no）text_seq_no，
text_seq_no1 =当count（*）> 1然后是max（y）时的情况.text_seq_no）-1 else 0 end，
text_seq_no2 = count（*）> 2的情况然后max（y.text_seq_no）-2 else 0 end，
text_seq_no3 = count（*）> 3时的情况，然后是max（y.text_seq_no）-3 else 0 end，
text_eff_date
来自
AUM_Reporting..Tpolicy x noholdlock，
AUM_Reporting..ttext y noholdlock，
S3REPORTS..Track_Record dd（index sla_policy_indx）noholdlock
其中
- dd.accounting_period ='2010年7月31日'
- b.text_type_cd =4和 - （评论人amol041010）
y.text_id = x.remarks_txt_id
AND dd.POLICY_NO = x.POLICY_NO
AND dd.CERTIFICATE_NO = x.CERTIFICATE_NO
AND dd.RENL_CERT_NO = x.RENL_CERT_NO
AND dd.EFF_DT_SEQ_NO = x.EFF_DT_SEQ_NO
和convert（smalldatetime，convert（varchar，text_eff_date， 111））> =转换（smalldatetime，convert（varchar，dd.WIP_CREATION_DT，111））
和x.pol_office_cd ='064001'
group by x.POLICY_NO ，x.CERTIFICATE_ NO，x.RENL_CERT_NO，x.EFF_DT_SEQ_NO，x.remarks_txt_id，y.text_eff_date）
B，
S3REPORTS..Track_Record A，
AUM_Reporting..ttext C
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a。 EFF_DT_SEQ_NO
AND b.remarks_txt_id = C.text_id
AND b.text_seq_no = C.text_seq_no
AND b.text_seq_no1 = C.text_seq_no
AND b.text_seq_no2 = C.text_seq_no
解决方案
SQL Server中的临时表简介 [ ^ ]
那里有一节关于删除临时表的内容
All,
I have the below query which makes use of multiple temp tables :
IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL
BEGIN
DROP PROCEDURE RPTUSR.SP_Track_Record2
IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE RPTUSR.SP_Track_Record2 >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE RPTUSR.SP_Track_Record2 >>>'
END
go
CREATE PROCEDURE SP_Track_Record2
AS
BEGIN
set compatibility_mode on
select a.POLICY_NO,a.CERTIFICATE_NO,a.RENL_CERT_NO,a.EFF_DT_SEQ_NO,
a.remarks_txt_id ,max (b.text_seq_no) text_seq_no ,
text_seq_no1 = case when count(*) >1 then max (b.text_seq_no) -1 else 0 end,
text_seq_no2 = case when count(*) >2 then max (b.text_seq_no) -2 else 0 end,
text_seq_no3 = case when count(*) >3 then max (b.text_seq_no) -3 else 0 end,
text_eff_date
into #GetRemarkSeqNo
from
AUM_Reporting..Tpolicy a noholdlock,
AUM_Reporting..ttext b noholdlock,
G3..Track_Record_REPORT dd(index sla_policy_indx) noholdlock
where
-- dd.accounting_period = 'Jul 31 2010'
-- b.text_type_cd="4" and --(commented by amol041010)
b.text_id = a.remarks_txt_id
AND dd.POLICY_NO = a.POLICY_NO
AND dd.CERTIFICATE_NO = a.CERTIFICATE_NO
AND dd.RENL_CERT_NO = a.RENL_CERT_NO
AND dd.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
and convert(smalldatetime,convert(varchar,text_eff_date,111))>= convert(smalldatetime,convert(varchar,dd.WIP_CREATION_DT,111))
and a.pol_office_cd = '064001'
group by a.POLICY_NO,a.CERTIFICATE_NO,a.RENL_CERT_NO,a.EFF_DT_SEQ_NO,a.remarks_txt_id,b.text_eff_date
--47045 row(s) affected.
CREATE INDEX WIP3in1_GetRemarkSeqNo ON #GetRemarkSeqNo (remarks_txt_id,text_seq_no,text_seq_no1,text_seq_no2,text_seq_no3 )
--drop table #GetRemark3
SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO,a.text_detail AS User_Remarks, a.text_type_cd,#getremarkseqno.text_eff_date
into #GetRemark
FROM #getremarkseqno,AUM_Reporting..ttext a
where #getremarkseqno.remarks_txt_id = a.text_id
AND #getremarkseqno.text_seq_no = a.text_seq_no
--and a.text_type_cd="4"
-- CREATE INDEX WIP3in1_GetRemark ON #GetRemark(WIP_NO)
--GetRemark
--GetRemark1
SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO, a.text_detail AS User_Remarks1, a.text_type_cd,#getremarkseqno.text_eff_date
into #GetRemark1
FROM #getremarkseqno , AUM_Reporting..ttext a
where #getremarkseqno.text_seq_no1 = a.text_seq_no
AND #getremarkseqno.remarks_txt_id = a.text_id
--and a.text_type_cd="4"
-- CREATE INDEX WIP3in1_GetRemark1 ON #GetRemark1(WIP_NO)
--GetRemark1
--GetRemark2
SELECT #getremarkseqno.POLICY_NO,#getremarkseqno.CERTIFICATE_NO,#getremarkseqno.RENL_CERT_NO,#getremarkseqno.EFF_DT_SEQ_NO, a.text_detail AS User_Remarks2, a.text_type_cd,#getremarkseqno.text_eff_date
into #GetRemark2
FROM #getremarkseqno,AUM_Reporting..ttext a
where #getremarkseqno.text_seq_no2 = a.text_seq_no
AND #getremarkseqno.remarks_txt_id = a.text_id
--and a.text_type_cd="4"
-- CREATE INDEX WIP3in1_GetRemark2 ON #GetRemark2(WIP_NO)
--GetRemark2
-- UPDATE G3..Track_Record_REPORT
-- SET User_Remarks = b.User_Remarks
/* Adaptive Server has expanded all '*' elements in the following statement */ select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd, a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID , b.User_Remarks,b.text_eff_date into #Track_Record_report
FROM #GetRemark B ,G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
-- and accounting_period = 'Jul 31 2010'
-- UPDATE G3..Track_Record_REPORT
-- SET User_Remarks1 = b.User_Remarks1
/* Adaptive Server has expanded all '*' elements in the following statement */ insert into #Track_Record_report
select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd, a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID , b.User_Remarks1,b.text_eff_date
FROM #GetRemark1 B ,G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
-- and accounting_period = 'Jul 31 2010'
-- UPDATE G3..Track_Record_REPORT
-- SET User_Remarks2 = b.User_Remarks2
/* Adaptive Server has expanded all '*' elements in the following statement */ insert into #Track_Record_report
select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd, a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc, a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate, a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month, a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd, a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det, a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID, a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID , b.User_Remarks2,b.text_eff_date
FROM #GetRemark2 B ,G3..Track_Record_REPORT A
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
-- and accounting_period = 'Jul 31 2010'
IF OBJECT_ID('tempdb..Track_Record') IS NOT NULL
BEGIN
DROP TABLE tempdb..Track_Record
END
/* Adaptive Server has expanded all '*' elements in the following statement */ select #Track_Record_report.policy_no, #Track_Record_report.certificate_no, #Track_Record_report.renl_cert_no, #Track_Record_report.eff_dt_seq_no, #Track_Record_report.branch_off_cd, #Track_Record_report.prdr_branch_sub_cd, #Track_Record_report.Branch, #Track_Record_report.product_name, #Track_Record_report.major_line_cd, #Track_Record_report.ProductGroup, #Track_Record_report.pol_incept_date, #Track_Record_report.pol_exp_date, #Track_Record_report.ins_name_client_no, #Track_Record_report.ClientName, #Track_Record_report.Client_city_name, #Track_Record_report.pol_status_cd, #Track_Record_report.record_type_desc, #Track_Record_report.policy_counter, #Track_Record_report.accounting_period, #Track_Record_report.ProducerName, #Track_Record_report.PremiumAmount, #Track_Record_report.o_prem_amt, #Track_Record_report.SUM_INSURED, #Track_Record_report.record_type_cd, #Track_Record_report.producer_cd, #Track_Record_report.EntryDate, #Track_Record_report.EntryMonth, #Track_Record_report.EntryDay, #Track_Record_report.EntryWeek, #Track_Record_report.InceptionMonth, #Track_Record_report.InceptionDay, #Track_Record_report.InceptionWeek, #Track_Record_report.CoverNoteNumber, #Track_Record_report.Cover_Note_date, #Track_Record_report.Cov_nt_iss_month, #Track_Record_report.Application_no, #Track_Record_report.TIMESTAMP, #Track_Record_report.watts_policy_no, #Track_Record_report.Producer_type, #Track_Record_report.times_renewed_cnt, #Track_Record_report.Producer_Category, #Track_Record_report.receipt_date, #Track_Record_report.rcpt_branch_cd, #Track_Record_report.rcpt_branch_sub_cd, #Track_Record_report.receipt_no, #Track_Record_report.product_cd, #Track_Record_report.new_producer_cd, #Track_Record_report.watts_issue_dt, #Track_Record_report.Channel, #Track_Record_report.prem_eff_date, #Track_Record_report.orig_curr_cd, #Track_Record_report.LOB, #Track_Record_report.devel_det, #Track_Record_report.PIN, #Track_Record_report.WIP_NO, #Track_Record_report.POL_TYPE, #Track_Record_report.APPLICATION_RECEIVED_DT, #Track_Record_report.WIP_CREATION_DT, #Track_Record_report.POL_PRINT_DT, #Track_Record_report.POL_DELIVERY_DT, #Track_Record_report.QC_STATUS_CD, #Track_Record_report.QC_REMARK_ID, #Track_Record_report.QC_USERID_CD, #Track_Record_report.QC_TIMESTAMP, #Track_Record_report.QC_TIMES_REJECT, #Track_Record_report.pol_chnge_eff_date, #Track_Record_report.tpol_rcpt_dets, #Track_Record_report.REFUNDEE_ID, #Track_Record_report.User_Remarks, #Track_Record_report.text_eff_date into tempdb..Track_Record from #Track_Record_report
delete from tempdb..Track_Record where user_remarks like '%clean%'
end
go
IF OBJECT_ID('RPTUSR.SP_Track_Record2') IS NOT NULL
PRINT '<<< CREATED PROCEDURE RPTUSR.SP_Track_Record2 >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE RPTUSR.SP_Track_Record2 >>>'
Now I want to get rid of all the temporary tables in the above query :
Below is the query that I have written, but it does not return any rows. Can anyone help?
What I have tried:
select a.policy_no, a.certificate_no, a.renl_cert_no, a.eff_dt_seq_no, a.branch_off_cd, a.prdr_branch_sub_cd, a.Branch, a.product_name, a.major_line_cd,
a.ProductGroup, a.pol_incept_date, a.pol_exp_date, a.ins_name_client_no, a.ClientName, a.Client_city_name, a.pol_status_cd, a.record_type_desc,
a.policy_counter, a.accounting_period, a.ProducerName, a.PremiumAmount, a.o_prem_amt, a.SUM_INSURED, a.record_type_cd, a.producer_cd, a.EntryDate,
a.EntryMonth, a.EntryDay, a.EntryWeek, a.InceptionMonth, a.InceptionDay, a.InceptionWeek, a.CoverNoteNumber, a.Cover_Note_date, a.Cov_nt_iss_month,
a.Application_no, a.TIMESTAMP, a.watts_policy_no, a.Producer_type, a.times_renewed_cnt, a.Producer_Category, a.receipt_date, a.rcpt_branch_cd,
a.rcpt_branch_sub_cd, a.receipt_no, a.product_cd, a.new_producer_cd, a.watts_issue_dt, a.Channel, a.prem_eff_date, a.orig_curr_cd, a.LOB, a.devel_det,
a.PIN, a.WIP_NO, a.POL_TYPE, a.APPLICATION_RECEIVED_DT, a.WIP_CREATION_DT, a.POL_PRINT_DT, a.POL_DELIVERY_DT, a.QC_STATUS_CD, a.QC_REMARK_ID,
a.QC_USERID_CD, a.QC_TIMESTAMP, a.QC_TIMES_REJECT, a.pol_chnge_eff_date, a.tpol_rcpt_dets, a.REFUNDEE_ID, b.text_eff_date
--b.User_Remarks1,b.text_eff_date, a.Entry
FROM
(select x.POLICY_NO,x.CERTIFICATE_NO,x.RENL_CERT_NO,x.EFF_DT_SEQ_NO,
x.remarks_txt_id ,max (y.text_seq_no) text_seq_no ,
text_seq_no1 = case when count(*) >1 then max (y.text_seq_no) -1 else 0 end,
text_seq_no2 = case when count(*) >2 then max (y.text_seq_no) -2 else 0 end,
text_seq_no3 = case when count(*) >3 then max (y.text_seq_no) -3 else 0 end,
text_eff_date
from
AUM_Reporting..Tpolicy x noholdlock,
AUM_Reporting..ttext y noholdlock,
S3REPORTS..Track_Record dd(index sla_policy_indx) noholdlock
where
-- dd.accounting_period = 'Jul 31 2010'
-- b.text_type_cd="4" and --(commented by amol041010)
y.text_id = x.remarks_txt_id
AND dd.POLICY_NO = x.POLICY_NO
AND dd.CERTIFICATE_NO = x.CERTIFICATE_NO
AND dd.RENL_CERT_NO = x.RENL_CERT_NO
AND dd.EFF_DT_SEQ_NO = x.EFF_DT_SEQ_NO
and convert(smalldatetime,convert(varchar,text_eff_date,111))>= convert(smalldatetime,convert(varchar,dd.WIP_CREATION_DT,111))
and x.pol_office_cd = '064001'
group by x.POLICY_NO,x.CERTIFICATE_NO,x.RENL_CERT_NO,x.EFF_DT_SEQ_NO,x.remarks_txt_id,y.text_eff_date)
B ,
S3REPORTS..Track_Record A,
AUM_Reporting..ttext C
WHERE b.POLICY_NO = a.POLICY_NO
AND b.CERTIFICATE_NO = a.CERTIFICATE_NO
AND b.RENL_CERT_NO = a.RENL_CERT_NO
AND b.EFF_DT_SEQ_NO = a.EFF_DT_SEQ_NO
AND b.remarks_txt_id = C.text_id
AND b.text_seq_no = C.text_seq_no
AND b.text_seq_no1 = C.text_seq_no
AND b.text_seq_no2 = C.text_seq_no 解决方案
Introduction to Temporary Tables in SQL Server[^]
There is a section in there about deleting temporary tables
这篇关于帮助摆脱查询中的临时表。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
