[SQL] problem with 'insert into...': ADDITIONAL EXAMPLES

On Tue, 23 Jun 1998, William D. McCoy wrote:

> I am trying to work around the lack of an outer join function in
> postgreSql. I am trying to construct a full outer join of two
> identically defined tables, each of which contains (along with other
> data), a value for a particular lab sample.
>
> I have come across the following problem. When I execute the
> following query it returns the expected result (816 rows returned).
>
> select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values
> from free_ratios f
> where not exists (
> select *
> from hyd_ratios h
> where h.lab_no = f.lab_no
> and h.prep_no = f.prep_no
> and h.run_no = f.run_no
> )
> group by f.peak_values, f.lab_no, f.prep_no;
>
>
> However, when I add an 'insert into table_name' to the query like
> this:
>
> insert into both_ratios_avg
> select f.lab_no, f.prep_no, avg(f.fai), NULL, f.peak_values
> from free_ratios f
> where not exists (
> select *
> from hyd_ratios h
> where h.lab_no = f.lab_no
> and h.prep_no = f.prep_no
> and h.run_no = f.run_no
> )
> group by f.peak_values, f.lab_no, f.prep_no;
>
>
> I get the following error message:
>
> ERROR: parser: aggregates not allowed in GROUP BY clause
>
>
> There are clearly no aggregates in my GROUP BY clause.
>
>
> I get the same error message with the following query, although, just
> like the example above, the select by itself works fine:
>
>
> insert into both_ratios_avg
> select f.lab_no, f.prep_no, avg(fai), avg(hai), f.peak_values
> from free_ratios f, hyd_ratios h
> where f.lab_no = h.lab_no
> and f.prep_no = h.prep_no
> and f.run_no = h.run_no
> group by f.peak_values, f.lab_no, f.prep_no;
>
>
> Just to make things more interesting, the following very similar query
> works fine and does not give any error:
>
> insert into both_ratios_avg
> select h.lab_no, h.prep_no, NULL, avg(hai), h.peak_values
> from hyd_ratios h
> where not exists (
> select *
> from free_ratios f
> where f.lab_no = h.lab_no
> and f.prep_no = h.prep_no
> and f.run_no = h.run_no
> )
> group by h.peak_values, h.lab_no, h.prep_no;
>
>
> (This is another third of my full outer join workaround.) Any ideas
> about what might be happening here?
>
I don't know where the bug is, but I have similar results with
the following:

insert into historical_mv(symbol, mv, d) select s.symbol, sum(%(lw.price * s.shares)), lw.d
from stock s, quotes lw, timeinterval
where s.symbol=lw.tick and
lw.d=timeinterval.lastweek and
s.acquisition_date <= lw.d and
s.market_index=false
group by s.symbol, lw.d ;
ERROR: parser: aggregates not allowed in GROUP BY clause

and a different, but possibly related error with this modification:

insert into historical_mv(symbol, mv, d) select s.symbol, sum(%(lw.price * s.shares)), timeinterval.lastweek
from stock s, quotes lw, timeinterval
where s.symbol=lw.tick and
lw.d=timeinterval.lastweek and
s.acquisition_date <= lw.d and
s.market_index=false
group by s.symbol, timeinterval.lastweek ;
ERROR: The field being grouped by must appear in the target list

Speculating that the target list might by interpreted as (symbol, mv, d),
I attempt the following with the same results:
insert into historical_mv(symbol, mv, d)
select s.symbol, sum(lw.price * s.shares), timeinterval.lastweek
from stock s, quotes lw, timeinterval
where s.symbol=lw.tick and
lw.d=timeinterval.lastweek and
s.acquisition_date <= lw.d and
s.market_index=false
group by symbol, d ;
ERROR: parser: aggregates not allowed in GROUP BY clause

and deleting this target list still doesn't solve the problem.

insert into historical_mv
select s.symbol, sum(lw.price * s.shares), timeinterval.lastweek
from stock s, quotes lw, timeinterval
where s.symbol=lw.tick and
lw.d=timeinterval.lastweek and
s.acquisition_date <= lw.d and
s.market_index=false
group by s.symbol, timeinterval.lastweek ;
ERROR: The field being grouped by must appear in the target list

Marc Zuckman
marc(at)fallon(dot)classyad(dot)com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_ Visit The Home and Condo MarketPlace _
_ http://www.ClassyAd.com _
_ _
_ FREE basic property listings/advertisements and searches. _
_ _
_ Try our premium, yet inexpensive services for a real _
_ selling or buying edge! _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_