At 09:29 AM 23-01-02 -0300, you wrote:

>Hi everybody,
>
> I'm in a problem. I have Interbase 6/NT and I'm trying to do
>this:
>
> Select Ab.Surname || ', ' || Ab.Name as Name
> .....
> from Abogado Ab,
> ......
> where ..............
>
> The problem is Ab.Name in some cases is null .... and for my
>surprise all the operation of concatenating is null. Why?

Because NULL is a state, not a value, so the result of the expression will be NULL.

>In other databases the result is Ab.Surname || ', '.

This is so in all RDBMSs I have used where NULL is supported. (Some, e.g. Paradox, does not store NULL, but placeholder zero values. So "null" strings are not null, but ascii 0, or "empty string".)

>This is a bug?

No, it is "as designed".

>Can I resolve this? How?

It depends on what you need to do. If you want to continue to permit null in these columns, then you will need to handle each case individually - using a stored procedure, usually, to output a dataset containing the concatenation as you want it; using triggers if you are wanting to store the result of concatenation.

The sensible thing to do, if you have a requirement to perform calculations on strings, is to write before update and before insert triggers to intercept null and replace it with empty string.

helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________