Solved

Between operator returning incorrect result

Posted on 2011-09-10
4
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

623 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