FULL JOIN is only supported with merge-joinable join conditions

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: FULL JOIN is only supported with merge-joinable join conditions
Date: 2007-05-17 14:28:01
Message-ID: f2hoq7$sce$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I try to port application to PostgreSQL 8.1+

The following query runs OK in VFP but causes error in Postgres

FULL JOIN is only supported with merge-joinable join conditions

How to fix ?

Andrus.

SELECT
ametikoh.Nimetus as ametikoht,
Isik.nimi,
Isik.eesnimi,
koosseis.kogus,
COALESCE( iandmed.Kuluobj, koosseis.objekt1) as osakond

FROM iandmed
FULL JOIN koosseis ON iandmed.ametikoht=koosseis.ametikoht
AND iandmed.Kuluobj= koosseis.objekt1
AND iandmed.AmetiKoht is not null
JOIN ametikoh ON COALESCE(iandmed.ametikoht,koosseis.ametikoht)=
ametikoh.AmetiKoht
LEFT JOIN isik ON iandmed.isik=isik.isik
WHERE true
ORDER BY 1,2

Revelant pars of table structures are:

CREATE TABLE iandmed
(
reanr integer NOT NULL DEFAULT nextval('iandmed_reanr_seq'::regclass),
isik character(10) NOT NULL,
miskuup date,
plopp date,
summavrt numeric(12,2),
kuluobj character(10),
ametikoht numeric(7),
CONSTRAINT iandmed_pkey PRIMARY KEY (reanr)
) ;

CREATE TABLE koosseis
(
id numeric(7) NOT NULL,
ametikoht numeric(7) NOT NULL,
objekt1 character(10) NOT NULL,
kogus numeric(4) NOT NULL DEFAULT 0,
algus date,
lopp date,
CONSTRAINT koosseis_pkey PRIMARY KEY (id)
);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dave Page 2007-05-17 14:32:33 Re: Paypal and "going root"
Previous Message Richard Huxton 2007-05-17 14:23:28 Re: Paypal and "going root"