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

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

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

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

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

Fixed in version 2.03 build 12

http://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.03&incident=3188

Thank you!