Hello,
your are right. But in FB 3, it will be handled different. I need the left join tlager_sum, because I’d like to have all records too there no records in tlager_sum. But in this case I get now in fb 3 the tteile.teilenr clean without the amaount of tlager_sum (the a.minb is greater than the coalesce(b.menge,0) and in this case, I get no referenced Record from tlager_sum), the one from tteile now in every case (left join). Finally, I would get all records with a.minb > b.menge and all records, where a.minb is present an there is no record from the teilenr in tlager_sum. Thanks Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Mittwoch, 9. März 2016 09:04 An: firebird-support@yahoogroups.com Betreff: Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2? Hello, > sorry, outlook corrects me in German. At the moment, I have no sulution > for the problem. Anything ideas? Query: 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 Result: TEILENR BEZEICHNUNG MINB M TYP PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm 280 420 1 I would say your TLAGER_SUM contains a record with: TEILENR MENGE PrSt110x44x3 420 So results are correct? If not give sample records for tteile and tlager_sum with DDL Kind Regards, Arno Brinkman ABVisie
