1

So my query is this

SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation FROM Sites WHERE OnHold<>'' or Urgent<>'' or MissingInformation<>'' or SARequired<>'' and PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived') Order By SiteNumber asc

where PM is not Null - I have also tried in vb.net to set the value to 

PM<>dbnull.value 
'also tried 
PM<> is null 
'or just 
PM<> Null

None of them seem to leave out the nulls

also confusing me that I'm asking to only see PM where PM='K' as in the query above but I see 'R'

Here is what I'm getting back

pm
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
R
NULL
NULL
NULL

Yes on purpose I tested 'K' for the PM Column since I know I don't have any

I'm sure its something stupid simple , just can't put my finger on it

Any help is appreciated

Improve this question
1
  • You have 'pm' at the beginning of your SELECT and then later use 'PM'. That can make a difference. SELECT **PM**, SiteNumber, Client, OnHold, Urgent, SARequired, MissingInformation FROM Sites WHERE OnHold<>'' or Urgent<>'' or MissingInformation<>'' or SARequired<>'' and PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived') Order By SiteNumber asc Commented Apr 26, 2013 at 15:48

2 Answers 2

Reset to default
3

Just use IS NOT NULL. But the problem in your query are the ORs.

formatted:

SELECT columns
FROM   sites 
WHERE  onhold <> '' 
        OR urgent <> '' 
        OR missinginformation <> '' 
        OR sarequired <> '' 
           AND pm = 'K' 
           AND pm IS NOT NULL 
           AND ( projectstatus <> 'Complete' 
                  OR projectstatus <> 'Archived' ) 
ORDER  BY sitenumber ASC

If you want to exclude NULLS in any case put the rest of the conditions in parentheses:

SELECT columns 
FROM   sites 
WHERE  pm IS NOT NULL 
       AND ( onhold <> '' 
              OR urgent <> '' 
              OR missinginformation <> '' 
              OR sarequired <> '' 
                 AND pm = 'K' 
                 AND ( projectstatus <> 'Complete' 
                        OR projectstatus <> 'Archived' ) ) 
ORDER  BY sitenumber ASC

Edit: how can we fix the R showing up instead of K when i tell it to look for K not R

I must have overlooked that question. When you only want to have rows with pm = 'K' you don't need to ask for non-null rows at all because pm = 'K' is not-null implicitly:

WHERE  pm = 'K' 
           AND ( onhold <> ''  ...
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Wow thats absolutely solving the Null issue thanks! - Now how can we fix the R showing up instead of K when i tell it to look for K not R.? EDIT: i had to switch one of the brackets around to get the and seperated from the or's - THANKS will mark correct as soon as it lets me ( 5 min or so )
3

You need parentheses around the initial or conditions in the where clause.

SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation
FROM Sites
WHERE (OnHold<>'' or Urgent<>'' or MissingInformation<>'' or SARequired<>'') and
      PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived')
Order By SiteNumber asc

In SQL, AND has higher precedence than OR. So, the original statement was parsing as:

SELECT pm,SiteNumber,Client,OnHold,Urgent,SARequired,MissingInformation
FROM Sites
WHERE OnHold<>'' or Urgent<>'' or MissingInformation<>'' or 
      (SARequired<>'' and PM='K' and PM is not null and (ProjectStatus<>'Complete' or ProjectStatus<>'Archived'))
Order By SiteNumber asc
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.