Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 3 of 3 total |
error in sql select |
Fri, Feb 16 2007 5:21 PM | Permanent Link |
jean bernard | hello
i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB. then i do this selectlan in EDB) ================================================================================ SQL Query (Executed by ElevateDB 1.00 Build 1) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ================================================================================ SELECT ALL "CLIENTS"."NO_CLIENT" AS "NO_CLIENT", "CLIENTS"."NOM" AS "NOM", "FACTURES"."ANNEE" AS "ANNEE", SUM("LIGNFACT"."Q_COM") AS "TOTAL", "VARIET"."TIPE" AS "TIPE", "CLIENTS"."COMMISSION" AS "COMMISSION", "CLIENTS"."CODE_POSTAL" AS "CODE_POSTAL" FROM "CLIENTS" INNER JOIN "FACTURES" ON ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") INNER JOIN "LIGNFACT" ON ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") INNER JOIN "VARIET" ON ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR"), "ANNEE" WHERE ("CLIENTS"."REPRESENTANT" IN (2, 2)) WHERE ("factures"."tipe" = 'FACTURE') WHERE ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) GROUP BY 1, 2, 3, 5, 6, 7 NOJOINOPTIMIZE Source Tables ------------- CLIENTS: 2891 rows FACTURES: 16818 rows LIGNFACT: 330358 rows VARIET: 579 rows ANNEE: 1 rows Result Set ---------- The result set was static The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CLIENTS table: ("CLIENTS"."REPRESENTANT" IN (2, 2) [Row scan: 2891 rows, 2659720 bytes estimated cost]) The following filter condition was applied to the FACTURES table: ("factures"."tipe" = 'FACTURE' [Index scan: 16505 keys, 262144 bytes estimated cost]) The following filter condition was applied to the result set rows as they were generated: ("FACTURES"."ANNEE" >= "ANNEE"."ANNEE" - 4) Joins ----- The driver table was the CLIENTS table The CLIENTS table was joined to the FACTURES table with the inner join expression: ("CLIENTS"."NO_CLIENT" = "FACTURES"."NO_CLIENT") The FACTURES table was joined to the LIGNFACT table with the inner join expression: ("FACTURES"."NO_LIGNE" = "LIGNFACT"."NOCOM") The LIGNFACT table was joined to the VARIET table with the inner join expression: ("LIGNFACT"."CODEVAR" = "VARIET"."CODEVAR") The NOJOINOPTIMIZE clause was used and the optimizer left the joins in their declared order The following join condition was applied to the FACTURES table: ("FACTURES"."NO_CLIENT" = "CLIENTS"."NO_CLIENT" [Index scan]) The following join condition was applied to the LIGNFACT table: ("LIGNFACT"."NOCOM" = "FACTURES"."NO_LIGNE" [Index scan]) The following join condition was applied to the VARIET table: ("VARIET"."CODEVAR" = "LIGNFACT"."CODEVAR" [Index scan]) ================================================================================ 1 row(s) returned in 10.609 secs ================================================================================ with firebird or dbisam4, 3535 rows returned. EDB TIME : 10.6 seconds dbisam4 : 4.62 s firebird : 0.85 s |
Mon, Feb 19 2007 6:18 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jean,
<< i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB. then i do this selectlan in EDB) >> Could you send me the DBISAM 4 tables that you're using ? -- Tim Young Elevate Software www.elevatesoft.com |
Mon, Feb 19 2007 7:11 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jean,
<< i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB. then i do this selectlan in EDB) >> Never mind about the tables. The issue is the GROUP BY: > GROUP BY 1, 2, 3, 5, 6, 7 ElevateDB requires the actual column references or expressions and cannot use ordinal values for SELECT column positions like DBISAM did. This was left out of the migration guide, and I will make sure that it is updated immediately. The reason for this is that the SQL standards body is phasing out support for this type of ordering or grouping. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Friday, March 29, 2024 at 03:30 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |