Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread error in sql select
Fri, Feb 16 2007 5:21 PMPermanent Link

jean bernard
hello
i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB.
then i do this selectFrownlan 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jean,

<< i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB. then i do
this selectFrownlan 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 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Jean,

<< i have dowloaded EDB1.0 today and i tranfer dbisam4 to EDB. then i do
this selectFrownlan 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

Image