Re: replace null with 0 in subselect ?

On Wednesday 16 October 2002 12:07, Albrecht Berger wrote:
> Hello,
> I have a statement like this :
>
> INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);
>
> This works fine if the subselect returns a value, but if it returns
> null there is a problem. In this case a 0 has to be inserted.
> Is there any pg function which solves this problem ?
> I know that oracle has a function but I didn't find
> something similar in pg.

A quick and dirty solution (untested):

INSERT INTO tab1 (c1, c2, c3)
VALUES (1,2,
(SELECT CASE WHEN (SELECT MAX(pos)+1 ) IS NULL THEN 0
ELSE (SELECT MAX(pos)+1 )
END)
)

Ian Barwick
barwick(at)gmx(dot)net