Using SELECT inside COALESCE

Question

How do I correct the following SQL code, specifically the COALESCE part?

insert into Stmt G (ID,blah,foo)
select
coalesce(SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER, select StmtSeq.nextval from dual),
c.blah,
d.foo
from claim c
left join d on ...;

I'm taking the ID from the Stmt table itself if the ClaimNo matches, otherwise creating a new one. Is this not allowed in SQL? How else can I write this statement?

I'm getting a "Missing Expression" error on the coalesce part right now.

Answer 1

You should place parenthesis around the selects:

coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER)
        , (select StmtSeq.nextval from dual)
        )