At 03:56 PM 12/08/2005 +1000, you wrote:

> > HI All
> >
> > How can I get a row if a field is NULL in the following SELECT.
> >
> > select
> > receivables.FIRST_NAME + ' ' + receivables.LAST_NAME as GUEST_NAME
> > from
> > receivables
> > where
> > last_name like 'SHIVAMB%'
> >
> >
> > If FIRST_NAME or LAST_NAME is NULL nothing gets returned. Is there a
> > way of specifying that if FIRST_NAME is NULL to return an empty string
> > in one SELECT and concatenating the fields.
> >
>
>COALSECE(FIRST_NAME, '')

C O A L E S C E !!


...and the SQL concatenator is ||, not +. So --

select
COALESCE(receivables.FIRST_NAME,'')
|| ' '
|| COALESCE(receivables.LAST_NAME, '') as GUEST_NAME
from receivables
where last_name starting with 'SHIVAMB'

./hb