Oracle Script returning no rows

Question

I have a script that returns 0 rows for accurate data. I omit line 6 or 7 the script works. Is there anything logically incorrect with this script?

1 Select Max (seq_num)
2 From t_cap_history
3  Where pr_id_sak = :il_pr_id_sak
4     And re_unique_id=:locw_re_unique_id
5     And period_dte = :ll_period_date 
6     AND cap_from_dte = :ll_curr_from_date
7     And cap_to_dte = :ll_curr_to_date
8     And capitation_cde = :ls_cap_cde 

Answer 1

Staring deeply into my crystal ball...

If cap_from_dte and cap_to_dte are columns of type date, then they will have a day component and a time component. Even if your session isn't set to display the time component, it will be there. You're doing an equality comparison so the :ll_curr_from_date and :ll_curr_to_date bind variables would need to match the data in the table exactly. I would guess that your SQL*Plus session is set not to display the time portion of date values by default and that has caused you to pass incorrect values for your bind variables.

Assuming that your bind variables are dates with a time component of midnight (the default), you could use inequalities which generally make it easier for the optimizer to use indexes on the date columns

Select Max (seq_num)
  From t_cap_history
 Where pr_id_sak = :il_pr_id_sak
   And re_unique_id=:locw_re_unique_id
   And period_dte = :ll_period_date 
   AND cap_from_dte >= :ll_curr_from_date
   AND cap_from_dte <  :ll_curr_from_date + 1
   And cap_to_dte >= :ll_curr_to_date
   And cap_to_dte < :ll_curr_to_date + 1
   And capitation_cde = :ls_cap_cde 

or you could use the trunc function to set the time component to midnight

Select Max (seq_num)
  From t_cap_history
 Where pr_id_sak = :il_pr_id_sak
   And re_unique_id=:locw_re_unique_id
   And period_dte = :ll_period_date 
   AND trunc(cap_from_dte) = :ll_curr_from_date
   And trunc(cap_to_dte) = :ll_curr_to_date
   And capitation_cde = :ls_cap_cde 

Answer 2

For date ranges why not

cap_to_dte >= :ll_curr_from_date AND cap_from_dte <= :ll_curr_to_date