informix SPL while loop 1

[fanik]

I'm working with informix database and i need to create some procedure/function which returns the first or the second or etc. word from the string char(100). I need some instruction or tutorial or written procedure. I have written some procedure but it doesn't work. Here is the code: ( telnet: A syntax error has occurred on the line: let pom2=pom1[i,i]


create procedure word(naziv char(100), br integer) returning char(50);

define str1 char(200);
define j smallint;
define len,i integer;
define pom char(100);
define pom1 char(100);
define pom2 char(100);
define pom3 char(100);

--set debug file to 'err.sql';
--trace on;

let len = length(trim(naziv));
let pom=trim(naziv);
let pom1=trim(naziv);
let pom3=' ';
let pom2=pom1[1,1];
let j=1;
let i=0;
let k=br;
if k=0 then return pom3;
while j<len
let i=i+1;
let pom2=pom1[i,i];
if pom2=' ' then
if length(pom1[(i+1),len])=length
(trim(pom1[(i+1),len])) then let k=k-1;
end if;
end if;
if k=0 then let j=len;
let pom=pom1[1,(i-1)];
let pom1=pom1[(i+1),len]
else j=j+1;
end if;

end while;
return pom;
--trace off;
end procedure;

 

[olded]

The Informix stored procedure language is brain dead. It will not let you use a variable in a string's subscript.

This is illegal:
pom1[(i+1),len]

This is not:
pom1[5,10]

You can only access parts of a string using numbers within the string brackets.

The example SP below a very convoluted upper SP that I downloaded from the iiug.org. You can see a ton of str[1,1]:

# UNTESTED
CREATE PROCEDURE "informix".upper(str VARCHAR(255))
        RETURNING VARCHAR(255);

DEFINE i INTEGER;
XDEFINE retstr VARCHAR(255);

   IF str IS NULL THEN
      RETURN NULL;
   ELSE
      SELECT length(str) INTO length_str FROM systables WHERE tabid=1;
      LET retstr = '';
      FOR i = 1 TO length_str
         if  str[1,1] < 'a' or  str[1,1] > 'z'  then 
            let retstr = retstr || str[1,1];
         elif  str[1,1] < 'n'  then
            if  str[1,1] < 'h'  then
               if  str[1,1] < 'e'  then
                  if  str[1,1] < 'c'  then
                     if  str[1,1] = 'a'  then
                        let retstr = retstr || "A";
                     else
                        let retstr = retstr || "B";
                     end if;
                  elif  str[1,1] = 'c'  then
                     let retstr = retstr || "C";
                  else 
                     let retstr = retstr || "D";
                  end if;
              
               elif  str[1,1] < 'g'  then
                  if  str[1,1] = 'e'  then
                     let retstr = retstr || "E";
                  else
                     let retstr = retstr || "F";
                  end if;
               else 
                  let retstr = retstr || "G";
               end if;
            elif  str[1,1] < 'k'  then
               if  str[1,1] < 'j'  then
                  if  str[1,1] = 'h'  then
                     let retstr = retstr || "H";
                  else 
                     let retstr = retstr || "I";
                  end if;
               else 
                  let retstr = retstr || "J";
               end if;
            elif  str[1,1] < 'm'  then
               if  str[1,1] = 'k'  then
                  let retstr = retstr || "K";
               else 
                  let retstr = retstr || "L";
               end if;
            else 
               let retstr = retstr || "M";
            end if;
         elif  str[1,1] < 'u'  then
            if str[1,1] < 'r'  then
               if str[1,1] < 'p' then
                  if str[1,1] = 'n' then
                     let retstr = retstr || "N";
                  else
                     let retstr = retstr || "O";
                  end if;
               elif str[1,1] = 'p' then 
                  let retstr = retstr || "P";
               else 
                  let retstr = retstr || "Q";
               end if;
            elif str[1,1] < 't' then
               if str[1,1] = 'r' then
                  let retstr = retstr || "R";
               else
                  let retstr = retstr || "S";
               end if;
            else
               let retstr = retstr || "T";
            end if; 
         elif  str[1,1] < 'x'  then
            if str[1,1] < 'w' then
               if str[1,1] = 'u' then
                  let retstr = retstr || "U";
              else
                  let retstr = retstr || "V";
               end if;
            else 
               let retstr = retstr || "W";
            end if;
         elif str[1,1] < 'z' then
            if str[1,1] = 'x' then
               let retstr = retstr || "X";
            else
               let retstr = retstr || "Y";
            end if;
         else
            let retstr = retstr || "Z";
         end if;
--      end-- if;

      LET str = str[2,255];

  END FOR;

--      LET retstr = retstr[2,255];

      RETURN retstr;

   END IF;

END PROCEDURE;

 

[fanik]

Thank you olded.
I have done simply corrections in that code and it works.
XDEFINE retstr VARCHAR(255);
is replased with
DEFINE retstr VARCHAR(255);
and missed declaration
define length_str integer;
is filled.

If you know other web pages with examples of routines, please send me.

 

[fanik]

Thank you olded.
Here is the code of procedure word(a,b) which takes two values: string a (with words and ' '-space) and number of words b which should be returned like string of words delimited by ' '-space.
The code is tested and it is working.
code:

create procedure word(naziv varchar(255), br integer) returning varchar(255);

define len,i, k integer;
define pom,poma varchar(255);
define pom1 varchar(255);
define pom2 varchar(255);
define pom3 varchar(255);
define a,b varchar(255);
--set debug file to 'err.sql';
--trace on;

let len = length(trim(naziv));
let poma=trim(naziv);
let pom1=trim(naziv);
let pom3='';
let pom2=pom1[1,1];
let k=br;
if k=0 then return pom3;
end if;
if naziv is null then return null;
elif length(trim(naziv))=0 then return pom3;
end if;
LET a = '';
for i=1 to len
let pom2=poma[1,1];
let pom1=poma[2,225];
let b=pom2;

if pom2=' ' then
if length(pom1)=length(trim(pom1)) then
let k=k-1;
else let poma=" " || trim(pom1);
let k=k-1;
end if;
end if;
if k<>0 then let a=a || poma[1,1];
else exit for;
end if;
let poma=poma[2,255];
end for;
return a;
--trace off;
end procedure;

 

[PHV]

returns the first or the second or etc. word
I think your procedure returns the br first word[!]s[/!].
If you wanted the br'[!]th[/!] word, you may try this:

CREATE PROCEDURE word(naziv VARCHAR(255),br INTEGER) RETURNING VARCHAR(255);
DEFINE i,k INTEGER;
DEFINE poma,pom1,a VARCHAR(255);
DEFINE pom2 CHAR;
--set debug file to 'sql.err';
--trace on;
IF naziv IS NULL THEN RETURN NULL; END IF;
LEt poma=TRIM(naziv);
IF LENGTH(poma)=0 OR br=0 THEN RETURN ''; END IF;
LET pom1,pom2,k,a=poma,poma[1,1],br,'';
FOR i=1 TO LENGTH(poma)
  LET pom2,pom1=poma[1,1],poma[2,225];
  IF pom2=' ' THEN
    IF LENGTH(pom1)!=LENGTH(TRIM(pom1)) THEN
      LET poma=' ' || TRIM(pom1);
    END IF;
    LET k=k-1;
    IF k=0 THEN EXIT FOR; END IF;
    LET a='';
  ELSE
    LET a=a||pom2;
  END IF;
  LET poma=poma[2,255];
END FOR;
--trace off;
RETURN a;
END PROCEDURE;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886