null value

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 ?

you are seeing that issue in sql*plus or toad or any other app/tool ?

Try 1

--

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

--

Select allOpen in new window

Try 2

--

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

--

Select allOpen in new window

Try 3

--

select empno,hire_date from employee where hire_date = ''

--

Select allOpen in new window

@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
===============================================================

Select allOpen in new window

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 ?

i use is null

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

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 = ' '

Select allOpen in new window

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

      EMPNO      HIRE_DATE
1      1

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.

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 ?

Geert, thanks. i understand now. I  didnt befote.

chalie001
I am not comfortable having points for asking a question