Re: Problem with Select

["Ranade, Sameer" <sranade@BRISHOSP.CHIME.ORG>]

Hi Bill,

        select max(effdt) logic should work in this case. Try following.

       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'

      23    and hb.benefit_plan = bp.benefit_plan
      24    and bp.provider = 'FRSTHE'
            and hb.effdt = (select max(hb1.effdt) from ps_health_benefit
hb1
                            where hb1.emplid= hb.emplid and
                                  hb1.empl_rcd#=hb.empl_rcd# and
                                  hb1.cobra_event_id = hb.cobra_event_id
and
                                  hb1.plan_type = hb.plan_type and
                                            hb1.benefit# = hb.benefit#
and
                                  hb1.effdt <='01-DEC-98')



HTH,
-Sameer


-----Original Message-----
From: Bill Bowers [mailto:bowersb@PSPH.PROVIDENCE.ORG]
Sent: Tuesday, December 29, 1998 7:18 PM
To: Multiple recipients of list SQR-USERS
Subject: Problem with Select


     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