Solved

Between operator returning incorrect result

Posted on 2011-09-10
4
284 Views
Last Modified: 2012-05-12
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
Comment
Question by:grnmachine
  • 2
  • 2
4 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 36517211
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
 

Author Comment

by:grnmachine
ID: 36517295
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
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 500 total points
ID: 36519749
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
 

Author Closing Comment

by:grnmachine
ID: 36904831
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to take different types of Oracle backups using RMAN.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question