Oliver,
I posted a reply to this on firebird-devel.

You should be using the function SNVL(), not SNULLIF(). Your test case is
correctly returning nulls, as designed. If you want Oracle NVL()
behaviour, use SVL().

heLen

At 07:43 AM 12-06-02 +0200, you wrote:

>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
>
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________