Thread: Ambiguous error message
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 innerjoin 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
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
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
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, seehttp://www.rfc1149.net/wsff) Sam
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
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/
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