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@gmx.net