Re: Problem with Select

[Wayne Ivory <wivory@WSL.COM.AU>]

Doing a max on effdt should do it for you.  The fact that you didn't get any data could be because you didn't duplicate all conditions into the subselect, eg perhaps you didn't put hb.emplid = '651232' into the subselect.  If you still can't work it out post your sql with the max in it.

Wayne Ivory
Information Services
Westralian Sands Limited

>>> Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG> 30/12/98 8:17:46 am >>>
     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