Re: SQL-Question: Error 393
>From: Richard Spitz <richard.spitz@ana.med.uni-muenchen.de>
>Date: Mon, 25 Aug 1997 16:53:11 +0200
>X-Informix-List-Id: <news.42073>
>
>Dear SQL-Gurus,
>
>somehow I cannot get my act together with the following query:
I reformatted the SQL to make it easier for me to read:
SELECT teamnr[1], bezeich
FROM team
WHERE teamnr MATCHES "?000"
INTO TEMP ort;
SELECT teamnr[1,3], bezeich
FROM team
WHERE teamnr MATCHES "*0"
AND teamnr NOT MATCHES "?00?"
INTO TEMP klinik;
SELECT team.druck,
nverteil.folge,
nverteil.tage,
team.bezeich tbezeich,
klinik.bezeich kbezeich,
ort.bezeich obezeich,
personal.vollname,
team.zahl
FROM team , OUTER nverteil, personal, OUTER klinik, ort
WHERE team.druck BETWEEN 0 AND 249
AND team.teamnr = nverteil.teamnr
AND nverteil.persnr = personal.persnr
AND nverteil.teamnr = ort.teamnr[1]
AND nverteil.teamnr = klinik.teamnr[1,3]
AND nverteil.woche = "29.09.1997"
ORDER BY druck, kbezeich, folge
>This query gives me SQL error -393:
>"A condition in the where clause results in a two-sided outer join.".
And it's correct.
>What is a "two-sided outer join"? Can a SQL-Guru tell me which condition
>is the offending one?
The problem join is the one between nverteil.teamnr and klinik.teamnr.
These tables are both tagged with the OUTER keyword and cannot therefore
be joined to each other.
You should probably use "team.teamnr = klinik.teamnr".
I think the subscripting on klinik.teamnr is unnecessary since the
klinik table only holds the first three characters of the teamnr.
You might need to use subscripting on team.teamnr to cut that down to
3 characters.
>This happens with Online 5.07 as backend, in case that makes any
>difference.
No; this is neither version nor product specific, but thanks for including
the information...
Yours,
Jonathan Leffler (johnl@informix.com) #include <witticism.h>