pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAmbiguous error message
7+ messages / 5 participants
[nested] [flat]
* Ambiguous error message
@ 2004-01-02 16:49 Samuel Tardieu <sam@rfc1149.net>
0 siblings, 4 replies; 7+ messages in thread
From: Samuel Tardieu @ 2004-01-02 16:49 UTC (permalink / raw)
To: pgsql-sql
In PostgreSQL 7.4, the following select:
select texten, total
from (select protocolid, count(*) as total) from ips where catid=1
group by protocolid order by protocolid) as c
inner join protocols using (protocolid);
gives the error message:
ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
Why isn't the "as c" considered as an alias?
Sam
--
Samuel Tardieu -- sam@rfc1149.net -- http://www.rfc1149.net/sam
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 00:46 Michael Glaesemann <grzm@myrealbox.com>
parent: Samuel Tardieu <sam@rfc1149.net>
3 siblings, 1 reply; 7+ messages in thread
From: Michael Glaesemann @ 2004-01-03 00:46 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
Hi Sam,
I'm not quite sure what you want the query to return, but you've got a
problem with your parentheses. You've got two FROM clauses and an INNER
JOIN, which together aren't arranged properly. I've rearranged your
query a little, but I haven't changed anything. Perhaps this'll make it
a little clearer to you.
On Jan 2, 2004, at 10:49 AM, Samuel Tardieu wrote:
> In PostgreSQL 7.4, the following select:
>
> select texten, total
> from (select protocolid, count(*) as total)
> from ips
> where catid=1
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
If you explain a little more what your query is trying to return, I
might be able to help more.
Michael Glaesemann
grzm myrealbox com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 00:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Samuel Tardieu <sam@rfc1149.net>
3 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2004-01-03 00:47 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
Samuel Tardieu <sam@rfc1149.net> writes:
> In PostgreSQL 7.4, the following select:
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
> gives the error message:
> ERROR: subquery in FROM must have an alias
> HINT: For example, FROM (SELECT ...) [AS] foo.
> Why isn't the "as c" considered as an alias?
It's complaining about this part:
from (select protocolid, count(*) as total) from ips where
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
It hasn't gotten as far as noticing your mismatched parentheses and
two top-level FROM keywords yet ;-).
It would probably be helpful if this message included a cursor location...
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 00:55 Samuel Tardieu <sam@rfc1149.net>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 7+ messages in thread
From: Samuel Tardieu @ 2004-01-03 00:55 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
On 2/01, Michael Glaesemann wrote:
| I'm not quite sure what you want the query to return, but you've got a
| problem with your parentheses.
I can't believe this :) I got hit by the "inner query needs to be aliased"
message and added it to the wrong place and them munged the query a lot.
The correct one was of course:
# select texten, total from (select protocolid, count(*) as total from ips
where catid=1 group by protocolid order by protocolid) as c
inner join protocols using (protocolid);
(PostgreSQL is used for the will-spam-for-food black-list, see
http://www.rfc1149.net/wsff)
Sam
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 00:56 Samuel Tardieu <sam@rfc1149.net>
parent: Samuel Tardieu <sam@rfc1149.net>
0 siblings, 0 replies; 7+ messages in thread
From: Samuel Tardieu @ 2004-01-03 00:56 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
On 3/01, Samuel Tardieu wrote:
| On 2/01, Michael Glaesemann wrote:
|
| | I'm not quite sure what you want the query to return, but you've got a
| | problem with your parentheses.
|
| I can't believe this :) I got hit by the "inner query needs to be aliased"
| message and added it to the wrong place and them munged the query a lot.
|
| The correct one was of course:
| # select texten, total from (select protocolid, count(*) as total from ips
| where catid=1 group by protocolid order by protocolid) as c
| inner join protocols using (protocolid);
(with the "order by" part moved at the end of the query to avoid losing the
order during the inner join)
Sam
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 01:05 Michael Fuhr <mike@fuhr.org>
parent: Samuel Tardieu <sam@rfc1149.net>
3 siblings, 0 replies; 7+ messages in thread
From: Michael Fuhr @ 2004-01-03 01:05 UTC (permalink / raw)
To: Samuel Tardieu <sam@rfc1149.net>; +Cc: pgsql-sql
On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> In PostgreSQL 7.4, the following select:
>
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
>
> gives the error message:
>
> ERROR: subquery in FROM must have an alias
> HINT: For example, FROM (SELECT ...) [AS] foo.
>
> Why isn't the "as c" considered as an alias?
The query has a right parenthesis after "as total" that looks like
it shouldn't be there.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ambiguous error message
@ 2004-01-03 01:30 Bertrand Petit <pgsql-sql@phoe.frmug.org>
parent: Samuel Tardieu <sam@rfc1149.net>
3 siblings, 0 replies; 7+ messages in thread
From: Bertrand Petit @ 2004-01-03 01:30 UTC (permalink / raw)
To: pgsql-sql
On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
>
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
^
+--- There
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
>
> gives the error message:
>
> ERROR: subquery in FROM must have an alias
> HINT: For example, FROM (SELECT ...) [AS] foo.
>
> Why isn't the "as c" considered as an alias?
That's maybe due to the highlighted spurious parenthesis.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-01-03 01:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-02 16:49 Ambiguous error message Samuel Tardieu <sam@rfc1149.net>
2004-01-03 00:46 ` Michael Glaesemann <grzm@myrealbox.com>
2004-01-03 00:55 ` Samuel Tardieu <sam@rfc1149.net>
2004-01-03 00:56 ` Samuel Tardieu <sam@rfc1149.net>
2004-01-03 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-01-03 01:05 ` Michael Fuhr <mike@fuhr.org>
2004-01-03 01:30 ` Bertrand Petit <pgsql-sql@phoe.frmug.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox