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