Re: Problem with Select

["C. Willis III" <ceprn@HOTMAIL.COM>]

----Original Message Follows----
I am using PeopleSoft HRMS 6.01 with Oracle. I am writing an sqr an
having problems retrieving the correct row of data.
I am attempting to read the ps_health_benefit table using the
following sql and receiving the following results.  I want to retrieve
the row with SEL400 only.  I have also added additional code, using a
select max effdt for ps_health_benefit at which point I do not select
any data.

Any Ideas?

select
hb.emplid,
hb.empl_rcd#,
hb.effdt,
hb.Coverage_begin_dt,
hb.plan_type,
hb.coverage_elect,
hb.Benefit#,
hb.covrg_cd,
hb.benefit_plan
from ps_health_benefit hb,
     ps_benef_plan_tbl bp
where hb.emplid = '651232'
and   hb.coverage_Elect = 'E'
and   hb.effdt <= '01-DEC-98'
and   hb.benefit_plan = bp.benefit_plan
and   bp.provider = 'FRSTHE'
and   hb.effdt = (select max(hb2.effdt)
                  from ps_health_benefit hb2
                  where hb.[key fields] = hb2.[key fields])

should give you the information you require.  You could also replace the
max(effdt) with the pseudo-column max(rowid) as long as you can
guarantee the newer (chronologically speaking) record was entered after
previous records (can't be guaranteed with data imports).

clark
dragon enterprises, consulting services


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