Menu
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.
769
Generally speaking, you use the COALESCE expression in the column list of a SELECT statement, although its usage is not limited to the SELECT statement. COALESCE itself takes as arguments a list of 1 to N expressions and returns the value of the first expression that is not NULL.
The SQL COALESCE function can be syntactically represented using the CASE expression. For example, as we know, the Coalesce function returns the first non-NULL values. SELECT COALESCE (expression1, expression2, expression3) FROM TABLENAME; The above Coalesce SQL statement can be rewritten using the CASE statement.
The SQL server's Coalesce function is used to handle the Null values. The null values are replaced with user-defined values during the expression evaluation process. This function evaluates arguments in a particular order from the provided arguments list and always returns the first non-null value.
You can use coalesce anywhere, including the where clause, yes.
You should place parenthesis around the selects:
coalesce( (SELECT ID FROM Stmt G WHERE G.CLAIMNO=C.CLNUMBER) , (select StmtSeq.nextval from dual) ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
