Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Between operator returning incorrect result

Hi, I am attempting to find if a couple dates fall within the effective and termination dates of another record. While testing the data, I noticed that when I intentionally change the effective date and termination date of the incoming dates, it returns the incorrect result. I also noticed that when I take out the OR operand from my code, as well as the test for the termination date, the query returns the correct result but I am not sure why the OR would affect this?
here is a snippet of my code



--incoming in_eff_date: 1991-01-16
--incoming in_term_date: 1999-12-31



CURSOR cs_overlapping_dates IS
  SELECT 'x'
  FROM subscriber_group
  where group_nbr = in_group_nbr AND
  section_id = in_section_id AND
  lob_code = in_lob_code AND
  cvg_nbr = in_cvg_nbr AND
  participant_type_code = g_participant_type_cardholder AND
  ((in_eff_date BETWEEN eff_date AND term_date) OR
  (in_term_date BETWEEN eff_date AND term_date));


--end snippet
OUTPUT of this is when code ran:
l_dummy1 

(this is incorrect because the eff_date of the record in the subscriber table is 1/15/1991 and the term date is 12/31/1999. I should be seeing 'x' indicating a record has been found)

Open in new window

0
grnmachine
Asked:
grnmachine
  • 2
  • 2
1 Solution
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Well, your OR means that when either of the BETWEEN checks are true, your 'xr record is selected. This is not correct because if one is not correct, the record is still selected. You should change your OR to AND and check again.
0
 
grnmachineAuthor Commented:
Hi gerwinjansen, i know its kind of strange logic, but i need the query to return something if either the in_eff_date or the in_term_date is between the eff_date and term_date of the record in the subscriber_group table. In order for the check to return nothing, I want the in_eff_date or in_term_date to be totally outside of the eff_date/term_date range. In this case, it is returning nothing, which it should be returning x because in_eff_date is within the eff_date/term_date of the record being checked....but is not.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
When I test this, I get the output you're expecting. How are you providing in_eff_date and i_term_date to your query? Do you need to use a to_date conversion maybe? I tested this way (leaving out the first 4 where codes):

SQL> l
  1  select 'X' from subscriber_group
  2  where
  3  ((to_date('1991-01-16','YYYY-MM-DD') BETWEEN eff_date AND term_date)
  4* OR (to_date('1999-12-31','YYYY-MM-DD') BETWEEN eff_date AND term_date))
SQL> /

'
-
X

SQL>

SQL> select * from subscriber_group;

EFF_DATE  TERM_DATE NAME
--------- --------- --------------------
15-JAN-91 31-DEC-99 Employee1

SQL> 
 

Open in new window



0
 
grnmachineAuthor Commented:
sorry for the delay. It turns out that the record I was expecting to get a result for was being deleted by another process before hitting this procedure. Thanks for your time.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now