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.Elphick@lfix.co.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
>
>