Stack Overflow
Stack Overflow

Questions tagged [coalesce]

COALESCE is a SQL function that returns the first non-NULL expression among its arguments. COALESCE() is ANSI standard and may be used instead of vendor-specific alternatives such as ISNULL(), NVL() or IFNULL().

Return the first non-NULL argument. The COALESCE(expression1, expression2... expressionN) function is variety of the CASE expression.

Examples

Consider the expression: 

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN

It is equivalent to 

COALESCE(expression1, ... expressionN);

Another usage:

SELECT
  field1,
  COALESCE(SUM(customers), 0), -- if the SUM is NULL, it would be calculated as 0
  field3,
FROM
  ...

References

932 questions
7
votes
4 answers

What's wrong with the ?? operator used like this:

So insteed of writing: if (obj.Collection == null) obj.Collection = new Collection(); obj.Collection.Add(something); I thought of writing: obj.Collection = obj.Collection ?? new Collection; obj.Collection.Add(something); It kind of feels…
Calin
  • 6,661
  • 7
  • 49
  • 80
6
votes
5 answers

Can I use the coalesce operator on integers to chain CompareTo?

I want to do something like this: public override int CompareTo (Foo rhs) { return Bar.CompareTo(rhs.Bar) ?? Baz.CompareTo(rhs.Baz) ?? Fuz.CompareTo(rhs.Fuz) ?? 0; } This doesn't work as written; is there some minimal…
Matt Chambers
  • 2,229
  • 1
  • 25
  • 43
6
votes
3 answers

Equiv. to Coalesce() in XAML Binding?

In SQL I can do this: Select Coalesce(Property1, Property2, Property3, 'All Null') as Value From MyTable If Property1, 2 and 3 are all null, then I get 'All Null' How do I do this in XAML? I tried the following, but no luck: …
Jerry Nixon
  • 31,313
  • 14
  • 117
  • 233
6
votes
1 answer

COALESCE types character varying and numeric cannot be matched

I have a table named table1 with columns Gender varchar(10) and Team numeric. create table table1 ( ID integer Gender varchar(10), Team numeric ); insert into table1 (ID,Gender,Team)…
Omat
  • 67
  • 1
  • 1
  • 4
6
votes
2 answers

What is the C# equivalent of the Oracle PL/SQL COALESCE function?

Is there a one statement or one line way to accomplish something like this, where the string s is declared AND assigned the first non-null value in the expression? //pseudo-codeish string s = Coalesce(string1, string2, string3); or, more…
COPILOT User
  • 301
  • 1
  • 3
  • 4
6
votes
1 answer

How to code an intelligent coalesce in Java?

object.getProperty().getSubProperty().getSubSubProperty(); Consider the code above. An object has a property, that has a subProperty, that has a subSubProperty, that can be accessed with getter methods. What can we do in Java to achieve something…
GarouDan
  • 3,743
  • 9
  • 49
  • 75
6
votes
1 answer

Coalesce equivalent in Hibernate Criteria query?

I want to write the following query as a Hibernate Criteria query: select to_char(nvl(ol.updated_datetime, ol.created_datetime), 'dd/mm/yyyy'), sum(discount_price) from order_line ol where nvl(ol.updated_datetime,…
JMM
  • 3,922
  • 6
  • 39
  • 46
6
votes
1 answer

ERROR: COALESCE types bytea and character varying in PostgreSQL

I write a postgresql and pass parameter name1 from my java code with Spring + Hibernate. SELECT * FROM Test WHERE name = COALESCE(:name1, name2) but i got an error ERROR: COALESCE types bytea and character varying The type of name1 is String in my…
LoveTW
  • 3,746
  • 12
  • 42
  • 52
6
votes
2 answers

Does Ruby Sequel have an equivalent for the SQL "coalesce" function?

In SQL in order to avoid getting a NULL value, I can use the "coalesce" function to substitute it like so: SELECT COALESCE(some_column, 0) FROM some_table; But I can't find any way to do the same thing using Sequel.
Hans van der Meer
  • 63
  • 3
6
votes
1 answer

SQL Server: how can I use COALESCE with a PIVOT table?

What I'm trying to do is provide a value if another value doesn't exists within my pivot table. Pivot table SELECT * FROM MyTable PIVOT ( MAX(Number) for Total in ([Bob], [Jim], [Carol], [Simon])) as MaxValue Result Item | Bob | Jim |…
daveomcd
  • 6,367
  • 14
  • 83
  • 137
6
votes
3 answers

ANSI equivalent of IS NULL

I am trying to find the ANSI way to write the T-SQL 'IS NULL'. (corrected, was 'IN NULL') Some posts on the internet say you can use coalesce to make it work like 'IS NULL' The reason I like to do this: portable code. And the query must return the…
chihwah li
  • 85
  • 1
  • 1
  • 4
5
votes
1 answer

MySQL, multiple rows to separate fields

I have a MySQL table with fields and data such as follows; PartNumber Priority SupName a1 0 One a2 0 One a2 1 Two a3 0 One a4 1 Two a5 2 …
T Schultz
  • 53
  • 3
5
votes
2 answers

Print from jq using a wild card (or coalesce to first non null)

I have the following command: kubectl get pod -A -o=json | jq -r '.items[]|select(any( .status.containerStatuses[]; .state.waiting or .state.terminated))|[.metadata.namespace, .metadata.name]|@csv' This command works great. It outputs both the…
Vaccano
  • 78,325
  • 149
  • 468
  • 850
5
votes
2 answers

How to specify an ORDER BY from comparisons and null checks in MySQL?

I have two tables feed_old(default data) and feed_new(new data) where cron job will run every day and update ONLY feed_new table with the current information Cron job $url = "localhost/test.xml"; $xml =…
Ivan
  • 433
  • 5
  • 16
5
votes
3 answers

BigQuery Standard Get first not null value when grouping

I have a table like this: CUSTOMERS_ID DATE_SALES DIMENSION MARIO1 20200201 NULL MARIO1 20200113 Spain MARIO2 20200131 NULL MARIO3 20200101 France MARIO3 20191231 Spain and I need to order by…
Mario M.
  • 802
  • 11
  • 26
1 2 3
…
62 63