I dont see the difference between.. Amount and Ord_Head.Amount

Isnt it the same thing?

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Tuesday, April 01, 2008 4:41 PM
Subject: Re: [IBO] SQL update problem?


At 05:49 AM 2/04/2008, you wrote:
>Hi, This Query used to work perfect with firebird 1.5, now it doesnt with 2.0. Even if the field "Amount" is Null...
>
> Update Ord_Head set Ord_Head.Amount=:Amount
> Where ord_Head.OrderNum=:OrderNum
> and ord_Head.Amount <> :Amount

It should be
Update Ord_Head set Amount=:Amount
Where OrderNum=:OrderNum
/* and Amount <> :Amount */

Use qualifiers when they are needed, i.e., when the statement involves multiple sets. A simple DML statement doesn't need them. Fb 2 is stricter than before about them. Also, you need IBO 4.8 to work correctly with the new identifier rules, which have tightened a lot.

>I added this code, so it wouldnt made a change, unless the amount is different
> "and ord_Head.Amount <> :Amount"
>
>If I remove that line, works perfect..

You don't need it anyway.

>I thought that it would save resources, as this query is called often..

It doesn't save resources, it uses MORE. The resources are taken up with finding the record. Adding an extra search criterion - in this case probably one that isn't even indexed - you use *more* resources, not less. You could justify it if, for example, you had a trigger that updates something else. And a <> search is more costly than any other kind.

>When it does the evaluation, it seems like it looks at the "Amount" as if the field has already been updated..
>Weird..
>Am I missing something?

I don't know how you deduce that. But one thing you do seem to miss is that a (value <> null) search will not return a record for update. If you needed to include that search (which you don't) it would need to be

and (Amount <> :Amount or Amount is null)

But, as I mentioned before, it is unnecessary and costly.

Helen




[Non-text portions of this message have been removed]