WolfieeifloW WolfieeifloW - 3 years ago 123
SQL Question

SQL WHERE Field1 >= 1 OR Field2 >= 1 Allowing (null) Values

I'm trying to query a database table and have the following WHERE clause on my query:

WHERE (QTY_ON_HAND >= 1 OR QTY_ON_ORDER >= 1);


I am wanting to return all results who have either quantity on hand OR quantity on order.

However, this is allowing records to return that have
0 QTY_ON_HAND
and
(null) QTY_ON_ORDER
.

Scrolling throug the results, it's even allowing
-1
(And other negatives) and
NULL
.

enter image description here

Here is the rest of the WHERE clauses:

WHERE CHARGE_CODE = 'RETAIL' OR CHARGE_CODE = 'RETAILSN' AND
LOCATION = 100 AND
SPA_ITEM_ID LIKE '1%' AND
(QTY_ON_HAND >= 1 OR QTY_ON_ORDER >= 1);


Solution:

Forgot the parenthesis on the first WHERE clause

CHARGE_CODE = 'RETAIL' OR CHARGE_CODE = 'RETAILSN'


needs to be

(CHARGE_CODE = 'RETAIL' OR CHARGE_CODE = 'RETAILSN')

Answer Source

AND operator has precedence over OR, so you need to use parentheses:

WHERE 
   ( CHARGE_CODE = 'RETAIL' OR CHARGE_CODE = 'RETAILSN' )
   AND LOCATION = 100
   AND SPA_ITEM_ID LIKE '1%'
   AND ( QTY_ON_HAND >= 1 OR QTY_ON_ORDER >= 1 )

Better yet using the IN operator:

WHERE 
   CHARGE_CODE IN( 'RETAIL', 'RETAILSN' )
   AND LOCATION = 100
   AND SPA_ITEM_ID LIKE '1%'
   AND ( QTY_ON_HAND >= 1 OR QTY_ON_ORDER >= 1 )

More on the matter explained in documentation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download