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

null value

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
chalie001
Asked:
chalie001
  • 3
  • 3
  • 2
  • +3
2 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you are seeing that issue in sql*plus or toad or any other app/tool ?
0
 
Pawan KumarDatabase ExpertCommented:
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
Technology Partners: 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!

 
Geert GruwezOracle dbaCommented:
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
 
PortletPaulCommented:
@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
 
Geert GruwezOracle dbaCommented:
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
 
chalie001Author Commented:
i use is null
0
 
PortletPaulCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Geert GruwezOracle dbaCommented:
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
 
PortletPaulCommented:
Geert, thanks. i understand now. I  didnt befote.

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

Featured Post

Industry Leaders: 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!

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