Click here to Skip to main content
15,943,085 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
All,

I have the below query which makes use of multiple temp tables :

SQL
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
Posted
Updated 16-Jun-18 23:41pm
v2
Comments
[no name] 17-Jun-18 14:50pm    
So you "refactored" and now it's "not working"?

Did you consider the possibility that the "temp tables" were there in order to make the query "less complex"?

Do you know that the "query optimizer" is not infallible?

1 solution

Introduction to Temporary Tables in SQL Server[^]

There is a section in there about deleting temporary tables
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900