where selection excludes NULL values

[almaragni]

Can you help me with this question please.
I'm not a formally schooled DB/SQL person, and reading the help info just confused me more so.

i have - what i thought - was a simple query


select * from orders
where status <> 'HOLD'

the actual statement is more complex -with joins and such.
basically i want all orders that do NOT have a status which is HOLD

however, what appears to happen is that - i do NOT get those orders who's status is NULL and i am afraid to include the ( ... or status is NULL) to avoid orders that I may not what - do to other selection criteria

is my thinking incorrect?
must i include the additional criteria?

thank you

 

[almaragni]

note: this is a microsoft SQL 2000 installation
commands are run in query analyzer.

thx

 

[jbenson001]

Using or status IS NULL should get you what you want, based on the other criteria you have. It should then only add the rows where status IS NULL AND the other criteria.
The best way is to test it and find out if you get the desired output. If not, you may have to write two selects with different WHERE clauses and UNION them.

Jim

 

[gmmastros]

>> the actual statement is more complex -with joins and such.

jbenson001 is correct. Adding Or status is null should do the trick. However, if you implement this incorrectly, you will have problems. Specifically, if you have multiple AND conditions in your where clause, then you will need to use parenthesis. Like this...

Where Sky = 'Blue'
      And (status <> 'HOLD' or Status Is NULL)
      And Grass = 'Green'

Notice the placement of the parenthesis. It's important.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PWise]

try

where isnull(status ,'')<>'hold' and....