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.

Sybase ASE 12.5 - sql Incorrect syntax

8 posts in General Discussion Last posting was on 2011-06-16 08:38:03.0Z
manho Posted on 2011-06-15 04:04:30.0Z
Sender: 4e5b.4df82ef6.1804289383@sybase.com
From: manho
Newsgroups: sybase.public.ase.general
Subject: Sybase ASE 12.5 - sql Incorrect syntax
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df82f4e.4e66.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Jun 2011 21:04:30 -0700
X-Trace: forums-1-dub 1308110670 10.22.241.41 (14 Jun 2011 21:04:30 -0700)
X-Original-Trace: 14 Jun 2011 21:04:30 -0700, 10.22.241.41
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30256
Article PK: 72437

I am using Sybase ASE 12.5
I have two table as follow

TableA
PID, Amount1
1, 10
1, 20
2, 10
2, 10
3, 30

TableB
PID, Amount2
1, 20
1, 50
2, 30
2, 20

SELECT one.pid
, one.Amount1OfSum
, two.Amount2OfSum
FROM ( SELECT pid
, SUM(Amount1) AS Amount1OfSum
FROM TableA
GROUP
BY pid ) AS one,
( SELECT pid
, SUM(Amount2) AS Amount2OfSum
FROM TableB
GROUP
BY pid ) AS two
where two.pid *= one.pid

What I want the output is
PID, Amount1, Amount2
1, 30, 70
2, 20, 50
3, 30,

Incorrect syntax near the keyword 'select'.
Incorrect syntax near ')'.

Thank.


Rob V [ Sybase ] Posted on 2011-06-15 08:44:04.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
References: <4df82f4e.4e66.1681692777@sybase.com>
In-Reply-To: <4df82f4e.4e66.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4df870d4$1@forums-1-dub>
Date: 15 Jun 2011 01:44:04 -0700
X-Trace: forums-1-dub 1308127444 10.22.241.152 (15 Jun 2011 01:44:04 -0700)
X-Original-Trace: 15 Jun 2011 01:44:04 -0700, vip152.sybase.com
Lines: 67
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30259
Article PK: 72438


On 15-Jun-2011 06:04, manho wrote:
> I am using Sybase ASE 12.5
> I have two table as follow
>
> TableA
> PID, Amount1
> 1, 10
> 1, 20
> 2, 10
> 2, 10
> 3, 30
>
> TableB
> PID, Amount2
> 1, 20
> 1, 50
> 2, 30
> 2, 20
>
> SELECT one.pid
> , one.Amount1OfSum
> , two.Amount2OfSum
> FROM ( SELECT pid
> , SUM(Amount1) AS Amount1OfSum
> FROM TableA
> GROUP
> BY pid ) AS one,
> ( SELECT pid
> , SUM(Amount2) AS Amount2OfSum
> FROM TableB
> GROUP
> BY pid ) AS two
> where two.pid *= one.pid
>
> What I want the output is
> PID, Amount1, Amount2
> 1, 30, 70
> 2, 20, 50
> 3, 30,
>
> Incorrect syntax near the keyword 'select'.
> Incorrect syntax near ')'.
>
> Thank.

This should work fine in 12.5.1 or later: the query uses the 'derived
table' syntax which was added in ASE 12.5.1. Sinc eyou get an error, I'm
guessing you're using an earlier version of 12.5.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


manho Posted on 2011-06-15 13:26:07.0Z
Sender: 6151.4df8af3b.1804289383@sybase.com
From: manho
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df8b2ef.61c5.1681692777@sybase.com>
References: <4df870d4$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jun 2011 06:26:07 -0700
X-Trace: forums-1-dub 1308144367 10.22.241.41 (15 Jun 2011 06:26:07 -0700)
X-Original-Trace: 15 Jun 2011 06:26:07 -0700, 10.22.241.41
Lines: 29
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30260
Article PK: 72439

Hi Rob V.

Thank very much for your reply.

I am using ASE 12.5

I know I can get the same result by using following query

SELECT PID, sum(Amount1)
,
(SELECT SUM(Amount1) FROM TableB
WHERE PID = TableA.PID
GROUP BY PID) Amount1OfSum
FROM TableA
group by PID

but this db is only example.

In my actual database, it is more complicate.
I have to do currency exchange and other calculation, so I
think it is not possible to use the above method.


Do have you any idea if 'derived table' syntax is not
support?



Thank you for much.


HarryLai Posted on 2011-06-15 14:03:13.0Z
From: "HarryLai" <nospam_harrylhy@bigfoot.com>
Newsgroups: sybase.public.ase.general
References: <4df870d4$1@forums-1-dub> <4df8b2ef.61c5.1681692777@sybase.com>
In-Reply-To: <4df8b2ef.61c5.1681692777@sybase.com>
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
Lines: 4
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4df8bba1$1@forums-1-dub>
Date: 15 Jun 2011 07:03:13 -0700
X-Trace: forums-1-dub 1308146593 10.22.241.152 (15 Jun 2011 07:03:13 -0700)
X-Original-Trace: 15 Jun 2011 07:03:13 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30261
Article PK: 72440

You can write a stored procedure for your query, select the first sum value and one numeric column (for second sum value) to temp
table first, then use update syntax to update this numeric column with second sum value.


HTH


"manho" ¦b¶l¥ó±i¶K¤º®e¥D¦® 4df8b2ef.61c5.1681692777@sybase.com ¤¤¼¶¼g...

