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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

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