Select statement

[anju77]

Hi friends,
i have a cursor in which I am passing two parameters (eff_dt and empl_id).
In a table there can be more than one record for a employee with the diff effective date.
I want to do a select on a table when eff_dt is not null and table column effective date = parameter eff_dt then select the matching record but when eff_dt is not null and table column effective date doesn’t match with parameter eff_dt then select the latest effective dated record from the table.
So I wrote:

Select * from employee emp
Where emp.emplid = empl_id
And ( (eff_dt is not null and emp.effdt = eff_dt)
Or
Eff_dt is not null and emp.effdt (select max(effdt) from employee emp1
Where emp1.emplid = empl_id
And emp1.effdt != eff_dt)
)


if suppose there are 2 record with effective date 10th sep 2005 and 6th dec 2006 and I am passing eff_dt as 10th sep 2005 then according to me it should return o/p as 10th sep 2005 but with this select it is returning 6th dec 2006 which I wrong.

Can someone help me in solving my issue?
Thanks in advance.

 

[lewisp]

2 problems.

1) Dates always have a time portion. To effectivly select on a date you need to use [t]trunc(emp.effdt)[/t]. Beware that indexes may not be used if you use funtion like this.

2) You have used an OR in your where clause, so records matching either criteria will be returned.

Your requirements are not clear. Do you want to return only 1 row or more than one?

 

[anju77]

Thanks Lewisp!
date in the table doesn't have time. and i just want to return one row based on my condition. if the parameter passing matches with the table date then return that date else if it doesn't match then return the latest date in the table

Thanks,

 

[lewisp]

Do you want to return any date that is the latest, or only the latest date earlier than the one you have entered?

Are you sure your date has no time? Was it created using SYSDATE? Check by using to_char(effdt,'ddmmyyyy hh24mi') on your table and see what times the dates are set to.

SELECT * FROM (
  Select *
  from   employee emp
  Where  emp.emplid = empl_id
  And    eff_dt is not null
  and    emp.effdt <= eff_dt
  ORDER BY emp.effdt DESC)
WHERE rownum = 1;

This code will return the row with latest date that is earlier than or equal to your entered date, which I think is what you're asking for.