On Thu, 27 Jan 2000, Tom Lane wrote:
> The Hermit Hacker <scrappy@hub.org> writes:
> > explain SELECT w.counter_id,
> > ( date_part('month', w.stat_date) || '/' ||
> > date_part('day', w.stat_date) || '/' ||
> > date_part('year', w.stat_date) || ' 00:00:00')::datetime as
> > dayperiod,
> > r.referrer_id, count(w.referrer_hits)
> > FROM webhit_referer_raw w, referrer_data r, referrer_link l
> > WHERE w.stat_date < 'Jan 25 2000'
> > AND w.referrer_url = r.referrer
> > AND l.counter_id = w.counter_id
> > AND l.stat_date = dayperiod
> > AND l.referrer_id = r.referrer_id
> > GROUP BY dayperiod, counter_id, referrer_id
> > ORDER BY counter_id, dayperiod;
>
> > I get:
> > ERROR: attribute 'dayperiod' not found
>
> > I'm guessing right now its on: "AND l.stat_date = dayperiod"
>
> Yup.
>
> > Should that not work?
>
> No, it shouldn't. Labeling SELECT output columns with AS doesn't
> affect the namespace visible in the WHERE clause (since WHERE is
> "upstream" of the SELECT outputs). If we had sub-selects in FROM,
> I believe that AS would determine the column names seen outside
> the sub-select ("downstream").
>
> Also, you can use the AS names in ORDER BY, which is also "downstream"
> of forming the results. (I think we currently accept them in GROUP BY
> as well, but I suspect that that is not in compliance with the
> standard...)
Yup, I'm using it in both the GROUP and ORDER BY ... not a big deal
though, the query looked like hell anyway :)
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org