Re: Select statement with a temp table

[Arjan Hoornstra <ahoor@YAHOO.COM>]

Do I understand you correct that the 2nd proc (the
local one) does work and the first one not?

I tried this in a test, and it probably will work if
uou put the variable table in front.

It really makes a difference... at my system (sybase)

...
from [$temp_ps_dis_ben] db, ps_job j, ps_employment e,
ps_personal_data  p
...



--- KBLUMEN@SEARS.COM wrote:
> !SQR/4.3.4/Sun/SunOS 5.5.1/Informix 7.24
>
> Hello all! I get the following error when I run my
> program: The specified table
> (temp_ps_dis_ben) is not in the database.
> Below is the select statement. I have created,
> loaded and indexed the table. It
> runs fine when records are selected from
>  this temp table in the other procedure. But when I
> add it to the main select, I
>  get an error. Any suggestions?
>
> begin-procedure Process-Main
> show 'Program running.......'
> begin-select
> j.emplid
> j.full_part_time
> j.empl_type
> !and other fields etc...
>
> from ps_job j, ps_employment e, ps_personal_data  p,
> [$temp_ps_dis_ben] db
> where j.effdt = (select max(j1.effdt) from ps_job j1
>           where j1.emplid = j.emplid
>                and j1.empl_rcdn = j.empl_rcdn
>                and j1.effdt <= $AsOfToday)
>    and j.effseq = (select max(j1.effseq) from ps_job
> j1
>             where j1.emplid = j.emplid
>                and j1.empl_rcdn = j.empl_rcdn
>                and j1.effdt = j.effdt)
>    and e.emplid = j.emplid
>    and e.empl_rcdn = j.empl_rcdn
>    and p.emplid = j.emplid
>    and j.emplid = db.emplid
>    and db.effdt = (select max(db2.effdt)
>                from ps_disability_ben db2
>                where db2.emplid = db.emplid
>                  and db2.empl_rcdn = db.empl_rcdn
>                  and db2.plan_type = db.plan_type
>                  and db2.benefitn = db.benefitn
>                  and db2.effdt <= $AsOfToday)
>    and db.coverage_elect = 'E'
>    and db.plan_type in ('30','31')
>    and j.empl_status in ('A','P')
>
> begin-procedure
>
process-ins-info($ssn,:$effdt,:$cov_beg_dt,:$cov_end_dt,:$cov_elect_dt)
> begin-select
> db.plan_type
> db.effdt
> db.coverage_begin_dt
> db.coverage_end_dt
> db.coverage_elect_dt
>
>    move &db.effdt to $effdt
>    move &db.coverage_begin_dt to $cov_beg_dt
>    move &db.coverage_end_dt to $cov_end_dt
>    move &db.coverage_elect_dt to $cov_elect_dt
>    #ifdef debuga
>    show '$effdt = ' $effdt
>    show '$cov_beg_dt = ' $cov_beg_dt
>    show '$cov_end_dt = ' $cov_end_dt
>    show '$cov_elect_dt = ' $cov_elect_dt
>    #end-if
>
> from [$_temp_ps_dis_ben] db
> where db.emplid = $ssn
>    [$_what_type]
>    and db.effdt = (select max(db2.effdt)
>                from ps_disability_ben db2
>                where db2.emplid = db.emplid
>                  and db2.empl_rcdn = db.empl_rcdn
>                  and db2.plan_type = db.plan_type
>                  and db2.benefitn = db.benefitn
>                  and db2.effdt <= $_AsOfToday)
> end-select
> end-procedure process-ins-info
>
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com