![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
![]() |
Mon, Apr 19 2010 3:34 PM | Permanent Link |
Erich Munz | SELECT
k.id_Kunde, k.LfNummer, k.Kurzzeichen, SUM(IF(b.BelegArt IN (3,5) THEN b.Summe ELSE -b.Summe)) as Gesamtumsatz FROM kunden k INNER JOIN (SELECT belegart, lfnummer, id_Beleg, id_Kunde, Summe, Datum FROM belege WHERE belegart IN (3,5,8) AND Datum BETWEEN :datumVON AND :datumBIS) b ON (b.id_Kunde = k.id_Kunde) GROUP BY k.LfNummer ORDER BY Gesamtumsatz DESC, k.Kurzzeichen ------------------------------------------------- above sql worked in 2.03 b9 and fails in 2.03 b11 (returns 0 rows) ================================================================================ SQL Query (Executed by ElevateDB 2.03 Build 11) 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 "k"."id_Kunde" AS "id_Kunde", "k"."LfNummer" AS "LfNummer", "k"."Kurzzeichen" AS "Kurzzeichen", SUM(IF("b"."BelegArt" IN (3, 5), "b"."Summe", - "b"."Summe")) AS "Gesamtumsatz" SUM(IF("b"."BelegArt" IN (3, 5), "b"."Summe", - "b"."Summe") * 1.2) AS "GesamtumsatzInklMwst" FROM "kunden" AS "k" INNER JOIN (SELECT ALL "belegart" AS "belegart", "lfnummer" AS "lfnummer", "id_Beleg" AS "id_Beleg", "id_Kunde" AS "id_Kunde", "Summe" AS "Summe", "Datum" AS "Datum" FROM "belege" WHERE "belegart" IN (3, 5, 8) AND IF("belegart" = 5, "belegnummerverknuepfung", NULL) IS NULL AND "storno" = false AND "Datum" BETWEEN ? AND ?) AS "b" ON ("b"."id_Kunde" = "k"."id_Kunde") GROUP BY "k"."LfNummer" ORDER BY SUM(IF("b"."BelegArt" IN (3, 5), "b"."Summe", - "b"."Summe")) DESC, "k"."Kurzzeichen" Source Tables ------------- kunden (k): 56 rows b1 (b): 0 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Joins ----- The driver table was the kunden (k) table The kunden (k) table was joined to the b1 (b) table with the inner join expression: ("b"."id_Kunde" = "k"."id_Kunde") The optimizer attempted to re-order the joins to a more optimal order The optimizer successfully re-ordered the joins into this more optimal order: The driver table was the b1 (b) table The b1 (b) table was joined to the kunden (k) table with the inner join expression: ("k"."id_Kunde" = "b"."id_Kunde") The following join condition was applied to the kunden (k) table: ("k"."id_Kunde" = "b"."id_Kunde" Index scan (kunden.PrimaryKey)) Result set I/O statistics ------------------------- Total rows visited: 0 Row buffer manager Max buffer size: 32768 Buffer size: 0 Hits: 0 Misses: 0 Hit ratio: 0 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 Index page buffer manager Max buffer size: 65536 Buffer size: 0 Hits: 0 Misses: 0 Hit ratio: 0 Reads: 0 Bytes read: 0 Writes: 0 Bytes written: 0 ================================================================================ 0 row(s) returned in 0 secs ================================================================================ changing date values to strings works e.g.: ....Datum BETWEEN DATE '2010-01-01' AND DATE '2010-04-30'.... Erich |
Tue, Apr 20 2010 7:44 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Erich,
<< above sql worked in 2.03 b9 and fails in 2.03 b11 (returns 0 rows) >> I'll check it out and see what the issue is. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 20 2010 8:37 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Erich,
I think I'm going to need your database catalog and table files for this query. I tried this query with 2.03 B11 and it works fine: SELECT * FROM (SELECT CustNo, Company, State FROM customer WHERE State BETWEEN :State1 AND :State2) customertable The equivalent operations being: 1) Parameters in a derived table 2) BETWEEN operator -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 20 2010 9:54 AM | Permanent Link |
Erich Munz | It seems that only DATE Params (Types) are affected, if have sent you a demo database per email
--------------------------------------------- "Tim Young [Elevate Software]" wrote: Erich, I think I'm going to need your database catalog and table files for this query. I tried this query with 2.03 B11 and it works fine: SELECT * FROM (SELECT CustNo, Company, State FROM customer WHERE State BETWEEN :State1 AND :State2) customertable The equivalent operations being: 1) Parameters in a derived table 2) BETWEEN operator -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Apr 21 2010 1:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Erich,
<< It seems that only DATE Params (Types) are affected, if have sent you a demo database per email >> Thanks, I should have an answer for you soon - I got sidetracked over the last couple of days wrestling with Visual Studio on Windows 7 x64 (ugh). -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Apr 27 2010 2:12 AM | Permanent Link |
Erich Munz | Fixed in version 2.03 build 12
http://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.03&incident=3188 Thank you! |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |