Icon View Incident Report

Serious Serious
Reported By: Tim Young
Reported On: 10/29/2009
For: Version 2.03 Build 4
# 3092 RIGHT OUTER JOINs Not Working Properly When Not Referring to Immediately Prior Table

This doesn't work in elevatedb, and raises :

ElevateDB Error #700 An error was found in the statement at line 6 and column 18 (Invalid expression "SD_PERS" AS "P" found, this table is the target of multiple join conditions)

SELECT FA.FIRMENNUMMER,FA.ABTEILUNGSNUMMER,F.NAME1,
A.BEZEICHNUNG, A.NUMMER AS ABTNR,
A.Leiter, P.ID , F.NUMMER AS FIRMENNR , P.ABTEILUNG,
P.NAME, P.VORNAME, P.NUMMER as PNUMMER, P.NAME,
P.KARTENNR , P.EINTRITTSDATUM, P.AUSTRITTSDATUM, P.KOSTENSTELLE1, 
F.ORT, P.ISTANWESEND 
FROM  FA_ABT FA
 LEFT OUTER JOIN SD_PERS P 
   ON ((P.FIRMENNR = FA.FIRMENNUMMER) AND
      (P.ABTEILUNG = FA.ABTEILUNGSNUMMER))
 RIGHT OUTER JOIN SD_FIRM F 
   ON ( FA.FIRMENNUMMER = F.NUMMER ) 
 LEFT OUTER JOIN SD_ABT A 
   ON ( FA.ABTEILUNGSNUMMER = A.NUMMER ) 
ORDER BY F.NAME1, A.BEZEICHNUNG, P.NAME,
  P.VORNAME, P.NUMMER , P.ID



Comments Comments and Workarounds
The problem was that EDB expected any RIGHT OUTER JOINs to be between the prior table and the current table. The workaround was to use this instead for the joins:

FROM FA_ABT FA
RIGHT OUTER JOIN SD_FIRM F
ON ( FA.FIRMENNUMMER = F.NUMMER )
LEFT OUTER JOIN SD_PERS P
ON ((P.FIRMENNR = FA.FIRMENNUMMER) AND
(P.ABTEILUNG = FA.ABTEILUNGSNUMMER))
LEFT OUTER JOIN SD_ABT A
ON ( FA.ABTEILUNGSNUMMER = A.NUMMER )
ORDER BY F.NAME1, A.BEZEICHNUNG, P.NAME,
P.VORNAME, P.NUMMER , P.ID


Resolution Resolution
Fixed Problem on 10/30/2009 in version 2.03 build 5


Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial

Image