Hell Mark,

I would get all material, where the minimum inventory amount iss maller the the inventory amount.

Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum amount it should be.

Now, the simplified query gets all records where the amount is to small and with the „where a.minb > coalesce(b.menge)) I get also the records, where is no inventory amount present.

If I let the code:

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

I get m = 0 for records, who has a inventory amount greater than the minimal inventory amount. If I change the

where ((a.minb > coalesce(b.menge,0))

to

where ((a.minb > b.menge)..

the records who has a greater inventory amount than the minimal one will be not displayed, but also not these who has no inventory amount (null in tlager_sum)

In firebird 2.5 and earlier versions it works fine in both situations.

The result of the unchanged code:

TEILENR    BEZEICHNUNG    MINB    M    TYP

PrSt110x44x3       Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm    280   0       1

Inventory amount = 0

The result without coalesce:

The record is no present

Without the condition >..

TEILENR    BEZEICHNUNG    MINB          M       TYP

PrSt110x44x3       Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm    280   420   1

I get 420 as inventory amount, 280 should it be as minimal inventory amount, aka minb

What can be the problem?