At 07:34 PM 27-04-02 -0300, Daniel Rail wrote:

>At 04/27/2002 05:53 PM, RRV wrote:
> >I.NOME||'. '||TC.DESCRICAO||'. '||C.DESCRICAO AS CONTA
> >
> >When the field C.DESCRICAO is Null, my result CONTA is null to.
> >Anyone can help-me? If any field of this concatenation will null, I don't
> >want that the result was null.
>
>This is a normal behavior for Interbase or Firebird as per SQL-92
>standard. I use a stored procedure to concatenate fields, when I know that
>they are the same fields all the time.
>
>If you don't want to use stored procedures, then I would suggest that you
>use calculated fields to concatenate the strings. The calculated fields
>are handled by the application, by the event OnCalcFields.
>
>These are two solutions that can be used.

Another is the sNvl() function in the FBUDF library. It works similarly to
NVL() in Oracle.
sNvl(I.NOME, '')||'. '||sNvl(TC.DESCRICAO, '')||'. '||sNvl(C.DESCRICAO, '')
AS CONTA

A problem with this approach is that, if all the inputs are null, you will
get a string like '..'
A single DSQL statement won't fix this, a stored procedure can.


regards,
Helen Borrie (TeamIBO Support)

** Please don't email your support questions privately **
Ask on the list and everyone benefits
Don't forget the IB Objects online FAQ - link from any page at
www.ibobjects.com