This doesn't work in elevatedb

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

it 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)

What's wrong in doing so and How should I do better?

or is it an "AS" missing? hm ?

<< it 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)

What's wrong in doing so and How should I do better? >>

Can you send me the database catalog that you're using (edbdatabase.edbcat)
?  I don't need the table files, just the catalog, and please indicate if it
is ANSI or Unicode.

I'll check it out and see what the issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com

<<
"Tim Young [Elevate Software]" wrote:

Can you send me the database catalog that you're using (edbdatabase.edbcat)
?  I don't need the table files, just the catalog, and please indicate if it
is ANSI or Unicode.

I'll check it out and see what the issue is.
>>
Where should I "SEND" it ?
The database is only a TEST database that I migrated...so there is only one entry, but it
works in Paradox and MSExpress, so I think it is something with the SQL2003 Standard again ???

And it seems the RIGHT JOIN is the problem...

Ansi means 32 Bit and Unicode is the 64 Bit version, is it? Then it is ANSI but I Collate DEU

"Tim Young [Elevate Software]" wrote:


<< it 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)

What's wrong in doing so and How should I do better? >>

Can you send me the database catalog that you're using (edbdatabase.edbcat)
?  I don't need the table files, just the catalog, and please indicate if it
is ANSI or Unicode.

I'll check it out and see what the issue is.

--
Tim Young
Elevate Software
www.elevatesoft.com
__________________________________________________

OK,
it should be atteched to this Post.

<< Where should I "SEND" it ? >>

Sorry for not being clear, email me the database at timyoung@elevatesoft.com

<< The database is only a TEST database that I migrated...so there is only
one entry, but it works in Paradox and MSExpress, so I think it is something
with the SQL2003 Standard again ??? >>

More than likely it's just a limitation of EDB.  There are certain rare join
combinations that it cannot handle, most notably when you combine the same
table in a left and right outer join.

<< Ansi means 32 Bit and Unicode is the 64 Bit version, is it? Then it is
ANSI but I Collate DEU >>

ANSI means ANSI/MBCS strings, and Unicode means Unicode strings.  There are
two versions of ElevateDB, one for 100% ANSI and one for 100% Unicode.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks, I'll check it out.

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes, the issue is with the joins.  I'll see if I can provide a fix for this,
but for now, the following will correct the problem:

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 )

Just move the ROJ up before the first LOJ.

--
Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:
<<Yes, the issue is with the joins.  I'll see if I can provide a fix for this, >>
Thank you, checking the Issue. I hope it is fixable.


"Tim Young [Elevate Software]" wrote:
<<
but for now, the following will correct the problem:
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 )

Just move the ROJ up before the first LOJ.
>>
I figured this out, too. In this case it is no Problem,  because we already have
alternative SQLs at this Function (one for paradox and mssql and one for the old oracle
standard with the (+)=  and =(+) joins) .....


Sincerely

Andreas Ramalhao
Andreas

<< Thank you, checking the Issue. I hope it is fixable. >>

Yep, it's fixed for 2.03 B5:

http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.03&type=f&incident=3092

--
Tim Young
Elevate Software
www.elevatesoft.com