Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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 from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

fanik (Programmer) (OP)
26 May 10 8:38
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;
 
Helpful Member!  olded (Programmer)
26 May 10 22:13
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;
 
fanik (Programmer) (OP)
27 May 10 5:13
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 (Programmer) (OP)
1 Jun 10 9:05
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 (MIS)
23 Jun 10 11:45
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?

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!

Back To Forum

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