From: Vince Nichols <vnichols@sprynet.com>
Date: Mon, 03 Nov 1997 19:04:09 -0600

I would like to contribute this collection of SPL to the IIUG so that
others will not go through what I went though when asked for Oracle
function equivalents in Informix. These procedures may help people ween
themselves off of the dreaded 'O' word.

Regards,
Vince Nichols
IIUG#6889
Certified Informix Database Specialist
At Large.

#!/bin/sh
#
# This is a shell archive.  To extract its contents,
# execute this file with /bin/sh to create the file(s):
#
# proclib.sql
#
# This shell archive created: Tue Nov  4 10:27:55 EST 1997
#
echo "Extracting file proclib.sql"
sed -e 's/^X//' <<\SHAR_EOF > proclib.sql
X--------------------------------------------------------
X-- ceil()
X--------------------------------------------------------
XDROP PROCEDURE "informix".ceil;
XCREATE PROCEDURE "informix".ceil(num DECIMAL(32,8))
X        RETURNING DECIMAL(32);
X
XDEFINE trunc_num DECIMAL(32,0);
X
X   IF num IS NULL THEN
X     RETURN NULL;
X   END IF;
X
X   SELECT trunc(num) INTO trunc_num FROM systables WHERE tabid=1;
X
X   IF num = trunc_num THEN
X      RETURN num;
X   END IF;
X
X   IF num < 0 THEN
X      RETURN trunc_num;
X   ELSE
X      RETURN trunc_num + 1;
X   END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- char_to_date()
X--------------------------------------------------------
XDROP PROCEDURE "informix".char_to_date;
XCREATE PROCEDURE "informix".char_to_date(str VARCHAR(255),
X        frmt VARCHAR(255) DEFAULT 'DD-MON-YY')
X        RETURNING DATETIME YEAR TO SECOND;
X
XDEFINE dt DATETIME YEAR TO SECOND;
XDEFINE dt_str VARCHAR(255);
XDEFINE year SMALLINT;
XDEFINE i INT;
XDEFINE length_str INT;
XDEFINE month VARCHAR(255);
X
X--  raise exception on: -1213 character to numeric conversion error.
X--                      -1263 field in a datetime or interval value is out of
X--                            range or incorrect.
X-- The exception raised: -1276 format conversion character not supported.
X   ON EXCEPTION IN (-1213 , 1263)
X         RAISE EXCEPTION -1276;
X   END EXCEPTION;
X
X   IF str IS NULL THEN
X      RETURN NULL;
X   END IF;
X
X   SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
X   IF length_str = 0 THEN
X      RETURN NULL;
X   END IF;
X
X
X   LET dt = "0001-01-01 00:00:00";
X
X   IF ( frmt = 'HH24:MI:SS' OR frmt = 'hh24:mi:ss' ) AND length_str = 8 THEN
X      LET dt_str = dt;
X      LET dt_str[12,13] = str[1,2];
X      LET dt_str[15,16] = str[4,5];
X      LET dt_str[18,19] = str[7,8];
X      LET dt = dt_str;
X   ELIF ( frmt = 'YYMMDD' OR frmt = 'yymmdd' ) AND length_str = 6  THEN
X      LET dt_str = dt;
X      LET year = str[1,2];
X      IF year > 50 THEN
X         LET dt_str[1,2] = '19';
X      ELSE
X         LET dt_str[1,2] = '20';
X      END IF;
X      LET dt_str[3,4] = str[1,2];
X      LET dt_str[6,7] = str[3,4];
X      LET dt_str[9,10] = str[5,6];
X      LET dt = dt_str;
X   ELIF ( frmt = 'DDMMYY' OR frmt = 'ddmmyy' ) AND length_str = 6  THEN
X      LET dt_str = dt;
X      LET year = str[5,6];
X      IF year > 50 THEN
X         LET dt_str[1,2] = '19';
X      ELSE
X         LET dt_str[1,2] = '20';
X      END IF;
X      LET dt_str[3,4] = str[5,6];
X      LET dt_str[6,7] = str[3,4];
X      LET dt_str[9,10] = str[1,2];
X      LET dt = dt_str;
X   ELIF ( frmt = 'MM/DD/YY' OR frmt = 'mm/dd/yy' ) AND length_str = 8 THEN
X      LET dt_str = dt;
X      LET year = str[7,8];
X      IF year > 50 THEN
X         LET dt_str[1,2] = '19';
X      ELSE
X         LET dt_str[1,2] = '20';
X      END IF;
X      LET dt_str[3,4] = str[7,8];
X      LET dt_str[6,7] = str[1,2];
X      LET dt_str[9,10] = str[4,5];
X      LET dt = dt_str;
X   ELIF ( frmt = 'DD/MM/YY' OR frmt = 'dd/mm/yy' ) AND length_str = 8 THEN
X      LET dt_str = dt;
X      LET year = str[7,8];
X      IF year > 50 THEN
X         LET dt_str[1,2] = '19';
X      ELSE
X         LET dt_str[1,2] = '20';
X      END IF;
X      LET dt_str[3,4] = str[7,8];
X      LET dt_str[6,7] = str[4,5];
X      LET dt_str[9,10] = str[1,2];
X      LET dt = dt_str;
X   ELIF ( frmt = 'YYYYMMDD' OR frmt = 'yyyymmdd' ) AND length_str = 8 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[1,4];
X      LET dt_str[6,7] = str[5,6];
X      LET dt_str[9,10] = str[7,8];
X      LET dt = dt_str;
X   ELIF ( frmt = 'MMDDYYYY' OR frmt = 'mmddyyyy' ) AND length_str = 8 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[5,8];
X      LET dt_str[6,7] = str[1,2];
X      LET dt_str[9,10] = str[3,4];
X      LET dt = dt_str;
X   ELIF ( frmt = 'MM/DD/YYYY' OR frmt = 'mm/dd/yyyy' ) AND length_str = 10 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[7,10];
X      LET dt_str[6,7] = str[1,2];
X      LET dt_str[9,10] = str[4,5];
X      LET dt = dt_str;
X   ELIF ( frmt = 'DD/MM/YYYY' OR frmt = 'dd/mm/yyyy' ) AND length_str = 10 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[7,10];
X      LET dt_str[6,7] = str[4,5];
X      LET dt_str[9,10] = str[1,2];
X      LET dt = dt_str;
X   ELIF ( frmt = 'YYYY/MM/DD' OR frmt = 'yyyy/mm/dd' ) AND length_str = 10 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[1,4];
X      LET dt_str[6,7] = str[6,7];
X      LET dt_str[9,10] = str[9,10];
X      LET dt = dt_str;
X   ELIF ( frmt = 'DD-MON-YYYY' OR frmt = 'dd-mon-yyyy' ) AND length_str =
X11 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[8,11];
X      LET i = 1;
X      FOR month IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep',
X'Oct','Nov','Dec')
X         IF month = str[4,6] THEN
X            IF i < 10 THEN
X               LET dt_str[6,6] = 0;
X               LET dt_str[7,7] = i;
X            ELSE
X               LET dt_str[6,7] = i;
X            END IF;
X            EXIT FOR;
X         END IF;
X         LET i = i + 1;
X      END FOR;
X      IF i > 12 THEN
X         RAISE EXCEPTION -1276;
X      END IF;
X      LET dt_str[9,10] = str[1,2];
X      LET dt = dt_str;
X   ELIF ( frmt = 'MM/DD/YYYY HH24:MI:SS' OR frmt = 'mm/dd/yyyy hh24:mi:ss' )
X           AND length_str = 19 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[7,10];
X      LET dt_str[6,7] = str[1,2];
X      LET dt_str[9,10] = str[4,5];
X      LET dt_str[12,13] = str[12,13];
X      LET dt_str[15,16] = str[15,16];
X      LET dt_str[18,19] = str[18,19];
X      LET dt = dt_str;
X   ELIF ( frmt = 'YYYYMMDDHH24MISS' OR frmt = 'yyyymmddhh24miss' )
X           AND length_str = 14 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[1,4];
X      LET dt_str[6,7] = str[5,6];
X      LET dt_str[9,10] = str[7,8];
X      LET dt_str[12,13] = str[9,10];
X      LET dt_str[15,16] = str[11,12];
X      LET dt_str[18,19] = str[13,14];
X      LET dt = dt_str;
X   ELIF ( frmt = 'YYYYMMDDHH24MI' OR frmt = 'yyyymmddhh24mi' )
X           AND length_str = 12 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[1,4];
X      LET dt_str[6,7] = str[5,6];
X      LET dt_str[9,10] = str[7,8];
X      LET dt_str[12,13] = str[9,10];
X      LET dt_str[15,16] = str[11,12];
X      LET dt = dt_str;
X   ELIF ( frmt = 'DD-MON-YYYY HH24:MI:SS' OR frmt = 'dd-mon-yyyy hh24:mi:ss' )
X           AND length_str = 20 THEN
X      LET dt_str = dt;
X      LET dt_str[1,4] = str[8,11];
X      LET i = 1;
X      FOR month IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep',
X'Oct','Nov','Dec')
X         IF month = str[4,6] THEN
X            IF i < 10 THEN
X               LET dt_str[6,6] = 0;
X               LET dt_str[7,7] = i;
X            ELSE
X               LET dt_str[6,7] = i;
X            END IF;
X            EXIT FOR;
X         END IF;
X         LET i = i + 1;
X      END FOR;
X      IF i > 12 THEN
X         RAISE EXCEPTION -1276;
X      END IF;
X      LET dt_str[9,10] = str[1,2];
X      LET dt_str[12,13] = str[13,14];
X      LET dt_str[15,16] = str[16,17];
X      LET dt_str[18,19] = str[19,20];
X      LET dt = dt_str;
X   ELSE
X      RAISE EXCEPTION -1276;
X   END IF;
X
X   RETURN dt;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- date_to_char()
X--------------------------------------------------------
XDROP PROCEDURE "informix".date_to_char;
XCREATE PROCEDURE "informix".date_to_char(dt DATETIME YEAR TO SECOND, frmt VARCHAR(255))
X        RETURNING VARCHAR(255);
X
XDEFINE dt_str VARCHAR(255);
XDEFINE str VARCHAR(255);
XDEFINE i INT;
XDEFINE month VARCHAR(255);
X
X   IF dt IS NULL THEN
X      RETURN NULL;
X   END IF;
X
X   LET str = NULL;
X
X   IF frmt = 'MM' OR frmt = 'mm' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[6,7];
X   ELIF frmt = 'YYYY' OR frmt = 'yyyy' THEN
X      LET dt_str = dt;
X      LET str[1,4] = dt_str[1,4];
X   ELIF frmt = 'HH24:MI:SS' OR frmt = 'hh24:mi:ss' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[12,13];
X      LET str[3,3] = ':';
X      LET str[4,5] = dt_str[15,16];
X      LET str[6,6] = ':';
X      LET str[7,8] = dt_str[18,19];
X   ELIF frmt = 'YYMMDD' OR frmt = 'yymmdd' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[3,4];
X      LET str[3,4] = dt_str[6,7];
X      LET str[5,6] = dt_str[9,10];
X   ELIF frmt = 'DDMMYY' OR frmt = 'ddmmyy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[9,10];
X      LET str[3,4] = dt_str[6,7];
X      LET str[5,6] = dt_str[3,4];
X   ELIF frmt = 'MM/DD/YY' OR frmt = 'mm/dd/yy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[6,7];
X      LET str[3,3] = '/';
X      LET str[4,5] = dt_str[9,10];
X      LET str[6,6] = '/';
X      LET str[7,8] = dt_str[3,4];
X   ELIF frmt = 'DD/MM/YY' OR frmt = 'dd/mm/yy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[9,10];
X      LET str[3,3] = '/';
X      LET str[4,5] = dt_str[6,7];
X      LET str[6,6] = '/';
X      LET str[7,8] = dt_str[3,4];
X   ELIF frmt = 'YYYYMMDD' OR frmt = 'yyyymmdd' THEN
X      LET dt_str = dt;
X      LET str[1,4] = dt_str[1,4];
X      LET str[5,6] = dt_str[6,7];
X      LET str[7,8] = dt_str[9,10];
X   ELIF frmt = 'MMDDYYYY' OR frmt = 'mmddyyyy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[6,7];
X      LET str[3,4] = dt_str[9,10];
X      LET str[5,8] = dt_str[1,4];
X   ELIF frmt = 'MM/DD/YYYY' OR frmt = 'mm/dd/yyyy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[6,7];
X      LET str[3,3] = '/';
X      LET str[4,5] = dt_str[9,10];
X      LET str[6,6] = '/';
X      LET str[7,10] = dt_str[1,4];
X   ELIF frmt = 'YYYY/MM/DD' OR frmt = 'yyyy/mm/dd' THEN
X      LET dt_str = dt;
X      LET str[1,4] = dt_str[1,4];
X      LET str[5,5] = '/';
X      LET str[6,7] = dt_str[6,7];
X      LET str[8,8] = '/';
X      LET str[9,10] = dt_str[9,10];
X   ELIF frmt = 'DD/MM/YYYY' OR frmt = 'dd/mm/yyyy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[9,10];
X      LET str[3,3] = '/';
X      LET str[4,5] = dt_str[6,7];
X      LET str[6,6] = '/';
X      LET str[7,10] = dt_str[1,4];
X   ELIF frmt = 'DD-MON-YYYY' OR frmt = 'dd-mon-yyyy' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[9,10];
X      LET str[3,3] = '-';
X      LET i = 1;
X      FOR month IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep',
X'Oct','Nov','Dec')
X         IF i = dt_str[6,7] THEN
X            LET str[4,6] = month;
X            EXIT FOR;
X         END IF;
X         LET i = i + 1;
X      END FOR;
X      LET str[7,7] = '-';
X      LET str[8,11] = dt_str[1,4];
X   ELIF frmt = 'MM/DD/YYYY HH24:MI:SS' OR frmt = 'mm/dd/yyyy hh24:mi:ss' THEN
X      LET dt_str = dt;
X      LET str[7,10] = dt_str[1,4];
X      LET str[3,3]='/';
X      LET str[1,2] = dt_str[6,7];
X      LET str[6,6]='/';
X      LET str[4,5] = dt_str[9,10];
X      LET str[12,13] = dt_str[12,13];
X      LET str[14,14]=':';
X      LET str[15,16] = dt_str[15,16];
X      LET str[17,17]=':';
X      LET str[18,19] = dt_str[18,19];
X   ELIF frmt = 'YYYYMMDDHH24MISS' OR frmt = 'yyyymmddhh24miss' THEN
X      LET dt_str = dt;
X      LET str[1,4] = dt_str[1,4];
X      LET str[5,6] = dt_str[6,7];
X      LET str[7,8] = dt_str[9,10];
X      LET str[9,10] = dt_str[12,13];
X      LET str[11,12] = dt_str[15,16];
X      LET str[13,14] = dt_str[18,19];
X   ELIF frmt = 'YYYYMMDDHH24MI' OR frmt = 'yyyymmddhh24mi' THEN
X      LET dt_str = dt;
X      LET str[1,4] = dt_str[1,4];
X      LET str[5,6] = dt_str[6,7];
X      LET str[7,8] = dt_str[9,10];
X      LET str[9,10] = dt_str[12,13];
X      LET str[11,12] = dt_str[15,16];
X   ELIF frmt = 'DD-MON-YYYY HH24:MI:SS' OR frmt = 'dd-mon-yyyy hh24:mi:ss' THEN
X      LET dt_str = dt;
X      LET str[1,2] = dt_str[9,10];
X      LET str[3,3] = '-';
X      LET i = 1;
X      FOR month IN ('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep',
X'Oct','Nov','Dec')
X         IF i = dt_str[6,7] THEN
X            LET str[4,6] = month;
X            EXIT FOR;
X         END IF;
X         LET i = i + 1;
X      END FOR;
X      LET str[7,7] = '-';
X      LET str[8,11] = dt_str[1,4];
X      LET str[12,12] = ' ';
X      LET str[13,14] = dt_str[12,13];
X      LET str[15,15] = ':';
X      LET str[16,17] = dt_str[15,16];
X      LET str[18,18] = ':';
X      LET str[19,20] = dt_str[18,19];
X   ELSE
X      RAISE EXCEPTION -1276;
X   END IF;
X
X   RETURN str;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- decode()
X--------------------------------------------------------
XDROP PROCEDURE "informix".decode;
XCREATE PROCEDURE "informix".decode(basis VARCHAR(255),
X                        case1 VARCHAR(255),
X                        retn1 VARCHAR(255),
X                        case2 VARCHAR(255) default null,
X                        retn2 VARCHAR(255) default null,
X                        case3 VARCHAR(255) default null,
X                        retn3 VARCHAR(255) default null,
X                        case4 VARCHAR(255) default null,
X                        retn4 VARCHAR(255) default null,
X                        case5 VARCHAR(255) default null,
X                        retn5 VARCHAR(255) default null,
X                        case6 VARCHAR(255) default null,
X                        retn6 VARCHAR(255) default null,
X                        case7 VARCHAR(255) default null,
X                        retn7 VARCHAR(255) default null,
X                        case8 VARCHAR(255) default null,
X                        retn8 VARCHAR(255) default null,
X                        case9 VARCHAR(255) default null,
X                        retn9 VARCHAR(255) default null,
X                        deflt VARCHAR(255) default null)
X      RETURNING VARCHAR(255);
X
X   IF basis = case1 THEN
X      RETURN retn1;
X   END IF;
X
X   IF case2 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn2 IS NULL THEN { case2 is default value.}
X         RETURN case2;
X      ELSE { matching pattern found. }
X         IF basis = case2 THEN
X            RETURN retn2;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case3 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn3 IS NULL THEN { case3 is default value.}
X         RETURN case3;
X      ELSE { matching pattern found. }
X         IF basis = case3 THEN
X            RETURN retn3;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case4 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn4 IS NULL THEN { case4 is default value.}
X         RETURN case4;
X      ELSE { matching pattern found. }
X         IF basis = case4 THEN
X            RETURN retn4;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case5 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn5 IS NULL THEN { case5 is default value.}
X         RETURN case5;
X      ELSE { matching pattern found. }
X         IF basis = case5 THEN
X            RETURN retn5;
X         END IF;
X      END IF;
X   END IF;
X
X   If case6 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn6 IS NULL THEN { case6 is default value.}
X         RETURN case6;
X      ELSE { matching pattern found. }
X         IF basis = case6 THEN
X            RETURN retn6;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case7 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn7 IS NULL THEN { case7 is default value.}
X         RETURN case7;
X      ELSE { matching pattern found. }
X         IF basis = case7 THEN
X            RETURN retn7;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case8 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn8 IS NULL THEN { case8 is default value.}
X         RETURN case8;
X      ELSE { matching pattern found. }
X         IF basis = case8 THEN
X            RETURN retn8;
X         END IF;
X      END IF;
X   END IF;
X
X   IF case9 IS NULL THEN { no default value available. }
X      RETURN null;
X   ELSE
X      IF retn9 IS NULL THEN { case9 is default value.}
X         RETURN case9;
X      ELSE { matching pattern found. }
X         IF basis = case9 THEN
X            RETURN retn9;
X         END IF;
X      END IF;
X   END IF;
X
X   IF deflt IS NULL then
X      RETURN null;
X   END IF;
X
X   RETURN deflt;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- decode_sint_4()
X--------------------------------------------------------
XDROP PROCEDURE "informix".decode_sint_4;
XCREATE PROCEDURE "informix".decode_sint_4(
X                                param SMALLINT,
X                                val SMALLINT ,
X                                ret SMALLINT ,
X                                default_ret SMALLINT)
X  RETURNING SMALLINT;
X
X  IF param = val OR ( param IS NULL AND val IS NULL ) THEN
X    return ret;
X  ELSE
X    return default_ret;
X  END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- decode_dt_4()
X--------------------------------------------------------
XDROP PROCEDURE "informix".decode_dt_4;
XCREATE PROCEDURE "informix".decode_dt_4(
X                                param DATETIME YEAR TO SECOND,
X                                val DATETIME YEAR TO SECOND ,
X                                ret DATETIME YEAR TO SECOND ,
X                                default_ret DATETIME YEAR TO SECOND)
X  RETURNING DATETIME YEAR TO SECOND;
X
X  IF param = val OR ( param IS NULL AND val IS NULL ) THEN
X    return ret;
X  ELSE
X    return default_ret;
X  END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- lower()
X--------------------------------------------------------
XDROP PROCEDURE "informix".lower;
XCREATE PROCEDURE "informix".lower(str VARCHAR(255))
X        RETURNING VARCHAR(255);
X
XDEFINE i INTEGER;
XDEFINE length_str INTEGER;
XDEFINE retstr VARCHAR(255);
X
X   IF str IS NULL THEN
X      RETURN NULL;
X   ELSE
X      SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
X      LET retstr = '';
X      FOR i = 1 TO length_str
X         IF str[1,1] BETWEEN 'A' AND 'Z' THEN
X            IF str[1,1] = 'A' THEN
X               LET retstr = retstr || 'a';
X            ELIF str[1,1] = 'B' THEN
X               LET retstr = retstr || 'b';
X            ELIF str[1,1] = 'C' THEN
X               LET retstr = retstr || 'c';
X            ELIF str[1,1] = 'D' THEN
X               LET retstr = retstr || 'd';
X            ELIF str[1,1] = 'E' THEN
X               LET retstr = retstr || 'e';
X            ELIF str[1,1] = 'F' THEN
X               LET retstr = retstr || 'f';
X            ELIF str[1,1] = 'G' THEN
X               LET retstr = retstr || 'g';
X            ELIF str[1,1] = 'H' THEN
X               LET retstr = retstr || 'h';
X            ELIF str[1,1] = 'I' THEN
X               LET retstr = retstr || 'i';
X            ELIF str[1,1] = 'J' THEN
X               LET retstr = retstr || 'j';
X            ELIF str[1,1] = 'K' THEN
X               LET retstr = retstr || 'k';
X            ELIF str[1,1] = 'L' THEN
X               LET retstr = retstr || 'l';
X            ELIF str[1,1] = 'M' THEN
X               LET retstr = retstr || 'm';
X            ELIF str[1,1] = 'N' THEN
X               LET retstr = retstr || 'n';
X            ELIF str[1,1] = 'O' THEN
X               LET retstr = retstr || 'o';
X            ELIF str[1,1] = 'P' THEN
X               LET retstr = retstr || 'p';
X            ELIF str[1,1] = 'Q' THEN
X               LET retstr = retstr || 'q';
X            ELIF str[1,1] = 'R' THEN
X               LET retstr = retstr || 'r';
X            ELIF str[1,1] = 'S' THEN
X               LET retstr = retstr || 's';
X            ELIF str[1,1] = 'T' THEN
X               LET retstr = retstr || 't';
X            ELIF str[1,1] = 'U' THEN
X               LET retstr = retstr || 'u';
X            ELIF str[1,1] = 'V' THEN
X               LET retstr = retstr || 'v';
X            ELIF str[1,1] = 'W' THEN
X               LET retstr = retstr || 'w';
X            ELIF str[1,1] = 'X' THEN
X               LET retstr = retstr || 'x';
X            ELIF str[1,1] = 'Y' THEN
X               LET retstr = retstr || 'y';
X            ELSE
X               LET retstr = retstr || 'z';
X            END IF;
X         ELSE
X            LET retstr = retstr || str[1,1];
X         END IF;
X
X         LET str = str[2,255];
X
X      END FOR;
X
X--      LET retstr = retstr[2,255];
X
X      RETURN retstr;
X
X   END IF;
X
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- lpad()
X--------------------------------------------------------
XDROP PROCEDURE "informix".lpad;
XCREATE PROCEDURE "informix".lpad(str VARCHAR(255),
X                          maxlen integer,
X                          padchar varchar(255) DEFAULT'')
X        RETURNING VARCHAR(255);
X
X        DEFINE i      INTEGER;
X        DEFINE l      INTEGER;
X        DEFINE retstr VARCHAR(255);
X        DEFINE retstr2 VARCHAR(255);
X        DEFINE plen   INTEGER;
X        DEFINE len    INTEGER;
X
X
X    LET len=1;
X    LET plen=LENGTH(padchar);
X
X    IF  plen=0 THEN
X         LET plen=1;
X    END IF;
X
X    IF str IS NULL THEN
X         RETURN NULL;
X    ELSE
X         LET l=LENGTH(str);
X         LET retstr=padchar;
X
X         IF   l=MAXLEN THEN
X                LET retstr=str;
X
X         ELIF l>MAXLEN THEN
X             LET retstr=str[1,1];
X                FOR i=2 TO MAXLEN
X                    LET str=str[2,255];
X                    LET retstr=retstr||str[1,1];
X                END FOR;
X         ELSE
X              WHILE len < MAXLEN-l
X                 LET len=len+plen;
X                 LET retstr=retstr||padchar;
X              END WHILE;
X              IF len >= MAXLEN-l THEN
X
X                  LET retstr2=retstr[1,1];
X                  FOR i=2 TO MAXLEN-l
X                      LET retstr=retstr[2,255];
X                      LET retstr2=retstr2||retstr[1,1];
X                  END FOR;
X                  LET retstr=retstr2;
X              END IF;
X
X              LET retstr=retstr||str;
X          END IF;
X       RETURN retstr;
X    END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- next_day()
X--------------------------------------------------------
XDROP PROCEDURE "informix".next_day;
XCREATE PROCEDURE "informix".next_day(dt DATETIME YEAR TO SECOND,
X        day VARCHAR(255))
X        RETURNING DATETIME YEAR TO SECOND;
X
XDEFINE d DATE;
XDEFINE ret DATETIME YEAR TO SECOND;
XDEFINE dt_day_id INTEGER;
XDEFINE day_id INTEGER;
XDEFINE tmp_day VARCHAR(255);
XDEFINE i INTEGER;
XDEFINE diff_days INTEGER;
X
X   IF dt IS NULL AND day IS NOT NULL THEN
X       RETURN NULL;
X   END IF;
X
X   LET d = dt;
X   LET dt_day_id = weekday(d);  -- SUNDAY=0, SATURDAY=6.
X   LET i = 0;
X   FOR tmp_day IN ('SUNDAY','MONDAY','TUESDAY','WEDNESDAY','THURSDAY',
X'FRIDAY','SATURDAY')
X      IF tmp_day = day THEN
X         LET day_id = i;
X         EXIT FOR;
X      END IF;
X      LET i = i + 1;
X   END FOR;
X   IF day_id > 6 THEN
X      RAISE EXCEPTION -1301;
X   END IF;
X
X   IF day_id < dt_day_id THEN
X      LET diff_days = 7 - (dt_day_id - day_id);
X      IF diff_days = 1 THEN
X         LET ret = dt + INTERVAL(1) DAY TO DAY;
X      ELIF diff_days = 2 THEN
X         LET ret = dt + INTERVAL(2) DAY TO DAY;
X      ELIF diff_days = 3 THEN
X         LET ret = dt + INTERVAL(3) DAY TO DAY;
X      ELIF diff_days = 4 THEN
X         LET ret = dt + INTERVAL(4) DAY TO DAY;
X      ELIF diff_days = 5 THEN
X         LET ret = dt + INTERVAL(5) DAY TO DAY;
X      ELIF diff_days = 6 THEN
X         LET ret = dt + INTERVAL(6) DAY TO DAY;
X      END IF;
X   ELIF day_id > dt_day_id THEN
X      LET diff_days = day_id - dt_day_id;
X      IF diff_days = 1 THEN
X         LET ret = dt + INTERVAL(1) DAY TO DAY;
X      ELIF diff_days = 2 THEN
X         LET ret = dt + INTERVAL(2) DAY TO DAY;
X      ELIF diff_days = 3 THEN
X         LET ret = dt + INTERVAL(3) DAY TO DAY;
X      ELIF diff_days = 4 THEN
X         LET ret = dt + INTERVAL(4) DAY TO DAY;
X      ELIF diff_days = 5 THEN
X         LET ret = dt + INTERVAL(5) DAY TO DAY;
X      ELIF diff_days = 6 THEN
X         LET ret = dt + INTERVAL(6) DAY TO DAY;
X      END IF;
X   ELSE
X      LET ret = dt + INTERVAL(7) DAY TO DAY;
X   END IF;
X   RETURN ret;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- nvl()
X--------------------------------------------------------
XDROP PROCEDURE "informix".nvl;
XCREATE PROCEDURE "informix".nvl(str1 VARCHAR(255), str2 VARCHAR(255) )
X  RETURNING VARCHAR(255);
X
X  IF str1 IS NULL THEN
X    return str2;
X  ELSE
X    return str1;
X  END IF;
X
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- nvln()
X--------------------------------------------------------
XDROP PROCEDURE "informix".nvln;
XCREATE PROCEDURE "informix".nvln(num1 VARCHAR(255), num2 VARCHAR(255) )
X  RETURNING DECIMAL(32,8);
X
X  IF num1 IS NULL THEN
X    return num2;
X  ELSE
X    return num1;
X  END IF;
X
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- nvl_dt()
X--------------------------------------------------------
XDROP PROCEDURE "informix".nvl_dt;
XCREATE PROCEDURE "informix".nvl_dt(str1 datetime year to second,
X  str2 datetime  year to second)
X  RETURNING datetime year to second;
X
X  IF str1 IS NULL THEN
X    return str2;
X  ELSE
X    return str1;
X  END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- nvl_sint()
X--------------------------------------------------------
XDROP PROCEDURE "informix".nvl_sint;
XCREATE PROCEDURE "informix".nvl_sint(str1 SMALLINT, str2 SMALLINT)
X  RETURNING SMALLINT;
X
X  IF str1 IS NULL THEN
X    return str2;
X  ELSE
X    return str1;
X  END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- nvl_dec()
X--------------------------------------------------------
XDROP PROCEDURE "informix".nvl_dec;
XCREATE PROCEDURE "informix".nvl_dec(str1 decimal (24,6) , str2 decimal (24,6) )
X  RETURNING decimal (24,6) ;
X
X  IF str1 IS NULL THEN
X    return str2;
X  ELSE
X    return str1;
X  END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- replace()
X--------------------------------------------------------
XDROP PROCEDURE "informix".replace;
XCREATE PROCEDURE "informix".replace(str VARCHAR(255),
X                             search_str VARCHAR(255),
X                             replace_str VARCHAR(255) DEFAULT NULL)
X        RETURNING VARCHAR(255);
X
X   DEFINE i INTEGER;
X   DEFINE j INTEGER;
X   DEFINE sw INTEGER;
X   DEFINE sw1 INTEGER;
X   DEFINE a VARCHAR(255);
X   DEFINE b VARCHAR(255);
X   DEFINE str1 VARCHAR(255);
X   DEFINE retstr VARCHAR(255);
X   DEFINE search_str1 VARCHAR(255);
X   DEFINE l INTEGER;
X   DEFINE l1 INTEGER;
X   DEFINE m INTEGER;
X
X   IF str IS NULL THEN
X      RETURN NULL;
X   ELSE
X     LET search_str1=search_str;
X     LET l=LENGTH(str);
X     LET l1=LENGTH(search_str);
X     IF l1>1 THEN
X        LET str1=str;
X        LET m=1;
X        FOR i=1 TO l
X         LET search_str=search_str1;
X         IF str[1,1]=search_str[1,1] THEN
X            LET a=str[1,1];
X            LET b=str[1,1];
X            LET j=1;
X            LET sw=0;
X            WHILE sw<>1
X               LET str1=str1[2,255];
X               LET search_str=search_str[2,255];
X               IF str1[1,1]=search_str[1,1] THEN
X                   LET sw=0;
X                   LET b=b||str1[1,1];
X                   LET j=j+1;
X               ELSE
X                   LET sw=1;
X               END IF;
X               IF j=l1 THEN
X                LET sw=1;
X               END IF;
X
X            END WHILE;
X
X            IF b<>search_str1 THEN
X                IF m=1 THEN
X                   LET retstr=a;
X                ELSE
X                   LET retstr=retstr||a;
X                END IF;
X                LET m=m+1;
X            ELSE
X                IF replace_str IS NOT NULL THEN
X                   IF m=1 THEN
X                      LET retstr=replace_str;
X                   ELSE
X                    LET retstr=retstr||replace_str;
X                   END IF;
X                   LET m=m+1;
X                END IF;
X            END IF;
X
X         ELSE
X             IF m=1 THEN
X               LET retstr=str[1,1];
X             ELSE
X               LET retstr=retstr||str[1,1];
X             END IF;
X             LET m=m+1;
X         END IF;
X
X         LET str=str1[2,255];
X         LET str1=str1[2,255];
X       END FOR;
X     ELSE
X      LET m=1;
X      IF str[1,1]=search_str THEN
X        IF replace_str IS NOT NULL THEN
X         LET retstr=replace_str;
X         LET m=m+1;
X        END IF;
X      ELSE
X        LET retstr=str[1,1];
X        LET m=m+1;
X      END IF;
X
X      FOR i=2 TO l
X         LET str=str[2,255];
X         IF str[1,1]=search_str THEN
X            IF replace_str IS NOT NULL THEN
X              LET retstr=retstr||replace_str;
X            END IF;
X         ELSE
X            IF m=1 THEN
X             LET retstr=str[1,1];
X            ELSE
X             LET retstr=retstr||str[1,1];
X            END IF;
X            LET m=m+1;
X         END IF;
X      END FOR;
X      END IF;
X       RETURN retstr;
X     END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- rpad()
X--------------------------------------------------------
XDROP PROCEDURE "informix".rpad;
XCREATE PROCEDURE "informix".rpad(str VARCHAR(255),
X                          maxlen integer,
X                          padchar varchar(255) DEFAULT NULL)
X        RETURNING VARCHAR(255);
X
X        DEFINE i      INTEGER;
X        DEFINE l      INTEGER;
X        DEFINE retstr VARCHAR(255);
X        DEFINE retstr1 VARCHAR(255);
X        DEFINE retstr2 VARCHAR(255);
X        DEFINE plen   INTEGER;
X        DEFINE len    INTEGER;
X
X
X    LET len=1;
X    LET plen=LENGTH(padchar);
X
X    IF  plen=0 THEN
X         LET plen=1;
X    END IF;
X
X    IF str IS NULL THEN
X         RETURN NULL;
X    ELSE
X         LET l=LENGTH(str);
X         LET retstr=padchar;
X
X         IF   l=MAXLEN THEN
X                LET retstr=str;
X
X         ELIF l>MAXLEN THEN
X             LET retstr=str[1,1];
X                FOR i=2 TO MAXLEN
X                    LET str=str[2,255];
X                    LET retstr=retstr||str[1,1];
X                END FOR;
X         ELSE
X              WHILE len < MAXLEN-l
X                 LET len=len+plen;
X                 LET retstr=retstr||padchar;
X              END WHILE;
X              IF len >= MAXLEN-l THEN
X
X                 LET retstr2=retstr[1,1];
X                 FOR i=2 TO MAXLEN-l
X                      LET retstr=retstr[2,255];
X                       LET retstr2=retstr2||retstr[1,1];
X                 END FOR;
X                 LET retstr=retstr2;
X              END IF;
X              LET retstr1=str[1,1];
X              FOR i=2 TO l
X                LET str=str[2,255];
X                LET retstr1=retstr1||str[1,1];
X              END FOR;
X
X             LET retstr=retstr1||retstr;
X          END IF;
X     RETURN retstr;
X     END IF;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- sign()
X--------------------------------------------------------
XDROP PROCEDURE "informix".sign;
XCREATE PROCEDURE "informix".sign(n DECIMAL(32,8))
X  RETURNING INTEGER;
X
X  IF n > 0 THEN
X    RETURN 1;
X  ELIF n < 0 THEN
X    RETURN -1;
X  ELSE
X    RETURN 0;
X  END IF;
X
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- substr()
X--------------------------------------------------------
XDROP PROCEDURE "informix".substr;
XCREATE PROCEDURE "informix".substr(str VARCHAR(255), pos INTEGER,
X        len INTEGER DEFAULT 0)
X        RETURNING VARCHAR(255);
X
XDEFINE i INTEGER;
XDEFINE length_str INTEGER;
XDEFINE abs_pos INTEGER;
XDEFINE retstr VARCHAR(255);
X
X   IF str IS NULL OR pos IS NULL OR len IS NULL THEN
X      RETURN NULL;
X   END IF;
X
X   LET retstr = '';
X
X   -- len can't be less then 0.
X   IF len < 0 THEN
X      RETURN retstr;
X   END IF;
X
X   -- pos can't be equal to 0.
X   IF pos = 0 THEN
X      RETURN retstr;
X   END IF;
X
X   -- if len equal 0 ,len gets new value.
X   IF len = 0 AND pos > 0 THEN
X      SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
X      LET len = length_str - pos + 1;
X   END IF;
X
X   IF len = 0 AND pos < 0 THEN
X      SELECT abs(pos) INTO abs_pos FROM systables WHERE tabid=1;
X      LET len = abs_pos;
X   END IF;
X
X   -- if pos is possitive the counting is done from the begining of str to the
X   -- right.
X   IF pos > 0 THEN
X      IF pos > 1 THEN
X         FOR i = 2 TO pos
X            LET str = str[2,255];
X         END FOR;
X      END IF;
X      FOR i = 1 TO len
X         LET retstr = retstr || str[1,1];
X         LET str = str[2,255];
X      END FOR;
X--      LET retstr = retstr[2,255];
X
X   -- if pos is negative the counting is done from the end of str to the left.
X   ELIF pos < 0 THEN
X      SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
X      LET pos = length_str + pos + 1;
X      IF pos > 1 THEN
X         FOR i = 2 TO pos
X            LET str = str[2,255];
X         END FOR;
X      END IF;
X      FOR i = 1 TO len
X         LET retstr = retstr || str[1,1];
X         LET str = str[2,255];
X      END FOR;
X--      LET retstr = retstr[2,255];
X    END IF;
X
X    RETURN retstr;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- to_number()
X--------------------------------------------------------
XDROP PROCEDURE "informix".to_number;
XCREATE PROCEDURE "informix".to_number(str VARCHAR(255)) RETURNING DECIMAL(32,8);
X    return str;
XEND PROCEDURE;
X
X--------------------------------------------------------
X-- upper()
X--------------------------------------------------------
XDROP PROCEDURE "informix".upper;
XCREATE PROCEDURE "informix".upper(str VARCHAR(255))
X        RETURNING VARCHAR(255);
X
XDEFINE i INTEGER;
XDEFINE length_str INTEGER;
XDEFINE retstr VARCHAR(255);
X
X   IF str IS NULL THEN
X      RETURN NULL;
X   ELSE
X      SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
X      LET retstr = '';
X      FOR i = 1 TO length_str
X         IF str[1,1] BETWEEN 'a' AND 'z' THEN
X            IF str[1,1] = 'a' THEN
X               LET retstr = retstr || 'A';
X            ELIF str[1,1] = 'b' THEN
X               LET retstr = retstr || 'B';
X            ELIF str[1,1] = 'c' THEN
X               LET retstr = retstr || 'C';
X            ELIF str[1,1] = 'd' THEN
X               LET retstr = retstr || 'D';
X            ELIF str[1,1] = 'e' THEN
X               LET retstr = retstr || 'E';
X            ELIF str[1,1] = 'f' THEN
X               LET retstr = retstr || 'F';
X            ELIF str[1,1] = 'g' THEN
X               LET retstr = retstr || 'G';
X            ELIF str[1,1] = 'h' THEN
X               LET retstr = retstr || 'H';
X            ELIF str[1,1] = 'i' THEN
X               LET retstr = retstr || 'I';
X            ELIF str[1,1] = 'j' THEN
X               LET retstr = retstr || 'J';
X            ELIF str[1,1] = 'k' THEN
X               LET retstr = retstr || 'K';
X            ELIF str[1,1] = 'l' THEN
X               LET retstr = retstr || 'L';
X            ELIF str[1,1] = 'm' THEN
X               LET retstr = retstr || 'M';
X            ELIF str[1,1] = 'n' THEN
X               LET retstr = retstr || 'N';
X            ELIF str[1,1] = 'o' THEN
X               LET retstr = retstr || 'O';
X            ELIF str[1,1] = 'p' THEN
X               LET retstr = retstr || 'P';
X            ELIF str[1,1] = 'q' THEN
X               LET retstr = retstr || 'Q';
X            ELIF str[1,1] = 'r' THEN
X               LET retstr = retstr || 'R';
X            ELIF str[1,1] = 's' THEN
X               LET retstr = retstr || 'S';
X            ELIF str[1,1] = 't' THEN
X               LET retstr = retstr || 'T';
X            ELIF str[1,1] = 'u' THEN
X               LET retstr = retstr || 'U';
X            ELIF str[1,1] = 'v' THEN
X               LET retstr = retstr || 'V';
X            ELIF str[1,1] = 'w' THEN
X               LET retstr = retstr || 'W';
X            ELIF str[1,1] = 'x' THEN
X               LET retstr = retstr || 'X';
X            ELIF str[1,1] = 'y' THEN
X               LET retstr = retstr || 'Y';
X            ELSE
X               LET retstr = retstr || 'Z';
X            END IF;
X         ELSE
X            LET retstr = retstr || str[1,1];
X         END IF;
X
X         LET str = str[2,255];
X
X      END FOR;
X
X--      LET retstr = retstr[2,255];
X
X      RETURN retstr;
X   END IF;
XEND PROCEDURE;
X
Xgrant  execute on "informix".ceil to "public" as "informix";
Xgrant  execute on "informix".char_to_date to "public" as "informix";
Xgrant  execute on "informix".date_to_char to "public" as "informix";
Xgrant  execute on "informix".decode to "public" as "informix";
Xgrant  execute on "informix".decode_sint_4 to "public" as "informix";
Xgrant  execute on "informix".decode_dt_4 to "public" as "informix";
Xgrant  execute on "informix".lower to "public" as "informix";
Xgrant  execute on "informix".lpad to "public" as "informix";
Xgrant  execute on "informix".next_day to "public" as "informix";
Xgrant  execute on "informix".nvl to "public" as "informix";
Xgrant  execute on "informix".nvln to "public" as "informix";
Xgrant  execute on "informix".nvl_dt to "public" as "informix";
Xgrant  execute on "informix".nvl_sint to "public" as "informix";
Xgrant  execute on "informix".nvl_dec to "public" as "informix";
Xgrant  execute on "informix".replace to "public" as "informix";
Xgrant  execute on "informix".rpad to "public" as "informix";
Xgrant  execute on "informix".sign to "public" as "informix";
Xgrant  execute on "informix".substr to "public" as "informix";
Xgrant  execute on "informix".to_number to "public" as "informix";
Xgrant  execute on "informix".upper to "public" as "informix";
X
XDROP VIEW "informix".dual;
XCREATE VIEW "informix".dual (tabname) AS
X  SELECT x0.tabname FROM "informix".systables x0 WHERE (x0.tabid = 1 );
X
XDROP VIEW "informix".sequence;
XCREATE VIEW "informix".sequence (tabname,currval) AS
X  SELECT x1.tabname ,x0.ti_serialv FROM sysmaster:"informix".systabinfo
X    x0 ,sysmaster:"informix".systabnames x1 WHERE ((x0.ti_partnum = x1.partnum
X    ) AND (x1.dbsname = '>> YOUR DATABASE NAME GOES HERE <<' ) ) ;
SHAR_EOF
if [ `wc -c < proclib.sql` -ne    37249 ]
then
    echo "Lengths do not match -- Bad Copy of proclib.sql"
fi
echo "Done."
exit 0