Hello,
I have created an example:
SET SQL DIALECT 3;
CREATE TABLE T_MAIN (
TNR VARCHAR(16) NOT NULL,
MINB INTEGER
);
ALTER TABLE T_MAIN ADD CONSTRAINT PK_T_MAIN PRIMARY KEY (TNR);
SET SQL DIALECT 3;
CREATE TABLE T_STOCK (
TNR VARCHAR(16),
AMOUNT INTEGER
);
ALTER TABLE T_STOCK ADD CONSTRAINT FK_T_STOCK_1 FOREIGN KEY (TNR) REFERENCES
T_MAIN (TNR) ON DELETE CASCADE ON UPDATE CASCADE;
Redords:
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('AAA', 0);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('BBB', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('CCC', 10);
INSERT INTO T_MAIN (TNR, MINB)
VALUES ('DDD', 10);
COMMIT WORK;
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('AAA', 100);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('BBB', 5);
INSERT INTO T_STOCK (TNR, AMOUNT)
VALUES ('CCC', 15);
COMMIT WORK;
The sql query
SELECT
a.tnr, a.minb, coalesce(b.amount,0) as m
FROM
t_main a
left join t_stock b on (a.tnr = b.tnr)
WHERE
((a.minb > coalesce(b.amount,0)))
ORDER BY
a.tnr
_________________________________-
I get ccc too. Why? Allthough I have a stock from 15.
If I delete the foreign key from table t_stock, CCC does not display! But with
this foreign key it does not work, I get CCC but CCC has a stock greater the
minimum amount (minb) and should not be displayed (like in firebird 2.5, 2.1,
2.0)
Thanks
Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Gesendet: Mittwoch, 9. März 2016 13:25
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?
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.
Please, give some DDL with example data.
I've tried to reproduce your issue on FB3.0 RC2, but i could not reproduce
it.
Kind Regards,
Arno Brinkman
