Re: Does Informix have these ORACLE like functions?

Peter J. Diaz de Leon <pdiazdeleon@infinityhealthcare.com> wrote in article <6d2a3u$gnv$1@news2.alpha.net>... > > I am wondering if Informix has functions in it's SQL that behave > in a similar way as the following functions behave in ORACLE's > SQL PLUS. > > 1) NVL(value, substitute) > > example: select first_name, NVL(last_name, :SMITH) from names; > > what this select would do is select the first and last names > from the names table, and if it finds any of the rows in the > result set with a null last name it replases it with SMITH. > > > 2) DECODE(value, if1, then1, if2, then2, ..... else) > > allows an if then else contruct within the select > statement. > > 3) Example; select TODAY from systables; > > Is there anyway of re-formating the result > within the select statement. > > <snip> A lot of this can be done by creating stored procedure functions. You won't get the exact same functionality as the Oracle extensions, but you can come pretty close. SPLs semi-strong typing may mean having to write several versions of the same function for different types of parameters and results. I have a set of spread-sheet like IF functions for example: ifcc (char1, operator, char2, truechar, falsechar): IF involving character comparisions returning a chracter result. ifcn (char1, operator, char2, truenum, falsenum): IF involving character comparisions returning numeric result. ifnd (num1, operator, num2, truedate, falsedate): IF involving numeric comparisions returning datetime result. ... etc. also: ifcnull (char1, value): Returns "value" if "char1" is null, otherwise returns char1. ifnnull (num1, value) ifdnull (date1, value) So following your example: select fist_name, ifcnull(last_name, "SMITH") from names; Let me know if you're interested and I'll post the source for these stored procedures. Irwin Goldstein Objective Software Systems, Inc. http://www.objectsoft.com (Remove anti-spam characters from e-mail address to reply via e-mail.)