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?
Von: [email protected] [mailto:[email protected]]
Gesendet: Dienstag, 8. März 2016 16:26
An: [email protected]
Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2?
On 2016-03-08 16:18, 'Checkmail' [email protected]
<mailto:[email protected]>
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and
> get
> the real value of m (exists in tlager_sum)
>
> 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
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
> a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?
It is not clear to me what your problem is. Could you describe it in
more detail and provide a sample dataset, expected results and actual
results?
Mark
