At 05:47 PM 11/8/00 -0600, Luis Magaña wrote:
>insert into employee(title,first_name,start_date,charge) values('Mr.
X','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Y','Smith',date(now()),'None');
>insert into employee(title,first_name,start_date,charge) values('Mr.
Z','Smith',date(now()),'None');
>
>so far there is no problem at all, the problem comes here:
>
>select title || ' ' || first_name || ' ' || last_name as fullname from
employee;
>
> fullname
>----------------
>
>
>
> (3 rows)
>
>Doesn't work !!!!, I'm thinking it is because of the null value in last_name.
Right. NULL means "has no value", it's not the empty string. The result of
concatenating with NULL is NULL.
> Have any idea or suggestion on how to workaround this situation.
It's a classic NULL issue. 1+NULL = NULL, too, for instance. Try
something like
"default ''" in your table definition rather than use null. Then you'll be
using
the empty string for concatenation. 'abc' || NULL = NULL. 'abc' || '' =
'abc'
which appears to be what you want.
This is standard SQL92 behavior...
- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.
