How do I generate based on conditions

For Testing I need test cases in 3 categories:
1. 10 Cases where Close Date (actual close date of Complaint) is more than 8 years prior to current date
2. 10 cases where Close Date  (actual close date) is less than 8 years but still prior to current date
3. 10 cases where Close Date is 12/31/9999 default date since these will not be closed.

How do I select records satisfying these conditions

I googled and found out this solution

select DATE_END
from TABLE_NAME
where DATE_END < SYSDATE - 2920

// CALCULATED 8*365 = 2920 DAYS

and I am looking for a better solution.. Please help experts....

XxtremeProAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
SELECT COL1, NVL(DATE_END,to_date('12/31/9999','MM/DD/YYYY')) DATE_END
        FROM TABLE1
        WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12')
    ORDER BY 1;
0
 
johnsoneSenior Oracle DBACommented:
Instead of the number of days, you can use the number of months:

add_months(sysdate, -96)

Or, you can use an interval

systimestamp - interval '8 year'
0
 
XxtremeProAuthor Commented:
one of my colleagues suggests using in between so and so date i.e, from Date1 to Date2

any thoughts on that... experts...
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
slightwv (䄆 Netminder) Commented:
There are many ways to skin this cat.

Since this is just to obtain sample test data, is one any better than the others?
0
 
XxtremeProAuthor Commented:
I just need one concrete way.... need to tackle the animal soon with the experts help.... :)
0
 
slightwv (䄆 Netminder) Commented:
What parts do you still need help with?

You have examples of how to manipulate the current date (sysdate).

The between syntax is straight forward:

Select ...
Where date_col between sysdate-1 and sysdate;

Just use one of the date methods to get your ranges.
0
 
johnsoneSenior Oracle DBACommented:
This is what I would use:

1. where DATE_END < add_months(sysdate, -96)
2. where DATE_END > add_months(sysdate, -96) and DATE_END < sysdate
3. where DATE_END = to_date('12/31/9999', 'mm/dd/yyyy')

No need for a between on 1 and 3.  If you really wanted to use between on 2, then it would be:


where DATE_END between add_months(sysdate, -96) and sysdate
0
 
XxtremeProAuthor Commented:
where DATE_END = to_date('12/31/9999', 'mm/dd/yyyy')... I am facing an error...

THE DATE_END FIELD WITH A QUERY

WHERE DATE_END IS NULL RETURNS A LOT OF RECORDS

BUT NONE WHEN I SAY

WHERE DATE_END = to_date('12/31/9999', NULL)

REMEMBER I WANT TO REPLACE IN THE OUTCOME NULL WITH 12/31/9999
0
 
slightwv (䄆 Netminder) Commented:
>>to_date('12/31/9999', NULL)

This results as a NULL and nothing can '=' null in oracle.

>>I am facing an error...

What is the error?


0
 
XxtremeProAuthor Commented:
WHERE DATE_END IS NULL RETURNS A LOT OF RECORDS

BUT NONE WHEN I SAY >> MEANING ALL RECORDS WITH NULL DISPLAYED BUT NONE REPLACED WITH 12/31/9999 AS THE REQUIREMENT SAYS, WHEN I RUN THE QUERY  :(

WHERE DATE_END = to_date('12/31/9999', NULL)


PLEASE HELP....
0
 
slightwv (䄆 Netminder) Commented:
>>WHERE DATE_END = to_date('12/31/9999', NULL)

I told you why this returns no rows.

 to_date('12/31/9999', NULL) results in NULL:

select  to_date('12/31/9999', NULL) from dual;

This makes that statement actually read:
WHERE DATE_END = NULL

In Oracle NOTHING can "=" null.

>>AS THE REQUIREMENT SAYS

Do you mean that the DATE_END is supposed to contain '12/31/9999'?

0
 
XxtremeProAuthor Commented:
Yes

DATE_END SUPPOSED TO CONTAIN '12/31/9999' WHERE IT EQUALS NULL
0
 
johnsoneSenior Oracle DBACommented:
Then I would go with:

nvl(DATE_END, to_date('12/31/9999', 'mm/dd/yyyy')) = to_date('12/31/9999', 'mm/dd/yyyy')

That way if any records actually contain the date, you would pick them up.  Because you are looking for nulls, you are going to do a full table scan anyway.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>DATE_END SUPPOSED TO CONTAIN '12/31/9999' WHERE IT EQUALS NULL

For grins try:
where trunc(DATE_END) = to_date('12/31/9999', 'mm/dd/yyyy')

If that doesn't return the right rows then what is 'supposed to be', well, isn't.
0
 
XxtremeProAuthor Commented:
SELECT COL1, DATE_END
        FROM TABLE1
        WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12')
    ORDER BY 1;

is the query. and when executed it gives me

COL1                               DATE_END
----------------                   ------------------------

1                                    
2
3
4
5
6
7
8
9
10
11
12


because in the table the records 1...12 have their date_end as null

and the query should be modified in a way that the output is

COL1                               DATE_END
----------------                   ------------------------

1                                     12/31/9999                                  
2                                     12/31/9999
3                                     12/31/9999
4                                     12/31/9999
5                                     12/31/9999
6                                     12/31/9999
7                                     12/31/9999
8                                     12/31/9999
9                                     12/31/9999
10                                   12/31/9999
11                                   12/31/9999
12                                   12/31/9999

 I hope I am clear this time, as to what I was trying out

Please help me experts... Thanks in advance
0
 
XxtremeProAuthor Commented:
slighwv thanks for the post

Thinking it works I see the output as below

COL1                     DATE_END
000000001        12/31/9999 00:00:00
001000003        12/31/9999 00:00:00
004141413        12/31/9999 00:00:00
074747472        12/31/9999 00:00:00


1) The col1 records are all having date_end as null and so expecting every value under date_end to be 12/31/9999 is normal, but showing something different is abnormal like 12/31/9999 00:00:00

I would not like to display  00:00:00 after the date how to avoid ??

I think we are almost close to the solution here....

Thanks Expert.........

0
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
What does the application on the other side expect as a data type?  An Oracle date datatype always contains time.  To remove it, convert it to a string:

SELECT COL1, TO_CHAR(NVL(DATE_END,to_date('12/31/9999','MM/DD/YYYY')), 'MM/DD/YYYY') DATE_END
        FROM TABLE1
        WHERE COL1 IN('1','2','3','4','5','6','7','8','9','10','11','12')
    ORDER BY 1;
0
 
slightwv (䄆 Netminder) Commented:
>>I would not like to display  00:00:00 after the date how to avoid ??

Does non-null date_end have a time portion that you might want displayed?
0
 
XxtremeProAuthor Commented:
non-null date_end does not have a time portion displayed

nor do I want the time portion to be displayed...
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Cool.  The to_char wrapper should take care of it.
0
 
XxtremeProAuthor Commented:
Would like to thank all experts for especially their valuable time and their expert knowledge
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.