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.

Which specific part of the query do you receive the error on? (Paste the exact error message.) — cdhowie, Aug 14 '14 at 15:27

score 56 · Accepted Answer · answered Aug 14 '14 at 15:29

56

You should place parenthesis around the selects:

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

answered Aug 14 '14 at 15:29

Patrick Hofman

153,850
22
249
325

1

Please note, it's critical to have the space between the braces after coalesce. – Bryn Feb 24 '21 at 13:00

Using SELECT inside COALESCE

1 Answers1