Hello, it's the third time I post this problem on varrious IB list and
nobody seems to be able to give me a clue, maybe that's the limit of
opensource dev.
I make a classic left join query to search all rows in table A that are not
in table B and for thoese rows in table B that are not in table A, I
substitute the null values with
the snullif function (oracle nvl equivalent) a constant string. But this
doesn't work in Firebird so as it works with Oracle or SQL Server.

Here is a test case to reproduce the problem.


CREATE TABLE TABLE_A(
COL_A VARCHAR(10),
COL_B VARCHAR(10))

CREATE TABLE TABLE_B(
COL_C VARCHAR(10),
COL_B VARCHAR(10))

INSERT INTO TABLE_A ( COL_A, COL_B)
VALUES('A', '1');
INSERT INTO TABLE_A ( COL_A, COL_B)
VALUES('B', '2');
INSERT INTO TABLE_A ( COL_A, COL_B)
VALUES('C', '3');
INSERT INTO TABLE_A ( COL_A, COL_B)
VALUES('D', '4');
INSERT INTO TABLE_A ( COL_A, COL_B)
VALUES('E', '5');

INSERT INTO TABLE_B ( COL_C, COL_B)
VALUES('AA', '1');
INSERT INTO TABLE_B ( COL_C, COL_B)
VALUES('BB', '2');

SELECT TABLE_A.COL_A, TABLE_A.COL_B, SNULLIF(TABLE_B.COL_B, 'not empty')
NULL_COL
FROM TABLE_A
LEFT JOIN TABLE_B ON (TABLE_B.COL_B = TABLE_A.COL_B)
WHERE TABLE_B.COL_B IS NULL

On should see in the TABLE_B.COL_B column, the 'not empty' string ?

but with this query it gives null values.
Have someone any idea of the problem ?

Thank's in advance.

Olivier STEINBERG