Re: Order by in SPL routines
Thread starts with confusion over the IDS 10 doc statement that "ORDER BY is not valid in queries within an SPL routine," since people use it successfully. Others suspected a doc bug, but the v11 wording clarifies it: ORDER BY is only allowed when the query is driven by a FOREACH loop, not in a singleton SELECT ... INTO. A follow-up case (SELECT FIRST 1 ... INTO ... ORDER BY inside a FOREACH) still gave a syntax error; posters agreed it ought to work and suggested raising it with IBM support. No fix recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Probably that's what clarified in v11 doc ... http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.sqls.doc/sqls723.htm *The ORDER BY clause implies that the query returns more than one row. In SPL, the database server issues an error if you specify the ORDER BY clause without a FOREACH loop to process the returned rows individually within the SPL routine. * It means one cannot have "SELECT c1 INTO lc1 FROM t1 *ORDER BY c1*" inside SPL. But "*FOREACH* SELECT c1 INTO lc1 FROM t1 *ORDER BY c1* .... *END FOREACH*" is a valid approach. Thanks, Rahul. On Fri, Mar 14, 2008 at 6:13 PM, Jack Parker <jack.parker4@verizon.net> wrote: > Submit a doc bug. > > j. > > Sane ego te vocavi. Forsitan capedictum tuum desit. > > -----Original Message----- > From: informix-list-bounces@iiug.org > [mailto:informix-list-bounces@iiug.org]On Behalf Of Rich > Sent: Friday, March 14, 2008 6:04 AM > To: informix-list@iiug.org > Subject: Re: Order by in SPL routines > > > On 14 Mar, 05:21, Krishna <calvinkri...@gmail.com> wrote: > > The section on 'Order by' in the 10.x documentation blankly mentions > > that: > > "The ORDER BY clause is not valid in queries within an SPL routine." > > > > (The last line in the page > > belowhttp://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/co.. > .) > > > > What is the context here? I have been using Order by's in queries > > within SPL routines and so > > far it seems to producing the proper ordered results. Would be great > > it someone can clarify. > > > > Krishna > > I've never had an issue, must be a mistake, surely? > > I've tried to imagine what they might have been trying to say...but > can't come up with any (even slightly) plausible explanation! > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- NJoy, Rahul.
On Mar 14, 10:16 pm, "Rahul Dhuvad" <tora...@gmail.com> wrote:
> Probably that's what clarified in v11 doc ...http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic...
>
> *The ORDER BY clause implies that the query returns more than one row. In
> SPL, the database server issues an error if you specify the ORDER BY clause
> without a FOREACH loop to process the returned rows individually within the
> SPL routine.
> *
Thanks for that.
That explains why the following fails with a "Syntax error" at the
select statement? Am interested in finding out the first record
returned by the result-set when ordered by inactive date (yes a much
cleaner idea would be to use a sub-query but the actual business
condition was a bit more complicated than this)
foreach crsr for select a, b
into l_a, l_b
from t1
select first 1 extension
into l_r
from t2
where i = l_a and
j = l_b
order by dateinactive desc;
update t1
set c = l_r
where current of crsr;
end foreach;
>
> It means one cannot have "SELECT c1 INTO lc1 FROM t1 *ORDER BY c1*" inside
> SPL. But "*FOREACH* SELECT c1 INTO lc1 FROM t1 *ORDER BY c1* .... *END
> FOREACH*" is a valid approach.
>
> Thanks,
> Rahul.
>
> On Fri, Mar 14, 2008 at 6:13 PM, Jack Parker <jack.park...@verizon.net>
> wrote:
>
>
>
> > Submit a doc bug.
>
> > j.
>
> > Sane ego te vocavi. Forsitan capedictum tuum desit.
>
> > -----Original Message-----
> > From: informix-list-boun...@iiug.org
> > [mailto:informix-list-boun...@iiug.org]On Behalf Of Rich
> > Sent: Friday, March 14, 2008 6:04 AM
> > To: informix-l...@iiug.org
> > Subject: Re: Order by in SPL routines
>
> > On 14 Mar, 05:21, Krishna <calvinkri...@gmail.com> wrote:
> > > The section on 'Order by' in the 10.x documentation blankly mentions
> > > that:
> > > "The ORDER BY clause is not valid in queries within an SPL routine."
>
> > > (The last line in the page
>
> > belowhttp://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/co..
> > .)
>
> > > What is the context here? I have been using Order by's in queries
> > > within SPL routines and so
> > > far it seems to producing the proper ordered results. Would be great
> > > it someone can clarify.
>
> > > Krishna
>
> > I've never had an issue, must be a mistake, surely?
>
> > I've tried to imagine what they might have been trying to say...but
> > can't come up with any (even slightly) plausible explanation!
You are right; I too would expect your singleton query having ORDER BY with
FIRST 1 usage to be allowed within SPL. You may want to contact IBM tech
support to let them know about this requirement.
Cheers,
Rahul.
On Sat, Mar 15, 2008 at 3:31 PM, Krishna <calvinkrishy@gmail.com> wrote:
> On Mar 14, 10:16 pm, "Rahul Dhuvad" <tora...@gmail.com> wrote:
> > Probably that's what clarified in v11 doc
> ...http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic...
> >
> > *The ORDER BY clause implies that the query returns more than one row.
> In
> > SPL, the database server issues an error if you specify the ORDER BY
> clause
> > without a FOREACH loop to process the returned rows individually within
> the
> > SPL routine.
> > *
>
> Thanks for that.
>
> That explains why the following fails with a "Syntax error" at the
> select statement? Am interested in finding out the first record
> returned by the result-set when ordered by inactive date (yes a much
> cleaner idea would be to use a sub-query but the actual business
> condition was a bit more complicated than this)
>
> foreach crsr for select a, b
> into l_a, l_b
> from t1
>
> select first 1 extension
> into l_r
> from t2
> where i = l_a and
> j = l_b
> order by dateinactive desc;>
> update t1
> set c = l_r
> where current of crsr;
> end foreach;
>
>
>
> >
> > It means one cannot have "SELECT c1 INTO lc1 FROM t1 *ORDER BY c1*"
> inside
> > SPL. But "*FOREACH* SELECT c1 INTO lc1 FROM t1 *ORDER BY c1* .... *END
> > FOREACH*" is a valid approach.
> >
> > Thanks,
> > Rahul.
> >
> > On Fri, Mar 14, 2008 at 6:13 PM, Jack Parker <jack.park...@verizon.net>
> > wrote:
> >
> >
> >
> > > Submit a doc bug.
> >
> > > j.
> >
> > > Sane ego te vocavi. Forsitan capedictum tuum desit.
> >
> > > -----Original Message-----
> > > From: informix-list-boun...@iiug.org
> > > [mailto:informix-list-boun...@iiug.org]On Behalf Of Rich
> > > Sent: Friday, March 14, 2008 6:04 AM
> > > To: informix-l...@iiug.org
> > > Subject: Re: Order by in SPL routines
> >
> > > On 14 Mar, 05:21, Krishna <calvinkri...@gmail.com> wrote:
> > > > The section on 'Order by' in the 10.x documentation blankly mentions
> > > > that:
> > > > "The ORDER BY clause is not valid in queries within an SPL routine."
> >
> > > > (The last line in the page
> >
> > >
> belowhttp://publib.boulder.ibm.com/infocenter/ids9help/index.jsp?topic=/co..
> > > .)
> >
> > > > What is the context here? I have been using Order by's in queries
> > > > within SPL routines and so
> > > > far it seems to producing the proper ordered results. Would be great
> > > > it someone can clarify.
> >
> > > > Krishna
> >
> > > I've never had an issue, must be a mistake, surely?
> >
> > > I've tried to imagine what they might have been trying to say...but
> > > can't come up with any (even slightly) plausible explanation!
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
>