Using OR in SQL clause

Hi,

I have a sql query looking like this:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
OR PaymentTypeID  = 12
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC

Open in new window

The problem is now that when I use the OR keyword it chooses between this clause:
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 

Open in new window

and this:
OR PaymentTypeID  = 12

Open in new window


I can solve this by doing like this and this reflects the result I really want.
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
OR PaymentTypeID  = 12
AND (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND orderStatusID = 5 
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC

Open in new window


I wonder however if there is a smarter way to use the OR keyword so I don't have to repeat everything in the WHERE clause every time I use it?!

Thanks for help!

Peter
Peter NordbergIT ManagerAsked:
Who is Participating?
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You need to use brackets.

So you want lines 3 to 5 of your first code to read:


WHERE (o.invoiceID ...etc
AND (orderStatusID =5
OR PaymentTypeID = 12)

Note the additional brackets in lines 4 and 5.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's all about the precedence of the OR operator. You need to use parentheses:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND (orderStatusID = 5 OR PaymentTypeID  = 12)
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC

Open in new window

0
 
Peter NordbergIT ManagerAuthor Commented:
Hi,

I tried like this:
SELECT o.orderID, o.invoiceID, o.customerID, c.firstname + ' ' + c.lastname AS Name, o.invoiceDate, o.PayDate, o.ocr, paymentStatusID, orderStatusID, ISNULL(printed,0) AS printed, currencyID, paymentTypeID  
FROM orders AS o INNER JOIN Customer AS c ON o.customerID = c.CustomerID  
WHERE (o.invoiceID > '0') AND (o.orgID = 11) AND orderSaved = 1 AND o.orderStatusID > 0 AND (old IS NULL OR Old = 0) 
AND (orderStatusID = 5  OR PaymentTypeID = 12)
ORDER BY CONVERT (BIGINT, o.invoiceID) DESC 

Open in new window


But then I get this result (two of the instances are doubled)

SQL result
Peter
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
They aren't duplicated. They has different orderID, orderStatusID and paymentTypeID.
If you want only one of them then you need to say which one should be returned.
0
 
Peter NordbergIT ManagerAuthor Commented:
You're right. I was to fast in my judgement.

Peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.