Solved

How do I generate based on conditions

Posted on 2011-09-15
22
289 Views
Last Modified: 2012-05-12
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....

0
Comment
Question by:XxtremePro
[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
  • 9
  • 9
  • 4
22 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 36543538
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
 

Author Comment

by:XxtremePro
ID: 36543684
one of my colleagues suggests using in between so and so date i.e, from Date1 to Date2

any thoughts on that... experts...
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36543713
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:XxtremePro
ID: 36543763
I just need one concrete way.... need to tackle the animal soon with the experts help.... :)
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36543843
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
 
LVL 35

Expert Comment

by:johnsone
ID: 36544185
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
 

Author Comment

by:XxtremePro
ID: 36544885
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36544944
>>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
 

Author Comment

by:XxtremePro
ID: 36545003
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36545039
>>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
 

Author Comment

by:XxtremePro
ID: 36545151
Yes

DATE_END SUPPOSED TO CONTAIN '12/31/9999' WHERE IT EQUALS NULL
0
 
LVL 35

Expert Comment

by:johnsone
ID: 36545195
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 350 total points
ID: 36545223
>>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
 

Author Comment

by:XxtremePro
ID: 36545476
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 350 total points
ID: 36545553
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
 

Author Comment

by:XxtremePro
ID: 36545661
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
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 150 total points
ID: 36545688
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36545745
>>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
 

Author Comment

by:XxtremePro
ID: 36545967
non-null date_end does not have a time portion displayed

nor do I want the time portion to be displayed...
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 350 total points
ID: 36546305
Cool.  The to_char wrapper should take care of it.
0
 

Author Closing Comment

by:XxtremePro
ID: 36546644
Would like to thank all experts for especially their valuable time and their expert knowledge
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36546667
Glad to help.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 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