Thread: [6.5.3] 'attribute not found'
Using this Query:
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
WHEREw.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"
Should that not work? And, I know, its a major mess, just a thought I'm
playing with ... :)
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
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...)
regards, tom lane
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
At 10:35 PM 1/27/00 -0500, Tom Lane wrote:
>> 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).
And I agree ... in other words, the where clause determines the rows
to be processed and returned to the target list.
You are essentially asking that the result of the query be used as
a criterian for determining the result of the query...
> If we had sub-selects in FROM,
>I believe that AS would determine the column names seen outside
>the sub-select ("downstream").
Makes sense...
>
>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...)
After the discussion a few weeks ago I tried to decide by reading Date
(which I bought in part of the discussion, figuring it was about time)
and decided I couldn't tell for sure...
Now that I've got a draft copy of the ISO standard, maybe I'll find time
to dig into it.
May not be easy to decide for sure one way or the other...standards groups
occasionally leave such things more or less ambiguous as a means of moving
forward when dissenting parties (companies, usually) differ, in hopes that
in practice it won't matter.
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
> >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...)
> After the discussion a few weeks ago I tried to decide by reading Date
> (which I bought in part of the discussion, figuring it was about time)
> and decided I couldn't tell for sure...
Oh puhleez! Date... about time... Or maybe finding that funny means
I've been working on this stuff *way* too long :)
Anyway, afaik we will have the capability in 7.0 to specify column
aliases in AS clauses like this:
postgres=# select a+b from t1 as tx (a), t1 as ty (b);?column?
---------- 2
(1 row)
This is actually running on my computer now; I'm starting to rework
the "outer join syntax" and will then do a commit.
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California