Informix equivalent to GREATER function?

I am looking for an Informix equivalent to the Oracle GREATER function, which essentially takes two parameters and returns the greater of those two values. It is typically used on an UPDATE statement, when you want to update a column IF the value you supply in a host variable is greater than what is in the database. An example of what I'm talking about is: UPDATE mytable SET thevalue = GREATER(thevalue, 5) where keycolumn = '123'; In an application, 5 would really be a host variable instead. This statement would put a 5 in column thevalue if 5 was greater than the current value in that column; otherwise the column would be left alone. In DB2, you can achieve a similar affect but it's more difficult and uses the CASE statement that DB2 supports. Is there an equivalent way to do this in Informix? -- Regards, Jim

Jim Morgan wrote: > > I am looking for an Informix equivalent to the Oracle GREATER > function, which essentially takes two parameters and returns the > greater of those two values. It is typically used on an UPDATE > statement, when you want to update a column IF the value you supply in > a host variable is greater than what is in the database. An example > of what I'm talking about is: > > UPDATE mytable SET thevalue = GREATER(thevalue, 5) where keycolumn => '123'; > > In an application, 5 would really be a host variable instead. This > statement would put a 5 in column thevalue if 5 was greater than the > current value in that column; otherwise the column would be left > alone. In DB2, you can achieve a similar affect but it's more > difficult and uses the CASE statement that DB2 supports. > > Is there an equivalent way to do this in Informix? Well, if the version you're using supports CASE, then you can use CASE. If the version you're using supports user-defined (C) functions, you could write one of them. Assuming the version you're using supports stored procedures, you can use one of them; but it isn't built in to any version of Informix that I know of. CREATE PROCEDURE GREATER_INT(v1 INTEGER, v2 INTEGER) RETURNING INTEGER; IF v1 > v2 THEN RETURN v1; ELSE RETURN v2; END IF; END PROCEDURE; This of course ignores the possibility that either v1 or v2 might be null, thus throwing a spanner in the works. It's a bit harder to write, but not a lot. One advantage of a built-in version is that you can get the generic type handling; you could probably do that with IUS and a C function. Of course, if you'd said which version of Informix you were using, the answer could be more precise... -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>