Re: referring to calculated column in sub select

On Mon, May 18, 2009 at 06:49:30AM -0700, Scara Maccai wrote:
> why column "acoltest" is not found by the subselect in this select:

The "acoltest" identifier is only visible from outside the query, not
within its defining query or any of its sub-queries.  If you're trying
to solve a problem like the example, it would probably be easiest to
swap the inner and outer queries around, i.e. something like:

  SELECT acoltest, MAX(b.t)
  FROM (
    SELECT acol + 100 as acoltest
    FROM mytab2
    GROUP BY 1) a LEFT JOIN mytab b ON a.acoltest = b.anothercol
  GROUP BY acoltest;

--
  Sam  http://samason.me.uk/