Re: [Hsqldb-developers] Concatenating fields

[Nitin U. <nit...@gm...>]

Thanks John:

IFNULL works fine with non DATES but for dates I have to do
IFNULL(TO_CHAR(date_field,'MM/DD/YYYY','')

Do you know why?

However it works great for expressions like:

IFNULL('-'||field4,'')
Here I am trying to add the 4 digit zip code extension for US addresses and
when some of them do not have this field4, the dangling "-" is ommited!

Do you know why?


On 6/22/06, John Yeary <joh...@gm...> wrote:
>
>  Try using the following function.
>
> IFNULL(exp,value)
>
> if exp is null, value is returned else exp) - use COALESCE() instead
>
>
>  On 6/22/06, Nitin Uchil <nit...@gm...> wrote:
>
> >  If I concatenate 2 fields:
> >
> > SELECT field1||'-'||field2 FROM table1,
> >
> > I get NULL if one of the fields is NULL.
> >
> > Is this normal? How can I blank NULL fields on SELECT so that on
> > concatenation, I see the ones that are defined?
> >
> > I cannot do this in the database because some of them are foreign unique
> > keys
> >
> >
> >
> > Using Tomcat but need to do more? Need to support web services,
> > security?
> > Get stuff done quickly with pre-integrated technology to make your job
> > easier
> > Download IBM WebSphere Application Server v.1.0.1 based on Apache
> > Geronimo
> > http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> >
> >
> >
> > _______________________________________________
> > hsqldb-developers mailing list
> > hsq...@li...
> > https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
> >
> >
> >
>
>
> --
> John Yeary
>
> --
> "If I have seen further, it is because I have stood on the shoulders of
> giants..."  Sir Isaac Newton
>
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job
> easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
>
>
> _______________________________________________
> hsqldb-developers mailing list
> hsq...@li...
> https://lists.sourceforge.net/lists/listinfo/hsqldb-developers
>
>
>