Re: Problem with Select

[Les Hancock <les_hanc@YAHOO.COM>]

Just a thought:

If you don't want to fool with the max effdt stuff you can do
something like this:


do Get-Latest-Med-Info-for-this-EE



begin-procedure Get-Latest-Med-Info-for-this-EE

begin-select loops = 1
hb.emplid,
..
..
..
order by hb.effdt desc

end-procedure  Get-Latest-Med-Info-for-this-EE






---Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG> wrote:
>
>      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?
>
>        1  select
>        2  hb.emplid,
>        3  hb.empl_rcd#,
>        4  hb.effdt,
>        5  hb.Coverage_begin_dt,
>        6  hb.plan_type,
>        7  hb.coverage_elect,
>        8  hb.Benefit#,
>        9  hb.covrg_cd,
>       10  hb.benefit_plan
>       11  from ps_health_benefit hb,
>       12       ps_benef_plan_tbl bp
>       13  where hb.emplid = '651232'
>       14    and hb.coverage_Elect = 'E'
>       15    and hb.effdt <= '01-DEC-98'
>       23    and hb.benefit_plan = bp.benefit_plan
>       24    and bp.provider = 'FRSTHE'
>       25*
>      SQL>
>
>      EMPLID      EMPL_RCD# EFFDT     COVERAGE_ PL C  BENEFIT# C BENEFI
>      ----------- --------- --------- --------- -- - --------- - ------
>      651232              0 31-DEC-95 01-JAN-96 10 E         0 4 SEL200
>      651232              0 29-DEC-96 01-JAN-97 10 E         0 4 SEL400
>

_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com