Re: SQL/INFORMIX

T.Merlin wrote: > > My request is : > SELECT ROWID FROM EMP WHERE UPPER(FIRST_NAME)=UPPER('Tyler')> UPPER is a ORACLE/SQL+ function. > INFORMIX error is : > 674: Procedure (upper) not found. > 111: ISAM error: no record found.> Can you tell if UPPER or same function exist on INFORMIX. You need to create the upper function like so: -------------- CREATE PROCEDURE upper(str CHAR(255)) RETURNING CHAR(255); DEFINE loop INTEGER; DEFINE len INTEGER; DEFINE retstr VARCHAR(255); LET len = LENGTH(str); LET retstr = ''; FOR loop = 1 to len IF str[1,1] BETWEEN "a" AND "z" THEN IF str[1,1] = 'a' THEN LET retstr = retstr || 'A'; ELIF str[1,1] = 'b' THEN LET retstr = retstr || 'B'; ELIF str[1,1] = 'c' THEN LET retstr = retstr || 'C'; ELIF str[1,1] = 'd' THEN LET retstr = retstr || 'D'; ELIF str[1,1] = 'e' THEN LET retstr = retstr || 'E'; ELIF str[1,1] = 'f' THEN LET retstr = retstr || 'F'; ELIF str[1,1] = 'g' THEN LET retstr = retstr || 'G'; ELIF str[1,1] = 'h' THEN LET retstr = retstr || 'H'; ELIF str[1,1] = 'i' THEN LET retstr = retstr || 'I'; ELIF str[1,1] = 'j' THEN LET retstr = retstr || 'J'; ELIF str[1,1] = 'k' THEN LET retstr = retstr || 'K'; ELIF str[1,1] = 'l' THEN LET retstr = retstr || 'L'; ELIF str[1,1] = 'm' THEN LET retstr = retstr || 'M'; ELIF str[1,1] = 'n' THEN LET retstr = retstr || 'N'; ELIF str[1,1] = 'o' THEN LET retstr = retstr || 'O'; ELIF str[1,1] = 'p' THEN LET retstr = retstr || 'P'; ELIF str[1,1] = 'q' THEN LET retstr = retstr || 'Q'; ELIF str[1,1] = 'r' THEN LET retstr = retstr || 'R'; ELIF str[1,1] = 's' THEN LET retstr = retstr || 'S'; ELIF str[1,1] = 't' THEN LET retstr = retstr || 'T'; ELIF str[1,1] = 'u' THEN LET retstr = retstr || 'U'; ELIF str[1,1] = 'v' THEN LET retstr = retstr || 'V'; ELIF str[1,1] = 'w' THEN LET retstr = retstr || 'W'; ELIF str[1,1] = 'x' THEN LET retstr = retstr || 'X'; ELIF str[1,1] = 'y' THEN LET retstr = retstr || 'Y'; ELIF str[1,1] = 'z' THEN LET retstr = retstr || 'Z'; END IF ELSE LET retstr = retstr || str[1,1]; END IF LET str = str[2,255]; END FOR RETURN retstr; END PROCEDURE; -------------- This was submitted by someone on this group a while ago, but I can't remember who it was. Then you need to say: SELECT ROWID FROM EMP WHERE TRIM(UPPER(FIRST_NAME)) = 'TYLER' The TRIM is required to get rid of those extra SPACES at the end. If anyone has a better 'upper' function then please let me know. Hope that helps, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+