Обсуждение: Difference between Access97 and PostgreSQL
Hello all, My question may out of the range of this forum. When I run the following query under postgreSQL and Access2000, it works well. But Access97 cannot accept it. I got the error message as 'syntax error near FROM', it seems like in Access97, there cannot have a subquery after 'FROM' -------------------------------------------------- SELECT temp.rating, temp.avgage FROM (SELECT s.rating, AVG(s.age) as avgage FROM sailors s GROUP BY s.rating) as temp -------------------------------------------------- I did check the help information from Access2000 about 'what's new in Access 2000', since i want to find out what the difference make Access2000 accept this query, but no result :-( You helps are very desirable. Thanks in advance. Annie _________________________________________________________________ Chat with friends online, try MSN Messenger: http://messenger.msn.com
Annie, > My question may out of the range of this forum. Well, yes. But, since I'm a nice guy, I'll answer your question eventhough it does not relate to Postgres ... if you promisenot to do itagain! In the future, send this question to a more appropriate list,such as PSQL-ODBC, where they discussPaotgres & MS Access. > When I run the following query under postgreSQL and Access2000, it > works well. But Access97 cannot accept it. I got the error message > as 'syntax error near FROM', it seems like in Access97, there cannot > have a subquery after 'FROM' > > -------------------------------------------------- > SELECT temp.rating, temp.avgage > FROM (SELECT s.rating, AVG(s.age) as avgage > FROM sailors s GROUP BY s.rating) as temp > -------------------------------------------------- > You are exactly correct. MS Access 97 does not support sub-queires inthe FROM clause. THe only sub-queries supported byAccess 97 aresub-queries in the WHERE clause. All the more reason to use PostgreSQL! -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Wed, Jan 30, 2002 at 11:54:56PM +0000, Yan Bai wrote: > > -------------------------------------------------- > SELECT temp.rating, temp.avgage > FROM (SELECT s.rating, AVG(s.age) as avgage > FROM sailors s GROUP BY s.rating) as temp Have you tried this query without the subselect? It looks like it should work. -Roberto -- +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer It said "Insert disk #3", but only 2 will fit!
> On Wed, Jan 30, 2002 at 11:54:56PM +0000, Yan Bai wrote: > > > > -------------------------------------------------- > > SELECT temp.rating, temp.avgage > > FROM (SELECT s.rating, AVG(s.age) as avgage > > FROM sailors s GROUP BY s.rating) as temp > > Have you tried this query without the subselect? It looks like it should > work. What about this? SELECT temp.rating, temp.avgage FROM (SELECT s.rating, AVG(s.age) as avgage FROM sailors s GROUP BY s.rating, s.age) as temp Chris
> -----Original Message----- > From: Josh Berkus [mailto:josh@agliodbs.com] > Sent: 31 January 2002 00:58 > To: Yan Bai; pgsql-sql@postgresql.org > Subject: Re: Difference between Access97 and PostgreSQL > > > Annie, > > > My question may out of the range of this forum. > > Well, yes. But, since I'm a nice guy, I'll answer your question even > though it does not relate to Postgres ... if you promise not to do it > again! In the future, send this question to a more appropriate list, > such as PSQL-ODBC, where they discuss Paotgres & MS Access. > > > When I run the following query under postgreSQL and Access2000, it > > works well. But Access97 cannot accept it. I got the error message > > as 'syntax error near FROM', it seems like in Access97, > there cannot > > have a subquery after 'FROM' > > > > -------------------------------------------------- > > SELECT temp.rating, temp.avgage > > FROM (SELECT s.rating, AVG(s.age) as avgage > > FROM sailors s GROUP BY s.rating) as temp > > -------------------------------------------------- > > > > You are exactly correct. MS Access 97 does not support sub-queires in > the FROM clause. THe only sub-queries supported by Access 97 are > sub-queries in the WHERE clause. With this sort of problem (I also vaguely recall ahving a problem with a union) I tend to make another query (as in an MS Access query that anyone else would call a view) and use it in the from clause. Cheers, - Stuart