Solved

How do I generate based on conditions

Posted on 2011-09-15
22
285 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
  • 9
  • 9
  • 4
22 Comments
 
LVL 34

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 76

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
 

Author Comment

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

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 34

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 76

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 76

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
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.

 
LVL 34

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 76

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 76

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 34

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 76

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 76

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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36546667
Glad to help.
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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…

708 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

12 Experts available now in Live!

Get 1:1 Help Now