Helen, I'm sorry but even the snvl function does not work, and I think snvl
and snullif share the exactly same code.

I took the time to provide you a test case just try it and tell me if you
encounter the same problem.

Do you know in which newsgroup should I post those kind of pb to get a
chance to see it fixed ?

Thank's in advance.

Olivier STEINBERG

"Helen Borrie" <helebor@...> a �crit dans le message de news:
5.1.0.14.2.20020612163949.03a09720@......

> 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 7 http://firebirdsql.org 7
> http://users.tpg.com.au/helebor/
> _______________________________________________________
>
>
>
> 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/
>
>
>