
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
   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"

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 


Thu, 27 Jan 2000 21:07:35 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
[6.5.3] 'attribute not found' 
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

Thu, 27 Jan 2000 22:35:49 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] [6.5.3] 'attribute not found' 
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 


Thu, 27 Jan 2000 23:48:42 -0400 (AST)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] [6.5.3] 'attribute not found' 
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 Service and other goodies at
  http://donb.photo.net.

Thu, 27 Jan 2000 20:55:56 -0800
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] [6.5.3] 'attribute not found' 
> >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

Fri, 28 Jan 2000 15:25:36 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] [6.5.3] 'attribute not found'