Solved

Between operator returning incorrect result

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

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

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

770 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