CODEWITHSUNDEEP
sql
sc1324
sc1324

Reputation: 600

or statement in SQL

I am trying to use or statement in SQL but it's not producing the results I want.

Here I want to expand the code so they are either 0012 or 0005 with the rest of conditions being the same. Here is the codes I am trying to modify, and I just added second line but it doesn't get what I wanted.

 WHERE pr.code = '0005'
      OR pr.code = '0012'
      AND p.effdate > '2/8/2017'
      AND p.pnum not like 'R%'
      AND p.status like '6'

Upvotes: 1

Views: 95

Answers (4)

streetturtle
streetturtle

Reputation: 5850

You need to add parenthesis:

WHERE (pr.code = '0005' OR pr.code = '0012')
  AND p.effdate > '2/8/2017'
  AND p.pnum not like 'R%'
  AND p.status like '6'

And in this case, as @MiloBellano mentioned in his answer, you can prevent such errors by using IN operator:

WHERE pr.code IN ('0005', '0012')
  AND p.effdate > '2/8/2017'
  AND p.pnum not like 'R%'
  AND p.status like '6'

Upvotes: 4

MiloBellano
MiloBellano

Reputation: 396

  WHERE (pr.code = '0005'
  OR pr.code = '0012')
  AND p.effdate > '2/8/2017'
  AND p.pnum not like 'R%'
  AND p.status like '6'

But I think that 

  pr.code in ('0005','0012')
  AND p.effdate > '2/8/2017'
  AND p.pnum not like 'R%'
  AND p.status like '6'

looks nicer and probably faster

Upvotes: 1

David Adlington
David Adlington

Reputation: 666

 WHERE (pr.code = '0005'
      OR pr.code = '0012')
      AND p.effdate > '2/8/2017'
      AND p.pnum not like 'R%'
      AND p.status like '6'

Upvotes: 0

theGleep
theGleep

Reputation: 1187

One word: Parenthesis

(extra words to make the post long enough)

Upvotes: 1

Related Questions