• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

Date comparison with Null

I have a query wherin I do a date comparison with another date.
This works fine except in cases where the date is NULL (when there is no
record)

For eg:

Select * from Employee where status ='Present' and emp_id=10 and date > 
( select max(date) from Employee where emp_id=10 and status not in ('Work from home','On Site') )


This works great when the inner query returns a value .But when its NULL I get an empty set in the whole query as well .
0
Shaunsmith
Asked:
Shaunsmith
  • 6
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
What do you want the where clause to look like if there is no max date from the subquery?
0
 
slightwv (䄆 Netminder) Commented:
Just a guess but try:

Select * from Employee where status ='Present' and emp_id=10 and date > 
nvl(( select max(date) from Employee where emp_id=10 and status not in ('Work from home','On Site') ),sysdate)
0
 
slightwv (䄆 Netminder) Commented:
I believe your query can be simplified but I'm not follwing the logic.

Can you provide some sample data where a row should be returned?

When I dummy up data the MAX(DATE) where status is not in ('Work from home','on site'), is the date for 'Present' so I never get rows.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
ShaunsmithAuthor Commented:
>>What do you want the where clause to look like if there is no max date from the subquery?

If there is no Max Date I would like the Outer Query to disregard the Inner Query ...in otherwords the outerquery Date is always greater than the inner query date (which is null)

i.e
Select * from Employee where status ='Present' and emp_id=10 and date >  NULL

=

Select * from Employee where status ='Present' and emp_id=10

Thanks for your time
0
 
slightwv (䄆 Netminder) Commented:
Here is my test case I was playing with and the SQL I think might work and only hits the table once.

drop table tab1 purge;
create table tab1(
	emp_id number,
	status varchar2(10),
	some_date date
);

insert into tab1 values(10, 'Present',to_date('02/02/2002','MM/DD/YYYY'));
insert into tab1 values(10, 'Fred',to_date('01/01/2001','MM/DD/YYYY'));
commit;


Select emp_id from tab1 where status ='Present' and emp_id=10 and some_date > 
(
	select max(some_date) from tab1 where emp_id=10 and status not in ('Work from home','On Site')
)
/


select emp_id from
(
Select e.*,
	case when status='Present' then some_date end is_present,
	case when status not in ('Work from home','On Site') then some_date end is_not_in_list
from tab1 e where emp_id=10
)
where is_present > is_not_in_list
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
While I wait for an update to my test case.

>>in otherwords the outerquery Date is always greater than the inner query date (which is null)

Just create some small date that can never exist?

Select * from Employee where status ='Present' and emp_id=10 and date > 
nvl(( select max(date) from Employee where emp_id=10 and status not in ('Work from home','On Site') ),to_date('01/01/0001','MM/DD/YYYY'))
0
 
awking00Commented:
You could also add -
Select * from Employee where status ='Present' and emp_id=10 and (date is null or date > 
( select max(date) from Employee where emp_id=10 and status not in ('Work from home','On Site') ) )
0
 
awking00Commented:
Sorry, it's the max(date being null that is the problem. You can use slightwv's suggestion like
Select * from Employee where status ='Present' and emp_id=10 and date > 
( select coalesce(max(date),to_date('00010101','yyyymmdd')) from Employee where emp_id=10 and status not in ('Work from home','On Site') ) )
0
 
slightwv (䄆 Netminder) Commented:
>>coalesce(max(date),

I don't think that will work either.   I read the question to mean the where clause in the sub select returns no rows.  If so, the coalesce wil not work.
0
 
awking00Commented:
shaunsmith,
I think slightwv's interpretation of the question is probably correct. Can you post the query that actually worked?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now