Subject | Re: [firebird-support] Case vs. update |
---|---|

Author | Ann W. Harrison |

Post date | 2005-08-30T22:19:02Z |

semprolbat wrote:

set a = case (a-b >=0) then a-b else 0,

set b = case (a-b >=0) then 0 else -(a-b)

where (a > 0) and (b > 0)

There is a messiness in the Firebird handling of values that will break

this particular example. The SQL standard specifies that the values

used as input to a set operation are the old values, even if new values

have been assigned to those fields. Firebird doesn't follow the

standard in that case, so the set statement that assigns a new value to

a will affect the results of the assignment to b.

Regards,

Ann

>Case is a value expression, not an operator.

> Does case work in update statements, and if not, is this feature

> planned?

>update tablea

> update

> tablea

> case

> when (a-b >= 0) then set a = a-b, b = 0

> else set a = 0, b = -(a-b)

> where

> (a > 0) and (b > 0)

> ;

>

set a = case (a-b >=0) then a-b else 0,

set b = case (a-b >=0) then 0 else -(a-b)

where (a > 0) and (b > 0)

There is a messiness in the Firebird handling of values that will break

this particular example. The SQL standard specifies that the values

used as input to a set operation are the old values, even if new values

have been assigned to those fields. Firebird doesn't follow the

standard in that case, so the set statement that assigns a new value to

a will affect the results of the assignment to b.

Regards,

Ann