Re: Temp Tables

[Manoj Gurjar <mgurjar@HOTMAIL.COM>]

Everytime you create table, it uses $SQR-PID, in table name,
so u have unique table, everytime u run program.
But if those tables are not droped at the end of the program,
then imagine how many temp tables you have in your system!!
Every run instance will creat 4 temp tables!
You certainly need to drop at the end of the program.
Dynamically substitute table name for Drop table SQL
at the end of the program.

HTH,
Manoj

>From: Kimberly Blumenberg <KBLUMEN@SEARS.COM>
>Reply-To: SQR-USERS@list.iex.net
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>Subject: Temp Tables
>Date: Wed, 3 Nov 1999 16:13:02 -0600
>
>SQR 4.3.2, UNIX OS, Informix ...
>
>Hello all! I have a horrible programming task. I have two tax programs that
>have
>lots of hard coding that I need to get rid of. I also need to merge the
>programs
>into one. They are both very redundant. The original programmer created
>four
>temp tables but I noticed that they didn't get dropped anywhere towards the
>end
>of the program. Question: Do I need to drop the temp tables at the end of
>the
>program? I did a test and stopped the program while it was running and
>restarted
>it. I thought that I would get an error when it tries to re-create the
>tables
>but I didn't get one. Below is a sample of the code that creates and loads
>the
>tables:
>Thanks in advance!
>
>!----------------------------------------------------------------------
>! Procedure: create-temp-tbls
>!     Descr: This procedure creates 4 temporary tables.
>!----------------------------------------------------------------------
>
>begin-procedure create-temp-tbls
>
>    SHOW 'Begin Procedure create-temp-tbls'
>
>    let $temp_ps_pay_check     = 'tpc' || rtrim(ltrim(to_char(#sqr-pid),'
>'),' ')
>    let $temp_ps_pay_deduction = 'tpd' || rtrim(ltrim(to_char(#sqr-pid),'
>'),' ')
>    let $temp_ps_pay_tax       = 'tpt' || rtrim(ltrim(to_char(#sqr-pid),'
>'),' ')
>    let $temp_work             = 'tw'  || rtrim(ltrim(to_char(#sqr-pid),'
>'),' ')
>
>begin-sql
>commit;
>begin work;
>
>create temp table [$temp_ps_pay_check]
>    (company char(3)
>    ,paygroup char(3)
>    ,pay_end_dt date
>    ,off_cycle char(1)
>    ,pagen integer
>    ,linen smallint
>    ,sepchk smallint
>    ,emplid char(11)
>    ,check_dt date) with no log;
>
>create temp table [$temp_ps_pay_deduction]
>    (company char(3)
>    ,paygroup char(3)
>    ,pay_end_dt date
>    ,off_cycle char(1)
>    ,pagen integer
>    ,linen smallint
>    ,sepchk smallint
>    ,dedcd char(6)
>    ,ded_cur decimal(10,2)) with no log;
>
>create temp table [$temp_ps_pay_tax]
>    (company char(3)
>    ,paygroup char(3)
>    ,pay_end_dt date
>    ,off_cycle char(1)
>    ,pagen integer
>    ,linen smallint
>    ,sepchk smallint
>    ,state char(6)
>    ,locality char(7)
>    ,tax_class char(1)
>    ,nlgrs_cur decimal(10,2)
>    ,txgrs_cur decimal(10,2)
>    ,tax_cur decimal(10,2)) with no log;
>
>create temp table [$temp_work]
>    (company char(3) not null
>    ,pay_end_dt date not null) with no log;
>
>commit;
>end-sql
>
>end-procedure create-temp-tbls
>
>----------------------------------------------------------------------
>! Procedure: load-temp-tbls
>!     Descr: This procedure loads the temporary tables.
>!----------------------------------------------------------------------
>
>begin-procedure load-temp-tbls
>
>    SHOW 'Begin Procedure load-temp-tbls'
>    SHOW '   Load Pay Check'
>
>begin-sql
>begin work;
>insert into [$temp_ps_pay_check]
>select company
>       ,paygroup
>       ,pay_end_dt
>       ,off_cycle
>       ,pagen
>       ,linen
>       ,sepchk
>       ,emplid
>       ,check_dt
>   from ps_pay_check
>  where check_dt between $fromdate and $thrudate
>    and off_cycle = $off_cycle;                               !KBLUMEN MOD
>commit;
>end-sql
>
>    SHOW ' '
>    SHOW '   Load Pay Deduction'
>
>begin-sql
>begin work;
>insert into [$temp_ps_pay_deduction]
>select pd.company
>       ,pd.paygroup
>       ,pd.pay_end_dt
>       ,pd.off_cycle
>       ,pd.pagen
>       ,pd.linen
>       ,pd.sepchk
>       ,pd.dedcd
>       ,pd.ded_cur
>   from ps_pay_deduction pd
>       ,[$temp_ps_pay_check] pc
>  where pc.company    = pd.company
>    and pc.paygroup   = pd.paygroup
>    and pc.pay_end_dt = pd.pay_end_dt
>    and pc.off_cycle  = pd.off_cycle
>    and pc.pagen      = pd.pagen
>    and pc.linen      = pd.linen
>    and pc.sepchk     = pd.sepchk
>    and pd.ded_class  = 'B';
>commit;
>end-sql
>
>    SHOW ' '
>    SHOW '   Load Pay Tax'
>
>begin-sql
>begin work;
>insert into [$temp_ps_pay_tax]
>select pt.company
>       ,pt.paygroup
>       ,pt.pay_end_dt
>       ,pt.off_cycle
>       ,pt.pagen
>       ,pt.linen
>       ,pt.sepchk
>       ,pt.state
>       ,pt.locality
>       ,pt.tax_class
>       ,pt.nlgrs_cur
>       ,pt.txgrs_cur
>       ,pt.tax_cur
>   from ps_pay_tax pt
>       ,[$temp_ps_pay_check] pc
>  where pc.company    = pt.company
>    and pc.paygroup   = pt.paygroup
>    and pc.pay_end_dt = pt.pay_end_dt
>    and pc.off_cycle  = pt.off_cycle
>    and pc.pagen      = pt.pagen
>    and pc.linen      = pt.linen
>    and pc.sepchk     = pt.sepchk;
>commit;
>end-sql
>
>end-procedure load-temp-tbls
>
>!----------------------------------------------------------------------
>! Procedure: build-indexes
>!     Descr: This procedure builds indexes for the temporary tables.
>!----------------------------------------------------------------------
>
>begin-procedure build-indexes
>
>    SHOW 'Begin Procedure build-indexes'
>
>begin-sql
>begin work;
>lock table [$temp_ps_pay_check] in exclusive mode;
>create index pay_check1 on [$temp_ps_pay_check]
>       (check_dt
>       ,company
>       ,pay_end_dt);
>commit;
>end-sql
>
>begin-sql
>begin work;
>lock table [$temp_ps_pay_deduction] in exclusive mode;
>create index pay_deduction1 on [$temp_ps_pay_deduction]
>       (company
>       ,dedcd
>       ,ded_cur);
>commit;
>end-sql
>
>begin-sql
>begin work;
>lock table [$temp_ps_pay_tax] in exclusive mode;
>create index pay_tax1 on [$temp_ps_pay_tax]
>       (tax_class
>       ,company
>       ,locality
>       ,state
>       ,off_cycle
>       ,nlgrs_cur
>       ,txgrs_cur
>       ,tax_cur);
>commit;
>end-sql
>
>end-procedure build-indexes
>
>!----------------------------------------------------------------------
>! Procedure: load-work-tbl
>!     Descr: This procedure loads the work table.
>!----------------------------------------------------------------------
>
>begin-procedure load-work-tbl
>
>    SHOW 'Begin Procedure load-work-tbl'
>
>begin-sql
>insert into [$temp_work]
>select /*+index(a pay_check1)*/ distinct
>company,
>pay_end_dt
>
>from [$temp_ps_pay_check] a
>where a.check_dt between $fromdate and $thrudate
>end-sql
>
>end-procedure load-work-tbl
>
>etc, etc,....
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com