You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Problem with fetching Effective Dated Row

3 replies [Last post]
krisvamsi2005's picture
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 03/11/2008
Posts: 17

Hi Guys,

I have a problem with pulling the effective dated row from the ps_health_benefit table
the criteria is like COVERAGE_ELECT = ‘E’ and Max(HB.EFFDT) <= [date entered by the user on page]

I am not able to pull the correct plan type.I tried this query but its failing...Can any one of you guys let me know wher I am failing here

select plan_type from sysadm.ps_health_benefit hb where hb.emplid = '090801' and coverage_elect = 'E' and effdt =( select max(effdt) from sysadm.ps_health_benefit hb_ed where hb.emplid = hb_ed.emplid
and hb.empl_rcd = hb_ed.empl_rcd and hb.cobra_event_id = hb_ed.cobra_event_id and hb.plan_type = hb_ed.plan_type and hb.benefit_nbr = hb_ed.benefit_nbr and hb_ed.effdt < '03/04/2009');

Thanks
Vamsi

Thanks,
Vamsi Krishna

oddsnends's picture
User offline. Last seen 11 years 19 weeks ago. Offline
Joined: 03/28/2008
Posts: 16
Re: Problem with fetching Effective Dated Row

What is the failure you are having. Is it the statement itself or the data that it is returning?

I was able to run the statement in SQL (Toad) I just had to change the date format
from '03/04/2009' to '04-MAR-2009'.

krisvamsi2005's picture
User offline. Last seen 7 years 34 weeks ago. Offline
Joined: 03/11/2008
Posts: 17
Re: Problem with fetching Effective Dated Row

Thanks for the reply oddsnends.

The problem here is with the data that it is returning.

Thanks,
Vamsi Krishna

Ashar's picture
User offline. Last seen 5 years 1 week ago. Offline
Joined: 03/26/2008
Posts: 227
Re: Problem with fetching Effective Dated Row

What is the requirement then?