removing records where a field is null

[Jasper Vader]

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]

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]

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]

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]

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]

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

fielda    fieldb
 12         13
 null       15
 9          null

and you want results like

fielda+fieldb
  25
  15
   9

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

fielda+fieldb
  25
 null
 null

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

 
-----------------------------
| AND   TRUE   FALSE    NULL |
| TRUE  TRUE   FALSE    NULL |
| FALSE FALSE  FALSE    NULL |
| NULL  NULL   NULL     NULL |
------------------------------
 

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

 
------------------------------
| OR    TRUE   FALSE    NULL |
|TRUE   TRUE   TRUE     TRUE |
|FALSE  TRUE   FALSE    NULL |
|NULL   TRUE   NULL     NULL |
------------------------------
 

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]

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]

oh dear me, i just realised the clue to my original question was actually in the title of the thread!!! hahaha