Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Select, First, Order by - beginner

3 posts in General Discussion Last posting was on 2007-09-19 20:29:10.0Z
noob Posted on 2007-09-19 19:02:09.0Z
Sender: 6e73.46f1707e.1804289383@sybase.com
From: noob
Newsgroups: ianywhere.public.general
Subject: Select, First, Order by - beginner
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46f17231.6e90.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Sep 2007 12:02:09 -0700
X-Trace: forums-1-dub 1190228529 10.22.241.41 (19 Sep 2007 12:02:09 -0700)
X-Original-Trace: 19 Sep 2007 12:02:09 -0700, 10.22.241.41
Lines: 28
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6297
Article PK: 2818

Hello - I am having trouble with the below statement. If
someone could help point me in the right direction I would
appreciate the help. I am using SQL Anywhere 8. Thanks in
advance.

declare @mtag char(50);
declare @ctag char(50);

This is what I had orginally but leaving the @ctag select as
is errors when an asset has multiple fail records

set @mtag=(select mtag from mt.inventory where i4201 =
@control);
set @ctag=(select ctag from mt.calibration where mtag =
@mtag and c2323 = 'N' );

so I changed @ctag to this which fails. I get a syntax error
near order by

set @ctag=(select first ctag from mt.calibration where mtag
= @mtag and c2323 = 'N' order by c2316 desc);

but when I tested it with this - it works
select first ctag from mt.calibration where mtag = 'foo:foo'
and c2323 = 'N' order by c2316 desc

I am not sure why I am getting a syntax error by the order
by clause. Have any idea?


"Nick Elson" < Posted on 2007-09-19 20:03:09.0Z
From: "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@>
Newsgroups: ianywhere.public.general
References: <46f17231.6e90.1681692777@sybase.com>
Subject: Re: Select, First, Order by - beginner
Lines: 69
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: nicelson-d620.sybase.com
X-Original-NNTP-Posting-Host: nicelson-d620.sybase.com
Message-ID: <46f1807d$1@forums-1-dub>
Date: 19 Sep 2007 13:03:09 -0700
X-Trace: forums-1-dub 1190232189 10.25.98.247 (19 Sep 2007 13:03:09 -0700)
X-Original-Trace: 19 Sep 2007 13:03:09 -0700, nicelson-d620.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6298
Article PK: 4693

That's a limitation of all releases of 8 and earlier products.

This works in version 9 and up. Order-by clauses in views
and set statements was never supported before release 9.
The reference to this enhancement is noted at bottom.

For example this sample SQL works in 9.0.2 (w\asademo.db)

create variable @mtag char(50);
create variable @ctag char(50);
set @mtag = (select dept_id from department where dept_name='Sales');
set @ctag = ( select first emp_id from employee where dept_id=@mtag
order by emp_id)

but fails exactly the same was as yours does in 8.0.3 late ebf.


Reference (from the ASA 9/10 docs):
=========================

What's New in SQL Anywhere Studio
What's New in Version 9.0.0
New features in version 9.0.0
Adaptive Server Anywhere new features
. . .
SQL enhancements
. . .
ORDER BY clause allowed in all contexts In previous releases, many SELECT
statements in view definitions, in subqueries, or in UNION operations were
not allowed to use an ORDER BY clause. This restriction has now been
removed.

In some cases, particularly when combined with the FIRST or TOP clause,
using a SELECT with an ORDER BY clause does affect the results of a view
definition or a ***set operation***. In other contexts, the ORDER BY clause
is allowed but makes no difference to the operation.

<noob> wrote in message news:46f17231.6e90.1681692777@sybase.com...
> Hello - I am having trouble with the below statement. If
> someone could help point me in the right direction I would
> appreciate the help. I am using SQL Anywhere 8. Thanks in
> advance.
>
> declare @mtag char(50);
> declare @ctag char(50);
>
> This is what I had orginally but leaving the @ctag select as
> is errors when an asset has multiple fail records
>
> set @mtag=(select mtag from mt.inventory where i4201 =
> @control);
> set @ctag=(select ctag from mt.calibration where mtag =
> @mtag and c2323 = 'N' );
>
> so I changed @ctag to this which fails. I get a syntax error
> near order by
>
> set @ctag=(select first ctag from mt.calibration where mtag
> = @mtag and c2323 = 'N' order by c2316 desc);
>
> but when I tested it with this - it works
> select first ctag from mt.calibration where mtag = 'foo:foo'
> and c2323 = 'N' order by c2316 desc
>
> I am not sure why I am getting a syntax error by the order
> by clause. Have any idea?


Crys Posted on 2007-09-19 20:29:10.0Z
Sender: 6ea2.46f172dd.1804289383@sybase.com
From: Crys
Newsgroups: ianywhere.public.general
Subject: Re: Select, First, Order by - beginner
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <46f18695.70f1.1681692777@sybase.com>
References: <46f1807d$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 19 Sep 2007 13:29:10 -0700
X-Trace: forums-1-dub 1190233750 10.22.241.41 (19 Sep 2007 13:29:10 -0700)
X-Original-Trace: 19 Sep 2007 13:29:10 -0700, 10.22.241.41
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6299
Article PK: 4694

Thanks Nick for your response!

> That's a limitation of all releases of 8 and earlier
> products.
>
> This works in version 9 and up. Order-by clauses in views
> and set statements was never supported before release 9.
> The reference to this enhancement is noted at bottom.
>
> For example this sample SQL works in 9.0.2 (w\asademo.db)
>
> create variable @mtag char(50);
> create variable @ctag char(50);
> set @mtag = (select dept_id from department where
> dept_name='Sales');
> set @ctag = ( select first emp_id from employee where
> dept_id=@mtag order by emp_id)
>
> but fails exactly the same was as yours does in 8.0.3 late
> ebf.
>
>
> Reference (from the ASA 9/10 docs):
> =========================
>
> What's New in SQL Anywhere Studio
> What's New in Version 9.0.0
> New features in version 9.0.0
> Adaptive Server Anywhere new features
> . . .
> SQL enhancements
> . . .
> ORDER BY clause allowed in all contexts In previous
> releases, many SELECT statements in view definitions, in
> subqueries, or in UNION operations were not allowed to
> use an ORDER BY clause. This restriction has now been
> removed.
>
> In some cases, particularly when combined with the FIRST
> or TOP clause, using a SELECT with an ORDER BY clause
> does affect the results of a view definition or a ***set
> operation***. In other contexts, the ORDER BY clause is
> allowed but makes no difference to the operation.
>
>
> <noob> wrote in message
> > news:46f17231.6e90.1681692777@sybase.com... Hello - I am
> > having trouble with the below statement. If someone
> > could help point me in the right direction I would
> > appreciate the help. I am using SQL Anywhere 8. Thanks
> in advance. >
> > declare @mtag char(50);
> > declare @ctag char(50);
> >
> > This is what I had orginally but leaving the @ctag
> > select as is errors when an asset has multiple fail
> records >
> > set @mtag=(select mtag from mt.inventory where i4201 =
> > @control);
> > set @ctag=(select ctag from mt.calibration where mtag =
> > @mtag and c2323 = 'N' );
> >
> > so I changed @ctag to this which fails. I get a syntax
> > error near order by
> >
> > set @ctag=(select first ctag from mt.calibration where
> > mtag = @mtag and c2323 = 'N' order by c2316 desc);
> >
> > but when I tested it with this - it works
> > select first ctag from mt.calibration where mtag =
> > 'foo:foo' and c2323 = 'N' order by c2316 desc
> >
> > I am not sure why I am getting a syntax error by the
> > order by clause. Have any idea?
>
>