> Hi Rob V.
>
> Thank very much for your reply.
>
> I am using ASE 12.5
>
> I know I can get the same result by using following query
>
> SELECT PID, sum(Amount1)
> ,
> (SELECT SUM(Amount1) FROM TableB
> WHERE PID = TableA.PID
> GROUP BY PID) Amount1OfSum
> FROM TableA
> group by PID
>
> but this db is only example.
>
> In my actual database, it is more complicate.
> I have to do currency exchange and other calculation, so I
> think it is not possible to use the above method.
>
>
> Do have you any idea if 'derived table' syntax is not
> support?
>
>
>
> Thank you for much.


Rob V [ Sybase ] Posted on 2011-06-15 14:19:32.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
References: <4df870d4$1@forums-1-dub> <4df8b2ef.61c5.1681692777@sybase.com>
In-Reply-To: <4df8b2ef.61c5.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4df8bf74$1@forums-1-dub>
Date: 15 Jun 2011 07:19:32 -0700
X-Trace: forums-1-dub 1308147572 10.22.241.152 (15 Jun 2011 07:19:32 -0700)
X-Original-Trace: 15 Jun 2011 07:19:32 -0700, vip152.sybase.com
Lines: 55
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30262
Article PK: 72446


On 15-Jun-2011 15:26, manho wrote:
> Hi Rob V.
>
> Thank very much for your reply.
>
> I am using ASE 12.5
>
> I know I can get the same result by using following query
>
> SELECT PID, sum(Amount1)
> ,
> (SELECT SUM(Amount1) FROM TableB
> WHERE PID = TableA.PID
> GROUP BY PID) Amount1OfSum
> FROM TableA
> group by PID
>
> but this db is only example.
>
> In my actual database, it is more complicate.
> I have to do currency exchange and other calculation, so I
> think it is not possible to use the above method.
>
>
> Do have you any idea if 'derived table' syntax is not
> support?
>
>
>
> Thank you for much.

"12.5" is not specific enough -- which minor version of 12.5 are you using?
You can find out by running "select @@version".
As I said before, you need 12.5.1 in order to use the syntax you mentioned.
Otherwise, you will have to rewrite your query using different syntax;
this is probably easiest by cutting it up into a number of different steps.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


manho Posted on 2011-06-16 01:39:09.0Z
Sender: 5125.4df85148.1804289383@sybase.com
From: manho
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4df95ebd.7a02.1681692777@sybase.com>
References: <4df8bf74$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 15 Jun 2011 18:39:09 -0700
X-Trace: forums-1-dub 1308188349 10.22.241.41 (15 Jun 2011 18:39:09 -0700)
X-Original-Trace: 15 Jun 2011 18:39:09 -0700, 10.22.241.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30268
Article PK: 72448

Hi HTH, Rob V.

I learn the term is 'derived table' and I know I have to do
it by using temp table or sp. It is a big help.
Thank very much.

My version is
Adaptive Server Enterprise/12.5.0.3/EBF


hy Posted on 2011-06-16 02:17:05.0Z
From: "hy" <nospam_harrylhy@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4df8bf74$1@forums-1-dub> <4df95ebd.7a02.1681692777@sybase.com>
In-Reply-To: <4df95ebd.7a02.1681692777@sybase.com>
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
Lines: 4
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="big5"; reply-type=original
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 14.0.8117.416
X-MimeOLE: Produced By Microsoft MimeOLE V14.0.8117.416
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4df967a1@forums-1-dub>
Date: 15 Jun 2011 19:17:05 -0700
X-Trace: forums-1-dub 1308190625 10.22.241.152 (15 Jun 2011 19:17:05 -0700)
X-Original-Trace: 15 Jun 2011 19:17:05 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30269
Article PK: 72447

Hi manho

As I remember ASE 12.0.X (version older than yours) supports column subquery, If so, you can try the following sql that can produce
your result set, hope this help.


select distinct t1.pid,
t1amt = (select sum(a.amt) from t1 a where a.pid = t1.pid),
t2amt = (select isnull(sum(a.amt),0) from t2 a where a.pid = t1.pid)
from t1



"manho" 礎b繞l瞼籀簣i繞K瞻繙簧e瞼D礎簧 4df95ebd.7a02.1681692777@sybase.com 瞻瞻翹繞翹g...

> Hi HTH, Rob V.
>
> I learn the term is 'derived table' and I know I have to do
> it by using temp table or sp. It is a big help.
> Thank very much.
>
> My version is
> Adaptive Server Enterprise/12.5.0.3/EBF


Rob V [ Sybase ] Posted on 2011-06-16 08:38:03.0Z
From: "Rob V [ Sybase ]" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Sybase ASE 12.5 - sql Incorrect syntax
References: <4df8bf74$1@forums-1-dub> <4df95ebd.7a02.1681692777@sybase.com>
In-Reply-To: <4df95ebd.7a02.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4df9c0eb$1@forums-1-dub>
Date: 16 Jun 2011 01:38:03 -0700
X-Trace: forums-1-dub 1308213483 10.22.241.152 (16 Jun 2011 01:38:03 -0700)
X-Original-Trace: 16 Jun 2011 01:38:03 -0700, vip152.sybase.com
Lines: 38
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30271
Article PK: 72449


On 16-Jun-2011 03:39, manho wrote:
> Hi HTH, Rob V.
>
> I learn the term is 'derived table' and I know I have to do
> it by using temp table or sp. It is a big help.
> Thank very much.
>
> My version is
> Adaptive Server Enterprise/12.5.0.3/EBF

Ah, I see, sorry for that.
For your information 'derived table' is the construct where a subquery
is used instead of a table in a from clause (this is sometimes also
known as an "inline view"):

select * from
( select a,b,c from mytable) as t -- <<== this is the derived table

Your example used two derived tables which were joined together.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------