CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clause
Jabo13
Jabo13

Reputation: 65

SQL - Need a way around multiple WHERE restraints

I have an aggregate function in my WHERE statement along with other more normal WHERE restraints. If I use all AND statements to tie the WHERE statement together, then the query checks out fine.

However, I need to use an OR statement to have the proper data. When I try to run the query with the OR statement, I receive a "Divide by Zero" error. It is occurring despite one of my restraints is that the ExampleQuantity cannot equal 0.

How should I work around this to where I can both use the OR statement, and not have a divide by zero error? I'm sure that there is an easy solution to this, I just cannot see it.

Here is an example of my query: 

SELECT *
FROM table1
WHERE ExampleCode1 BETWEEEN 1 AND 10 
  AND ExampleNo IN (1, 2, 3, 4)
  **AND/OR** ExampleQuantity =! 0 
  AND ExamplePrice/ExampleQuantity = ExampleAmount

Upvotes: 2

Views: 125

Answers (2)

Stavr00
Stavr00

Reputation: 3314

As jean suggests, simply rewriting the division as a multiplication avoids dividing by zero.

SELECT *
FROM table1
WHERE ExampleCode1 BETWEEEN 1 AND 10 
AND ExampleNo IN (1, 2, 3, 4)
OR  ExamplePrice * ExampleQuantity = ExampleAmount

I think your test may be wrong, usually price means price per unit and amount represents total price

Upvotes: 4

Luc M
Luc M

Reputation: 17324

SELECT * FROM (
   SELECT *
   FROM table1
   WHERE ExampleCode1 BETWEEEN 1 AND 10 
   AND ExampleNo IN (1, 2, 3, 4)
   AND ExampleQuantity =! 0 
) tab_tmp
WHERE AND ExamplePrice/ExampleQuantity = ExampleAmount;

Upvotes: 1

Related Questions