Re: [Info-ingres] Coalesce equivalent in CA Ingres?

At 2:38 AM -0800 1/18/06, peter koch wrote: 
I have to update an element in a table with a value taken from another
table where i want to use a default value if the element in the other
table is not found.


[snip]
The update does not work, however:

update REL2
  set REL2.FIELD2 =
(select FIELD1 from REL1 where REL1.KEY = 100
union  select 500 from rel1 where not exists (select * from REL1 where
REL1.KEY = 100))


You would be able to work around this with outer join, except that
unfortunately the UPDATE statement doesn't support outer join in its
weird variant of the FROM clause.  Right now I can't think of anything
better than using two update statements, e.g.:

update rel2 from rel1
    set field2 = rel1.field1  /* Note NOT rel2.field2 = ... */
    where rel1.key = 100;
update rel2
    set field2 = default
    where 100 not in (select key from rel1);  /* or the NOT EXISTS variant */

well, OK, I just came up with this truly horrible disgustingness that
would probably work as long as there's at least one row in rel1:

update rel2 from rel1
    set field2 = case when rel1.key = 100 then rel1.field1
                 else default end
    where rel1.key = 100 or rel1.key = (select min(key) from rel1);

Ugh.  Bleah.

Both of these strongly assume that "rel1.key = 100" will match zero or
one rows in rel1.  The second variation further assumes that rel1.key
is unique (if it isn't, you could use tid which is even dirtier).

To reply specifically to your subject:  IFNULL or case expressions can
be used for COALESCE in Ingres, and I believe that r3 supports
COALESCE directly.

Karl
.