Thank's for spending some time on this.
What I expect is to get a function that mimics ths oracle nvl function which
is simple : if a column of a table is null and you select it with the nvl or
snvl function, it returns
the replacement sting.

For instance, this query on firebird works fine and returns as expected the
sting 'empty' after I inserted a row in this table with a null value.
So here the mimic of oracle nvl value is goood

select snvl(COL_B,'empty')
from TABLE_A
where COL_B is null

Result :

SNVL
empty

So simply stated, what I would expect from the above query, is that as the
TABLE_B.COL_B is null, the function SNVL should return the 'not empty'
string for those null
rows I select. That's all.

SELECT TABLE_A.COL_A,
TABLE_A.COL_B,
SNVL(TABLE_B.COL_B, 'not empty') as 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

So as this function works for a simple query on a table, why doesn't it
works for my left join query ?

Thank's anyway

Olivier STEINBERG


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

> At 10:25 AM 12-06-02 +0200, you wrote:
> >Helen, I'm sorry but even the snvl function does not work, and I think

snvl

> >and snullif share the exactly same code.
>
> When used appropriately as UDFs, both functions work as designed and quite
> clearly do not share the same code.
>
>
> >I took the time to provide you a test case just try it and tell me if you
> >encounter the same problem.
>
> I know (from private email earlier today) that you thought so. I told you
> it didn't ring a bell - meaning I had no record of it. So I'm working on
> what you posted in the lists - where (intended or not) you appeared to
> expect SNULLIF to work like NVL in Oracle...
>
> However, to move on: here's the SNULLIF statement:
>
> SELECT TABLE_A.COL_A,
> TABLE_A.COL_B,
> SNULLIF(TABLE_B.COL_B, 'not empty') as 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
>
> If I now correctly understand you, you are expecting the UDFs SNULLIF and
> SNVL to have the same scope as the WHERE clause - hence you are expecting
> that, if the query selects the *output* rows where TABLE_B.COL_B IS NULL,
> then it should produce the string 'not empty' in the NUL_COL column,
> because this NULL in the output row is "not equal to the string 'not

empty'

> " - is that right?
>
> There are two ways this can't work.
> -- First, NULL is not a value, so a comparison of NULL and 'not empty'

will

> never result in an evaluation of "not equal", but always NULL.
> -- However, it doesn't arise here, anyway. The UDF operates on the

*input*

> value, not the output value.
> --------In the case of SNULLIF, the first parameter passed does not equal
> the value supplied ('not empty') so the function passes back the same

value

> that was passed.
> --------In the case of SNVL, the first parameter is not null, so it is
> passed back as the return value.
>
> I don't have Oracle installed on my systems currently so I can't test the
> scope of its native NVL function to see whether it gets applied to the
> output...but, AFAIK, there is no way to force a UDF to be applied to
> ungrouped output. In the case where GROUP BY output is grouped by UDF -
> which is possible in Firebird 1 though starved of examples - the logic is
> different, with the UDF actually being applied to a value in a set which

is

> already formed.
>
> >Do you know in which newsgroup should I post those kind of pb to get a
> >chance to see it fixed ?
>
> Hmmm, either here or Firebird-devel, on a day when Claudio is reading list
> mail.
>
> As to getting it "fixed", I think it would first have to be established
> that it was wrong. The fact that it might not be interchangeable with a
> similar function in Oracle doesn't make it "wrong". It's not a standard
> SQL function, so there's really nothing to predicate how it *ought* to

behave.

>
> regards,
> Helen
>
>
> 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/
>
>
>