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