• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 612
  • 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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

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

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

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

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