problem with for each in stored procedure
A FOREACH loop in an SPL procedure using SELECT FIRST 1 ... ORDER BY ... DESC returned only the first row on Sun but appeared to iterate over all rows on an IBM machine, leaving the variables holding the last row's values. Suggestions included parenthesising the OR conditions (later noted as unnecessary without ANDs) and adding EXIT FOREACH after the SELECT. The latter fixed it: the poster confirmed the procedure simply lacked EXIT FOREACH, so the loop kept iterating. A side discussion covered whether FIRST is allowed in singleton SELECT INTO/LET.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
I'm using FOR EACH loop in a stored procedure, sql statement is
FOR Each
select first 1 column1, column2 into temp1, temp2
from table1
where column1 = '11111' orcolumn1 = '1111' or
...
column1 = '1'
order by column1 desc
end for each
In sun servers it is working fine and giving only 1st records values of temp1
is '11111' but in ibm servers this query is giving all the records and when
loop is terminated, value of temp1 is '1'. Please help to solve the problem
informix version for both servers is: Informix Dynamic Server Version 7.31.UD7
Did you mean FOREACH? With no space?
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
VIDYA PANDEY
Sent: Tuesday, November 04, 2008 7:32 AM
To: ids@iiug.org
Subject: problem with for each in stored procedure [13876]
Hi,
I'm using FOR EACH loop in a stored procedure, sql statement is
FOR Each
select first 1 column1, column2 into temp1, temp2
from table1
where column1 = '11111' orcolumn1 = '1111' or
....
column1 = '1'
order by column1 desc
end for each
In sun servers it is working fine and giving only 1st records values of
temp1
is '11111' but in ibm servers this query is giving all the records and when
loop is terminated, value of temp1 is '1'. Please help to solve the problem
informix version for both servers is: Informix Dynamic Server Version
7.31.UD7
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, Yes. The syntax of stored procedure is correct. Problem is happening based on machine ( sun or ibm) Regards Vidya
Hi Vidya, Try this, FOR Each select first 1 column1, column2 into temp1, temp2 from table1 where ( column1 = '11111' or column1 = '1111' or .... column1 = '1' ) order by column1 desc end for each NOTE : Please see the ( ) around where cluase... All "OR" must be within ( and ) Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: problem with for each in stored procedure [13876]> Date: Tue, 4 Nov 2008 07:32:04 -0500> > Hi, > I'm using FOR EACH loop in a stored procedure, sql statement is > > FOR Each > select first 1 column1, column2 into temp1, temp2 > from table1 > where column1 = '11111' or > column1 = '1111' or > .... > column1 = '1' > order by column1 desc > end for each > > In sun servers it is working fine and giving only 1st records values of temp1 > is '11111' but in ibm servers this query is giving all the records and when > loop is terminated, value of temp1 is '1'. Please help to solve the problem > > informix version for both servers is: Informix Dynamic Server Version 7.31.UD7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Stay up to date on your PC, the Web, and your mobile phone with Windows Live. http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/
Hi, All "or's" are grouped within "(" and ")". It is working for sun but for IBM server it is not working. Regards Vidya
Hi Vidya, Not sure why you are having an issue with it.. Let's try this.. 1) Remove the "Foreach" and "End Foreach" as you are interested in one record only. OR 2) You may include "exit Foreach" after your select statement to force the termination of execution... I don't have testing environment now and therefore can't say for sure...I would surely like to test it...I am sure I hRegards, Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: Re: RE: problem with for each in stored procedure [13880]> Date: Tue, 4 Nov 2008 08:27:41 -0500> > Hi, > All "or's" are grouped within "(" and ")". > It is working for sun but for IBM server it is not working. > Regards > Vidya > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Stay up to date on your PC, the Web, and your mobile phone with Windows Live. http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/
Hi, The SQL will return multiple records from the table. Out of them the max match record which is the first record when we do order by desc should be picked up. For sun machine the SQL is applied directly on the table and only one record is picked. For IBM the SQL is returing all the records and in "ForEach" all the previous value is getting over-written and the last value is retained. Regards Vidya
Hi Vidya, Did you already try either of the steps that I have recommended in my last email? Please try and see how it works. Let me know. Regards, Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: Re: RE: problem with for each in stored procedure [13882]> Date: Tue, 4 Nov 2008 08:44:11 -0500> > Hi, > > The SQL will return multiple records from the table. > Out of them the max match record which is the first record when we do order by > desc should be picked up. > > For sun machine the SQL is applied directly on the table and only one record > is picked. > For IBM the SQL is returing all the records and in "ForEach" all the previous > value is getting over-written and the last value is retained. > > Regards > Vidya > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Stay up to date on your PC, the Web, and your mobile phone with Windows Live. http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/
FOREACH
SELECT mycloumns
INTO Lmycolumns
FROM mytable
WHERE blah blah ORDER BY blah DESC
EXIT FOREACH;
END FOREACH;
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
VIDYA PANDEY
Sent: Tuesday, November 04, 2008 7:44 AM
To: ids@iiug.org
Subject: Re: RE: problem with for each in stored procedure [13882]
Hi,
The SQL will return multiple records from the table.
Out of them the max match record which is the first record when we do
order by
desc should be picked up.
For sun machine the SQL is applied directly on the table and only one
record
is picked.
For IBM the SQL is returing all the records and in "ForEach" all the
previous
value is getting over-written and the last value is retained.
Regards
Vidya
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi, The second solution is working Let's try this.. 1) Remove the "Foreach" and "End Foreach" as you are interested in one record only. OR 2) You may include "exit Foreach" after your select statement to force the termination of execution... I don't have testing environment now and therefore can't say for sure...I would surely like to test it...I am sure I hRegards, Thanks vidya
No, can't use FIRST or ORDER BY in a LET or a singleton SELECT ... INTO var. That's why he's using the odd looking FOREACH loop. Art On Tue, Nov 4, 2008 at 8:34 AM, dharmendra sharma < dharmendrasharma@hotmail.com> wrote: > Hi Vidya, > > Not sure why you are having an issue with it.. > > Let's try this.. > 1) Remove the "Foreach" and "End Foreach" as you are interested in one > record > only. > > OR > > 2) You may include "exit Foreach" after your select statement to force the > termination of execution... > > I don't have testing environment now and therefore can't say for sure...I > would surely like to test it...I am sure I hRegards, > > Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: Re: RE: > problem with for each in stored procedure [13880]> Date: Tue, 4 Nov 2008 > 08:27:41 -0500> > Hi, > All "or's" are grouped within "(" and ")". > It is > working for sun but for IBM server it is not working. > Regards > Vidya > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > _________________________________________________________________ > Stay up to date on your PC, the Web, and your mobile phone with Windows > Live. > http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/ > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
OOOOOOOOOOOOOOOO: Is it possible that on the IBM machine there is a column named FIRST? That MIGHT make the 'column1' look like an alias and negate the functionality of the FIRST 1 clause. Art On Tue, Nov 4, 2008 at 8:44 AM, VIDYA PANDEY <vidyapati@huawei.com> wrote: > Hi, > > The SQL will return multiple records from the table. > Out of them the max match record which is the first record when we do order > by > desc should be picked up. > > For sun machine the SQL is applied directly on the table and only one > record > is picked. > For IBM the SQL is returing all the records and in "ForEach" all the > previous > value is getting over-written and the last value is retained. > > Regards > Vidya > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Hi Art,
I have tried it on IDS 11.5 and it works. I am unable to test it on prior
version(s).
But, I do believe we can use FIRST 1 in a singleton SELECT statement in
Informix
7+ versions..
Dharmendra
create procedure test () returning int;define l_i int;
select first 1 col1 into l_i from tab1 where col2 = 1244169;return l_i;end procedure;
execute procedure test();> To: ids@iiug.org> From: art.kagel@gmail.com> Subject: Re: problem with for
each in stored procedure [13886]> Date: Tue, 4 Nov 2008 11:08:22 -0500> > No,
can't use FIRST or ORDER BY in a LET or a singleton SELECT ... INTO > var.
That's why he's using the odd looking FOREACH loop. > > Art > > On Tue, Nov 4,
2008 at 8:34 AM, dharmendra sharma < > dharmendrasharma@hotmail.com> wrote: >
> > Hi Vidya, > > > > Not sure why you are having an issue with it.. > > > >
Let's try this.. > > 1) Remove the "Foreach" and "End Foreach" as you are
interested in one > > record > > only. > > > > OR > > > > 2) You may include
"exit Foreach" after your select statement to force the > > termination of
execution... > > > > I don't have testing environment now and therefore can't
say for sure...I > > would surely like to test it...I am sure I hRegards, > >
> > Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: Re: RE:
> > problem with for each in stored procedure [13880]> Date: Tue, 4 Nov 2008 >
> 08:27:41 -0500> > Hi, > All "or's" are grouped within "(" and ")". > It is >
> working for sun but for IBM server it is not working. > Regards > Vidya > >
> > > > > > > >
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum. > >
> _________________________________________________________________ > > Stay
up to date on your PC, the Web, and your mobile phone with Windows > > Live. >
> http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/ > > > > > > > >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. > > >
> > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors
(art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my
own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any
other organization > with which I am associated either explicitly or
implicitly. Neither do > those opinions reflect those of other individuals
affiliated with any entity > with which I am affiliated nor those of the
entities themselves. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
See how Windows Mobile brings your life togetherat home, work, or on the go.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
No. I just tried it on 10.00xC5 and it rejects the syntax:
> create procedure art() returning lvarchar, lvarchar;> define db, tab lvarchar;
> let db, tab = (select first 1 dbsname, tabname from systabnames order by 1
DESC );
> return db, tab;
> end procedure;
201: A syntax error has occurred.
Error in line 3Near character position 64
> create procedure art() returning lvarchar, lvarchar;> define db, tab lvarchar;
> let db, tab = (select first 1 dbsname, tabname from systabnames );
> return db, tab;
> end procedure;
944: Cannot use "first", "limit" or "skip" in this context.
Error in line 3Near character position 64
Same result using INTO instead of the LET syntax.
Art
On Tue, Nov 4, 2008 at 11:43 AM, dharmendra sharma <
dharmendrasharma@hotmail.com> wrote:
> Hi Art,
>
> I have tried it on IDS 11.5 and it works. I am unable to test it on prior
> version(s).
> But, I do believe we can use FIRST 1 in a singleton SELECT statement in
> Informix
> 7+ versions..
>
> Dharmendra
>
> create procedure test () returning int;> define l_i int;
>
> select first 1 col1 into l_i from tab1 where col2 = 1244169;> return l_i;end procedure;
> execute procedure test();> > To: ids@iiug.org> From: art.kagel@gmail.com> Subject: Re: problem with
> for
> each in stored procedure [13886]> Date: Tue, 4 Nov 2008 11:08:22 -0500> >
> No,
> can't use FIRST or ORDER BY in a LET or a singleton SELECT ... INTO > var.
> That's why he's using the odd looking FOREACH loop. > > Art > > On Tue, Nov
> 4,
> 2008 at 8:34 AM, dharmendra sharma < > dharmendrasharma@hotmail.com>
> wrote: >
> > > Hi Vidya, > > > > Not sure why you are having an issue with it.. > > >
> >
> Let's try this.. > > 1) Remove the "Foreach" and "End Foreach" as you are
> interested in one > > record > > only. > > > > OR > > > > 2) You may
> include
> "exit Foreach" after your select statement to force the > > termination of
> execution... > > > > I don't have testing environment now and therefore
> can't
> say for sure...I > > would surely like to test it...I am sure I hRegards, >
> >
> > > Dharmendra> To: ids@iiug.org> From: vidyapati@huawei.com> Subject: Re:
> RE:
> > > problem with for each in stored procedure [13880]> Date: Tue, 4 Nov
> 2008 >
> > 08:27:41 -0500> > Hi, > All "or's" are grouped within "(" and ")". > It
> is >
> > working for sun but for IBM server it is not working. > Regards > Vidya >
> >
> > > > > > > > >
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum. >
> >
> > _________________________________________________________________ > >
> Stay
> up to date on your PC, the Web, and your mobile phone with Windows > >
> Live. >
> > http://clk.atdmt.com/MRT/go/msnnkwxp1020093185mrt/direct/01/ > > > > > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum. > >
> >
> > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of
> Directors
> (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions
> are my
> own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any
> other organization > with which I am associated either explicitly or
> implicitly. Neither do > those opinions reflect those of other individuals
> affiliated with any entity > with which I am affiliated nor those of the
> entities themselves. > > >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum. >
> _________________________________________________________________
> See how Windows Mobile brings your life togetherat home, work, or on the
> go.
> http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
Hi all, Problem is solved. It was our programming mistake. we have not used "exit foreach". Regards Vidya
On Tue, Nov 4, 2008 at 5:22 AM, dharmendra sharma wrote: > Try this, > > FOR Each select first 1 column1, column2 into temp1, temp2 from table1 where ( > column1 = '11111' or column1 = '1111' or .... column1 = '1' ) order by column1 > desc end for each > > NOTE : Please see the ( ) around where clause... All "OR" must be within ( and > ) The 'NOTE' is not accurate when there are no AND clauses around. When there are AND and OR terms, then it is a good idea to parenthesize carefully to ensure that both IDS and the human readers of your SQL understand what you say exactly as you intended to say it. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.