Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Multiple joins |
Thu, Oct 29 2009 6:08 AM | Permanent Link |
QuickAndDirty | 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? |
Thu, Oct 29 2009 6:12 AM | Permanent Link |
QuickAndDirty | or is it an "AS" missing? hm ?
|
Thu, Oct 29 2009 6:45 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Thu, Oct 29 2009 7:16 AM | Permanent Link |
QuickAndDirty | <<
"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 |
Fri, Oct 30 2009 5:03 AM | Permanent Link |
QuickAndDirty | "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. Attachments: EDBDatabase.zip |
Fri, Oct 30 2009 8:46 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Fri, Oct 30 2009 8:46 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | |
Fri, Oct 30 2009 9:26 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Nov 2 2009 4:00 AM | Permanent Link |
QuickAndDirty | "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 |
Mon, Nov 2 2009 10:18 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |