Обсуждение: Problems with a Query between 7.3 and 8.2

Hi,

I have to migrate an existing System from PostgreSQL 7 and 8.2.
There is a query that I'm not fully understand. It is running with
7.3.21 generating two notices about missing FROM's:

select poid,prodort.bez1 as prodort,sum(anz) as anz,
art,bez1 from best_dat where type='P' and relid=best_hdr.relid
and best_hdr.prod_date='20.04.2008' and poid=prodort.id
group by poid,prodort.bez1,art,bez1
order by poid,prodort.bez1,poid,art,bez1;

NOTICE:  Adding missing FROM-clause entry for table "prodort"
NOTICE:  Adding missing FROM-clause entry for table "best_hdr"poid |       prodort        | anz | art |
bez1

------+----------------------+-----+-----+----------------------------------   1 | VK-Kueche            |   5 |  50 |
Malzkaffee                        1 | VK-Kueche            |  30 |  57 | Kakao                              1 |
VK-Kueche           |  18 |  58 | Milch warm                 ...
 


The tables are as follows:
                          Table "public.prodort"Column |     Type      |                     Modifiers

--------+---------------+----------------------------------------------------id     | integer       | not null default
nextval('"prodort_id_seq"'::text)bez1   | character(20) | code   | character(3)  | 
Indexes: prodort_id_key unique btree (id)
      Table "public.best_dat"Column  |     Type      | Modifiers 
---------+---------------+-----------relid   | integer       | not nulltype    | character(1)  | bpid    | integer
| not nullbpcode  | character(3)  | poid    | integer       | grpid   | integer       | not nullart     | integer
|bez1    | character(32) | anz     | integer       | portion | character(1)  | fp      | integer       | default
0wertig | integer       | default 1
 
Indexes: best_dat_rt btree (relid, "type")


PGSQL 8.2.6 bumps out with the following:

# select poid,prodort.bez1 as prodort,sum(anz) as anz,
# art,bez1 from best_dat where type='P' and relid=best_hdr.relid
# and best_hdr.prod_date='20.04.2008' and poid=prodort.id
# group by poid,prodort.bez1,art,bez1
# order by poid,prodort.bez1,poid,art,bez1;
ERROR:  missing FROM-clause entry for table "prodort"
LINE 1: select poid,prodort.bez1 as prodort,sum(anz) as anz,                   ^
t seems, that PGSQL is missing the From Clause for bez1 but there
is a prodort.bez1 in the query...?

How do I wirte this correctly?

THX,

Holm
--      Technik Service u. Handel Tiffe, www.tsht.de, Holm Tiffe,     Freiberger Straße 42, 09600 Oberschöna, USt-Id:
DE253710583www.tsht.de, info@tsht.de, Fax +49 3731 74200, Mobil: 0172 8790 741
 

am  Wed, dem 04.06.2008, um 12:47:41 +0200 mailte Holm Tiffe folgendes:
> 
> Hi,
> 
> I have to migrate an existing System from PostgreSQL 7 and 8.2.
> There is a query that I'm not fully understand. It is running with
> 7.3.21 generating two notices about missing FROM's:
> 
> select poid,prodort.bez1 as prodort,sum(anz) as anz,
> art,bez1 from best_dat where type='P' and relid=best_hdr.relid
> and best_hdr.prod_date='20.04.2008' and poid=prodort.id
> group by poid,prodort.bez1,art,bez1
> order by poid,prodort.bez1,poid,art,bez1;
> 
> NOTICE:  Adding missing FROM-clause entry for table "prodort"
> NOTICE:  Adding missing FROM-clause entry for table "best_hdr"

Add prodort and best_hdr to your FROM-clause in your query, this
behavior follows on the sql-spec.



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:

> am  Wed, dem 04.06.2008, um 12:47:41 +0200 mailte Holm Tiffe folgendes:
> > 
> > Hi,
> > 
> > I have to migrate an existing System from PostgreSQL 7 and 8.2.
> > There is a query that I'm not fully understand. It is running with
> > 7.3.21 generating two notices about missing FROM's:
> > 
> > select poid,prodort.bez1 as prodort,sum(anz) as anz,
> > art,bez1 from best_dat where type='P' and relid=best_hdr.relid
> > and best_hdr.prod_date='20.04.2008' and poid=prodort.id
> > group by poid,prodort.bez1,art,bez1
> > order by poid,prodort.bez1,poid,art,bez1;
> > 
> > NOTICE:  Adding missing FROM-clause entry for table "prodort"
> > NOTICE:  Adding missing FROM-clause entry for table "best_hdr"
> 
> Add prodort and best_hdr to your FROM-clause in your query, this
> behavior follows on the sql-spec.
> 
> 
> 
> Andreas
> -- 
> Andreas Kretschmer
> Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net
> 

select poid,prodort.bez1 as prodort,sum(anz) as anz,
art,best_dat.bez1 from best_dat,prodort,best_hdr where type='P'
and best_dat.relid=best_hdr.relid and best_hdr.prod_date='04/20/2008'
and poid=prodort.id group by poid,prodort.bez1,art,best_dat.bez1 order by
poid,prodort.bez1,poid,art,bez1;

I have changed it to the above, can you please take a look over this?

Thanks and thanks to ramasubramanian..

Holm
--      Technik Service u. Handel Tiffe, www.tsht.de, Holm Tiffe,     Freiberger Straße 42, 09600 Oberschöna, USt-Id:
DE253710583www.tsht.de, info@tsht.de, Fax +49 3731 74200, Mobil: 0172 8790 741
 

sathiya moorthy wrote:

> add_missing_from = on
> 
> Simple, just turn on the above option in postgresql.conf ....
> 
> and restart postgres service.

:-)

This fixes all my Problems...
Aregardless of this I've fixed all queries in the meantime.
Thanks guys,

Holm
--      Technik Service u. Handel Tiffe, www.tsht.de, Holm Tiffe,     Freiberger Straße 42, 09600 Oberschöna, USt-Id:
DE253710583www.tsht.de, info@tsht.de, Fax +49 3731 74200, Mobil: 0172 8790 741