INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Jobs from Indeed

informix SPL while loop

informix SPL while loop

(OP)
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;
 

RE: informix SPL while loop

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

CODE

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]:

CODE

# 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;
 

RE: informix SPL while loop

(OP)
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.
 

RE: informix SPL while loop

(OP)
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;

 

RE: informix SPL while loop

returns the first or the second or etc. word
I think your procedure returns the br first words.
If you wanted the br'th word, you may try this:

CODE

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close