Skip site navigation (1) Skip section navigation (2)

Problems with a Query between 7.3 and 8.2

From: Holm Tiffe <holm(at)freibergnet(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Problems with a Query between 7.3 and 8.2
Date: 2008-06-04 10:47:41
Message-ID: 20080604104741.GA82113@pegasus.freiberg-net.de (view raw or flat)
Thread:
Lists: pgsql-sql
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 null
 type    | character(1)  | 
 bpid    | integer       | not null
 bpcode  | character(3)  | 
 poid    | integer       | 
 grpid   | integer       | not null
 art     | integer       | 
 bez1    | character(32) | 
 anz     | integer       | 
 portion | character(1)  | 
 fp      | integer       | default 0
 wertig  | 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: DE253710583
  www.tsht.de, info(at)tsht(dot)de, Fax +49 3731 74200, Mobil: 0172 8790 741


Responses

pgsql-sql by date

Next:From: A. KretschmerDate: 2008-06-04 11:35:50
Subject: Re: Problems with a Query between 7.3 and 8.2
Previous:From: Gary StainburnDate: 2008-06-04 10:06:02
Subject: Re: SOLVED - months, intervals and integers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group