aspose file tools*
The moose likes Oracle/OAS and the fly likes removing records where a field is null Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "removing records where a field is null" Watch "removing records where a field is null" New topic
Author

removing records where a field is null

Jasper Vader
Ranch Hand

Joined: Jun 10, 2002
Posts: 57
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


giddee up
Jasper Vader
Ranch Hand

Joined: Jun 10, 2002
Posts: 57
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

Joined: Jun 10, 2002
Posts: 57
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

Joined: Sep 28, 2001
Posts: 925
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

Joined: Jun 10, 2002
Posts: 57
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

Joined: Feb 05, 2001
Posts: 17250
    
    6

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 ]

Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Jasper Vader
Ranch Hand

Joined: Jun 10, 2002
Posts: 57
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

Joined: Jun 10, 2002
Posts: 57
oh dear me, i just realised the clue to my original question was actually in the title of the thread!!! hahaha
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: removing records where a field is null