Solved

null value

Posted on 2016-11-04
15
120 Views
Last Modified: 2016-12-05
hi i have this sqql select empno,hire_date from employee where hire_date is null am geting 0 rows bust when i do select * from employee i can see empty value in hire_date column
hire_date date datatype
0
Comment
Question by:chalie001
[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
  • 3
  • 3
  • 2
  • +3
15 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 41873568
select empno, nvl(hire_date,'01-jan-2005') hiredate_with_nvl. hiredate from employee;

Can you provide the output of this in excel or screenshot ?
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 41873570
you are seeing that issue in sql*plus or toad or any other app/tool ?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873571
Try 1

--

select empno,hire_date from employee where hire_date is null OR hire_date = ''

--

Open in new window



Try 2

--

select empno,hire_date from employee where LENGTH(hire_date) = 0

--

Open in new window


Try 3

--

select empno,hire_date from employee where hire_date = ''

--

Open in new window

0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 37

Accepted Solution

by:
Geert Gruwez earned 250 total points
ID: 41873590
use dump(column) to see what's really in it


with employee as 
   (select 1 empno, cast('  ' as varchar2(50)) hire_date from dual
    union all select 2 empno, to_char(sysdate) hire_date from dual)
 select empno,hire_date, dump(hire_date) from employee

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41873714
@Geert
What do 2 lines "from dual"  achieve?

with employee as 
   (select 1 empno, cast('  ' as varchar2(50)) hire_date from dual
    union all 
    select 2 empno, to_char(sysdate) hire_date from dual
   )
select empno,hire_date, dump(hire_date) from employee

===============================================================
 	EMPNO	HIRE_DATE	DUMP(HIRE_DATE)
 	1			Typ=1 Len=2: 32,32
 	2	04.11.16	Typ=1 Len=8: 48,52,46,49,49,46,49,54
===============================================================

Open in new window

0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41873758
Paul,
 Really ?

You have never been on a database where there is no employee table ?
you have never had to conjure up some sample rows to explain something using "from dual" ?

And you got to level 47 how exactly ?
0
 

Author Comment

by:chalie001
ID: 41873795
i use is null
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41873796
sorry... It was a serious question
I just don't follow the logic as you haven't unioned the table employee at all, you just have 2 rows from dual

did you intend to union to the table?

forgive me I was attempting to help
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 41873850
Just a comment on using empty strings.  Oracle does not support empty strings, it equates them to NULL.

So, this:

hire_date = ''

will always return false.  It is impossible for anything to ever equal null.

Try them out:

select * from dual where '' = '';
select * from dual where null = '';
select * from dual where null = null;
select * from dual where '' is null;

Only the last query will return a result.  All others will return no rows.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41873857
This is working for me .. :)

with employee as 
   (select 1 empno, cast(' ' as varchar2(50)) hire_date from dual
    union all select 2 empno, to_char(sysdate) hire_date from dual)
    
select empno,hire_date
from employee
WHERE hire_date = ' '

Open in new window


Output...
----------------------------------

      EMPNO      HIRE_DATE
1      1
0
 
LVL 35

Expert Comment

by:johnsone
ID: 41873864
That has a space in it, it is not an empty string.  What was posted earlier is:

hire_date = ''

No space, so it is an empty string.
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41874038
Paul,
i can't union the employee table, as i don't have a database here with an employee table
i don't have access to chalie001's database, i think
> you never one when a new user with a certain alias is asking question on a site and actually sitting next to you

chalie001,
how come paul get's point by using my sample, but i don't ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41874152
Geert, thanks. i understand now. I  didnt befote.

chalie001
I am not comfortable having points for asking a question
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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.

752 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