help with WHERE clause

[Lance Gurd]

We have a standard purchase order system, which has purchased items linked to purchase header records via purchase order_no.

These purchase orders can be collected from suppliers and taken into our stock( named 'Lock-up') or straight to a job. Also they can be taken from our stock to a job.

What I am trying to achieve is a grid based on a mySQL statement that shows only collected orders where there is a difference between 2 columns (purchase_items2->collected is less than purchase_items2->quantity) or the supplier is Lock-up and there is the same cryteria for the difference between the 2 columns. I have a logical field to determin if the order is to be delivered by the supplier, which is purchase_header2.DEL_COL true for delivery false for collection.

This what I have got to

WHERE purchase_items2.collected < purchase_items2.quantity AND purchase_header2.DEL_COL = .f. OR suppliers1.sup_name = 'Lock-up' AND purchase_items2.collected < purchase_items2.quantity

And have tried variations with parenthesis in various places (but Alpha seems to remove them from the overall select statement for some reason). But while it seems to give correct results for the first half - up to the OR statement - the second half returns eratic results. (Orders where del_col = .t. and some purchase_items2 where collected = quantity)

Anyone good with WHERE statements got any ideas please.

[StephenP]

Re: help with WHERE clause

It looks to me like what you have is:

WHERE a AND b OR c AND a

which can be simplified to:

WHERE a AND (b OR c)

However, OR is evaluated before AND, so your expression evaluates as:

WHERE a AND (b OR c) AND a

Since OR evaluates first, I would write it thus:

WHERE (b OR c) AND a

That way, if A5 drops the parentheses, you still get the correct results (or should).

Hope this helps.

[Lance Gurd]

Re: help with WHERE clause

Thanks Stephen,

Did not know about OR evaluating first, had already tried the simplified version, changed it around as in (b OR c) AND a and am still getting the same results(the parethesis did persist this time, however had to manually type the select statement as fields/values kept getting dropped by the SQL genie). Also get same resultset from Toad and Navicat, which itimates it could be something to do with my select statement in general. None of my recent purchase orders are showing???

Here is the full statement

SELECT purchase_header2.ORDER_NO, purchase_header2.DEL_COL, all_jobs.JOB, suppliers1.sup_name, purchase_header2.DEL_DATE, purchase_header2.JOB_NO, purchase_header2.SUP_NO, purchase_items2.a5fn_desc, purchase_items2.quantity, purchase_items2.collected
FROM (jobs.purchase_header2 purchase_header2
INNER JOIN jobs.purchase_items2 purchase_items2
ON purchase_header2.ORDER_NO = purchase_items2.order_no
INNER JOIN all_jobs all_jobs
ON purchase_header2.JOB_NO = all_jobs.JOB_NO
INNER JOIN suppliers1 suppliers1
ON purchase_header2.SUP_NO = suppliers1.refnum )
WHERE ( purchase_header2.DEL_COL = 0 OR purchase_header2.SUP_NO = 209 ) AND purchase_items2.collected < purchase_items2.quantity
GROUP BY purchase_header2.ORDER_NO
ORDER BY purchase_header2.ORDER_NO DESC

I am confused.

[Lance Gurd]

Re: help with WHERE clause

Found the problem

purchase_items2.collected is set to (NULL) and should be 0

Sorry for any confusion caused