Solved

Between operator returning incorrect result

Posted on 2011-09-10
4
281 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 37

Expert Comment

by:Gerwin Jansen
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 37

Accepted Solution

by:
Gerwin Jansen 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now