Hi,

I've got a syntax problem on a Stored Procedure undert IDS 7.31. Is it
possible to concatenate integers and put them into a varchar variable
maintaining them as a uniqeu item? How? Are they canverted
automatically to chars within thgelist? My code looks like something
like this:

DEFINE varList varchar(250);
DEFINE varFirst smallint;

LET varList = '';
LET varFirst = 0;

CREATE PROCEDURE TEST()

FOREACH
SELECT var INTO varAux FROM table1

IF (varFirst = 0) THEN
LET varList = varAux;
LET varFirst = 1;
ELSE
LET varList = varList || ',' || TRIM(varAux);
END IF;

END FOREACH;

FOREACH

SELECT value
INTO valueAux
FROM table2 WHERE value NOT IN (varList)
order by 1 desc

END FOREACH

END PROCEDURE;


My problem is that when I execute the sentence:

SELECT value
INTO valueAux
FROM table2 WHERE value NOT IN (varList)

varList is trated as a unique parameter (char) and not as a group of
integers and this sentence returns always all the values. Any idea?

Thank you for your help.

Massoroto wrote:
> Hi,
>
> I've got a syntax problem on a Stored Procedure undert IDS 7.31. Is it
> possible to concatenate integers and put them into a varchar variable
> maintaining them as a uniqeu item? How? Are they canverted
> automatically to chars within thgelist? My code looks like something
> like this:

If you had vesrion 9 or 10, then you could use a collection.

>
> DEFINE varList varchar(250);
> DEFINE varFirst smallint;
>
> LET varList = '';
> LET varFirst = 0;
>
> CREATE PROCEDURE TEST()
>
> FOREACH
> SELECT var INTO varAux FROM table1
>
> IF (varFirst = 0) THEN
> LET varList = varAux;
> LET varFirst = 1;
> ELSE
> LET varList = varList || ',' || TRIM(varAux);
> END IF;
>
> END FOREACH;

That builds you a varList like "1,2,3,4".

>
> FOREACH
>
> SELECT value
> INTO valueAux
> FROM table2 WHERE value NOT IN (varList)
> order by 1 desc

What you have here is: ... value NOT IN ("1,2,3,4")
Whereas, what you want is: ... value NOT IN (1,2,3,4)

>
> END FOREACH
>
> END PROCEDURE;
>
>
> My problem is that when I execute the sentence:
>
> SELECT value
> INTO valueAux
> FROM table2 WHERE value NOT IN (varList)
>
> varList is trated as a unique parameter (char) and not as a group of
> integers and this sentence returns always all the values. Any idea?

Because none of your table2.values will be in "1,2,3,4".

Can't you just say:

SELECT value
INTO valueAux
FROM table2
WHERE value NOT IN (SELECT var FROM table1)

--
rh

Massoroto wrote:
> Hi,
>
> I've got a syntax problem on a Stored Procedure undert IDS 7.31. Is it
> possible to concatenate integers and put them into a varchar variable
> maintaining them as a uniqeu item? How? Are they canverted
> automatically to chars within thgelist? My code looks like something
> like this:

PLEASE, take Jonathan's advice in the Informix mini-FAQ posted here just
yesterday and read the "Smart Questions" WEB page:
http://www.catb.org/~esr/faqs/smart-questions.html

You've asked us to fix your broken approach to your problem, one of the
no-nos discussed there. Instead PLEASE post the problem and we'll try to
help you solve it.

Looks here like you are trying to write a stored procedure to perform
dynamic SQL ie to execute a SQL string built at runtime. You cannot do this
in SPL. There IS a datablade in the IIUG Software Repository that will let
you do this, but 7.31 does not support datablades. So, repost the problem
you have to solve and we'll try to help.

Art S. Kagel

> -----Original Message-----
> From: Massoroto [mailto:alanmas@gmail.com]
> Posted At: Thursday, February 02, 2006 3:11 AM
> Posted To: comp.databases.informix
> Conversation: Problem on SPL syntax in Informix 7.31
> Subject: Problem on SPL syntax in Informix 7.31
>
>
> Hi,
>
> I've got a syntax problem on a Stored Procedure undert IDS 7.31. Is it
> possible to concatenate integers and put them into a varchar variable
> maintaining them as a uniqeu item? How? Are they canverted
> automatically to chars within thgelist? My code looks like something
> like this:
>
> DEFINE varList varchar(250);
> DEFINE varFirst smallint;
>
> LET varList = '';
> LET varFirst = 0;
>
> CREATE PROCEDURE TEST()
>
> FOREACH
> SELECT var INTO varAux FROM table1
>
> IF (varFirst = 0) THEN
> LET varList = varAux;
> LET varFirst = 1;
> ELSE
> LET varList = varList || ',' || TRIM(varAux);
> END IF;
>
> END FOREACH;
>
> FOREACH
>
> SELECT value
> INTO valueAux
> FROM table2 WHERE value NOT IN (varList)
> order by 1 desc
>
> END FOREACH
>
> END PROCEDURE;
>
>
> My problem is that when I execute the sentence:
>
> SELECT value
> INTO valueAux
> FROM table2 WHERE value NOT IN (varList)
>
> varList is trated as a unique parameter (char) and not as a group of
> integers and this sentence returns always all the values. Any idea?
>
> Thank you for your help.
>

This looks like dynamic SPL, something you can't do.

I think this is more an application issue and could be rewritten to
avoid the need for dynamic spl


Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457

GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend the IDUG 2006 North America Conference.
Tampa, Florida, USA. 7-11 May 2006.
Visit http://www.iiug.org/conf for more information.