• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Junilu Lacar
  • Liutauras Vilda
Sheriffs:
  • Paul Clapham
  • Jeanne Boyarsky
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Piet Souris
  • Carey Brown
Bartenders:
  • Jesse Duncan
  • Frits Walraven
  • Mikalai Zaikin

removing records where a field is null

 
Ranch Hand
Posts: 57
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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...
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
ranger
Posts: 17346
11
Mac IntelliJ IDE Spring
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
pie. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic