On 23 Jan 2002, at 9:29, Galante Hernan (SFA) 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? In other
> databases the result is Ab.Surname || ', '. This is a bug? Can I
> resolve this? How?
>

Interbase is correct here, databases that act otherwise are making
an assumption, that NULL means empty, and it doesn't, it means
unknown. For example, say you take your wife to a 5 star hotel for
a romantic weekend, when you arrive the hotel clerk puts in your
date of arrival, but the date of departure is unknown, they may
know you plan on leaving on the Sunday, but they can't really
known, suppose your car doesn't start, or you get really bad
weather. So your date of departure is unknown or NULL.

So with your names, if Name is unknown then combining a known
with an unknown gives you an unknown. Now I hate when
programs do this, list of female rock musicians:

Benatar, Pat
Madonna,
Easton, Sheena
Twain, Shannia

It looks ugly, now the null gives you an advantage you could code
the following in a stored procedure:

(NAME being the output parameter)

SELECT AB.NAME, AB.SURNAME FROM ABOGADO AB INTO :
:AB_NAME, :AB_SURNAME;

IF AB_NAME IS NULL THEN NAME = AB_SURNAME
ELSE NAME = AB_SURNAME || ', ' || AB_NAME

Benatar, Pat
Madonna
Easton, Sheena
Twain, Shannia

If there is a chance that SURNAME could be null, you could check
for that as well.

You really want to return TWO values, the concatenated name, and
the key for the table. Then you simply JOIN the procedure to the
other tables in your SQL statement.

Paul

Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com