INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

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

# informix SPL while loop

 Forum Search FAQs Links MVPs

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

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.