• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

removing records where a field is null

 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hmm i am a bit confused, not sure if i am going about this the right way...
The following works, returns 3 results..
select P_ID, PROJECT_NAME
from PROJECT
where PARENT_P_ID > 0;
but then when i use the following query instead, I get "no lines selected"...
select P_ID, PROJECT_NAME
from PROJECT
where PARENT_P_ID > NULL;
I wonder if i am checking for nulls correctly?
TIA
 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
just realised i just got the topic title a bit hazy, sorry if there is any confusion, thanks in advance for any help.
 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
also tried
select P_ID, PROJECT_NAME
from PROJECT
where PARENT_P_ID != NULL;
with no results returned... i dont understand, there are three projects that DO have a parent project and thus a PARENT_P_ID, but i cant seem to get them returned unless i do the first query... strange...
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
where PARENT_P_ID != NULL;

the correct syntax is " where not PARENT_P_ID is NULL"
As to the other questions, are you on drugs or something??
 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hey man, thanks for answering my question.
now i gotta ask, what on earth do you mean??? like, yeah sure, i've had some high times, up until sunrise with some crazy ladeez dropin mad flava tracks with them dancin around like butterflies... but err, not when trying to learn Oracle my good man.
Or did you read my question about multiple outer joins and thought it was so incredibly complex that someone only on drugs could have even discovered such a question? no, i thought not.
hmmm... yeah, i just thought well, you know, like you can do stuff like ... okay it was rather bizarre to suggest the etc > NULL ...
i am currently jumping right into oracle, vb and java at the moment, and yes, sometimes the synapses do get a little crossed, haha.
...greater than null, what was i thinking about!?!?!?!, doh!
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jasper, I think your real confusion lies with the real value of Null.
Whenever you do any comparison to a null value, the result is null.
Meaning a>null = null
a + null = null
(a != null) = null
Like Simon wrote, you need the is null or is not null to compare a value to null.
Now sometimes you will need to use the NVL(value,default) function,
default means, what value do you want to put into the field if it happens to be null.
So as an example if you want to add two numbers together and one of them could possibly be null, then you want to use the NVL function if you want to make sure you always get a value.
If you have data like

and you want results like

then you use the NVL function like so
NVL(fielda,0) + NVL(fieldb,0)
If you want null value results like

then you don't need any function at all.
Hope that helps clear things up.
one interesting chart to see is this

That states the results when you "AND"
when you OR the results are as follows.

If you don't believe the TRUE or NULL = TRUE you can run the following query to prove it
select count(*) from dual
where 1=NULL OR 1=1
Mark
[ May 30, 2003: Message edited by: Mark Spritzler ]
 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
okay, i get where you're coming from Mark, and it is good to see it written out, because i had a sneaking suspicion that something was just turning up strange because i was trying to compare things to null ... i forgot the 'is' option.
now concerning the null OR true = true... i believe you, but i wonder if it is because trying to get the a boolean on whether the value of 1=NULL is essentially similar to getting a false response, and seeing we are "OR"-ing, and the second condition being true, then it can return a value of true ... but... are you relating this as theory to the actual functionaloty of the NVL() comparison/assignment?
 
Jasper Vader
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
oh dear me, i just realised the clue to my original question was actually in the title of the thread!!! hahaha
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic