Skip site navigation (1) Skip section navigation (2)

Re: missing FROM-clause notice but nothing is missing ...

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: missing FROM-clause notice but nothing is missing ...
Date: 2003-03-28 16:11:52
Message-ID: 87znnf31yv.fsf@stark.dyndns.tv (view raw or flat)
Thread:
Lists: pgsql-general
Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> writes:

> No go with the view ... same error:
> 
> DB=# SELECT products.id
>       FROM products
>       WHERE name ILIKE 'AA'
> 
>       UNION
> 
>       SELECT prod_id
>       FROM v_products_cast_crews
>       WHERE cast_crew=1012
> 
>       ORDER BY products.id;
> 
> NOTICE:  Adding missing FROM-clause entry for table "products"
> ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
> result columns

You could do "ORDER BY 1" to sort based on the first column, or you could just
remove the word "products." in both places. It should work if you say "ORDER
BY id". I would put AS id in the second select for clarity for the next reader
though.

Incidentally, are you sure you want UNION and not UNION ALL. The former has to
exclude overlaps and that's a lot of extra work.

-- 
greg


In response to

pgsql-general by date

Next:From: Jean-Luc LachanceDate: 2003-03-28 16:23:18
Subject: Re: missing FROM-clause notice but nothing is missing ...
Previous:From: Greg StarkDate: 2003-03-28 16:08:20
Subject: Re: Slow query needs a kick in the pants.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group