Re: If test in sql??

Thank you very much. Fine! Just two little questiton s more:

1) Can COALESCE take more than two alternatives?

2) Does it treat a zero length string as NULL or NOT NULL?

Esa Pikkarainen

Oliver Elphick wrote (24 Jan 01,):
> "Esa Pikkarainen" wrote:
> >This is maybe a novice question, but after all...
> >Say, I have a discussion forum application. There is an expiration
> >value for a whole board; then there are individual values for every
> >conference. If this individual value is NULL then the general value
> >should be used. How can I test this situation in a query (to
> >Postgresql):
> >I mean something like:
> >
> > Select (IF conf.expire NOTNULL conf.expire ELSE gen_expire)
> > as expire, ...
> >
> >Where is this in manuals if it is?
>
> SELECT COALESCE(expire, gen_expire) AS expire FROM conf;
>
> COALESCE takes the first non-null value from its list of parameters, like
> this:
>
> junk=# select * from junk where name is null;
> id | name
> ----+------
> 3 |
> (1 row)
>
> junk=# select id, coalesce(name,'no-name') from junk;
> id | case
> ----+---------
> 1 | Fred
> 2 | George
> 4 | sdf%df
> 3 | no-name
> (4 rows)
>
> --
> Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
> Isle of Wight http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "If anyone has material possessions and sees his
> brother in need but has no pity on him, how can the
> love of God be in him?"
> I John 3:17
>
>