Questions tagged [coalesce]
41 questions
104
votes
8 answers
Does SQL Server read all of a COALESCE function even if the first argument is not NULL?
I'm using a T-SQL COALESCE function where the first argument will not be null on about 95% of the times it is ran. If the first argument is NULL, the second argument is quite a lengthy process:
SELECT COALESCE(c.FirstName
,(SELECT…
Curtis
- 1,265
- 2
- 8
- 9
42
votes
2 answers
'Column reference is ambiguous' when upserting element into table
I am using PostgreSQL as my database. And I need to create an entry in the database, and if it's already exists, just update its fields, but one of the fields should be updated only if it's not set.
I've used info from this question:…
serge1peshcoff
- 595
- 2
- 6
- 9
23
votes
2 answers
Why is COALESCE not a function?
The docs insist that ISNULL is a function, but COALESCE is not. Specifically, they say
The ISNULL function and the COALESCE expression
If I put on my Lisp hat, I can think of COALESCE as a macro and everything makes sense. However, I've never…
J. Mini
- 1,161
- 8
- 32
7
votes
5 answers
Why does SELECTing sum() return null instead of 0 when there are no matching records?
This is but one of countless little details that make me frustrated all the time, forces me to memorize all kinds of special code and causes my queries to become uglier than they should have to be.
Try this query on for size:
SELECT sum(amount) FROM…
Klappoklang
- 71
- 1
- 1
- 2
6
votes
2 answers
Postgres not returning data on array_agg query as below
The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id | name | owner |
--------------------------------
| 1 | ABC | …
PaxPrz
- 219
- 2
- 11
5
votes
2 answers
How to display queries of separate tables in adjacent columns?
I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.
Here are the schema of the two tables (edited for readiability):
…
Alex Willison
- 199
- 10
4
votes
1 answer
Create index for WHERE COALESCE() condition
I'm using PostgreSQL V9.6.11
Table DDL:
CREATE TABLE test_c (
insrt_prcs_id bigint NOT NULL,
updt_prcs_id bigint, src_sys_id integer NOT NULL,
load_dttm timestamp(6) with time zone NOT NULL,
updt_dttm…
Rj_N
- 436
- 1
- 7
- 13
4
votes
2 answers
Pick the most non-default value from a selection of values
Given the following tables:
CREATE TABLE FeeTestClient (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name VARCHAR(16))
INSERT INTO FeeTestClient (Name)
VALUES ('Test'), ('Test 2'), ('Test 3')
CREATE TABLE FeeTest (FeeId INT IDENTITY(1,1) NOT NULL…
Der Kommissar
- 1,523
- 1
- 16
- 27
3
votes
2 answers
COALESCE with cast
I'm pulling data from a jsonb column in Postgres, and casting it to an integer to compare it against a count. The problem is that in some cases the data apparently can't be cast to integer and I get:
SQL Error [22P02]: ERROR: invalid input syntax…
user101289
- 269
- 6
- 16
3
votes
2 answers
Why does COALESCE function not work for this query?
I am trying to count the number of columns that is retrieved from the subquery, and some subqueries may be empty. So instead of displaying it as an empty cell, I want it to be 0. Found out that I can do this by using the COALESCE function, but for…
Mathu
- 33
- 1
- 1
- 3
3
votes
1 answer
Why does coalescing make this query quicker?
I have the following query
SELECT SQL_NO_CACHE
`table1`.*
FROM
`table1`
LEFT JOIN `table2` ON table2.id = table1.table2_id
WHERE
(`table2`.`date_assigned` >= '2015-06-21')
AND (
`attempt_1_date`IS NOT NULL
OR…
TMH
- 199
- 1
- 6
2
votes
1 answer
Full outer joins, coalescing, indexes and views
I have two tables, let's call them TableA and TableB.
TableA TableB
------ ------
ID ID
Date Date
Foo Bar
Both tables have an index on their date field.
Now, I want to…
Shaul Behr
- 2,963
- 8
- 34
- 42
2
votes
1 answer
COALESCE still returning null values
UPDATE #Product_Comments
SET #Product_Comments.Comments = (
SELECT COALESCE(STRING_AGG(p.Label, ', '), '') + ' '
from (
select distinct Label, Product_ID
from #TEMPORARYTB
where Product_ID = pc.Product_id
…
user253549
2
votes
1 answer
Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?
In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning.
Beside local indexes, there are two global indexes on it.
Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.
Next night, the…
D. Lohrsträter
- 180
- 1
- 10
2
votes
1 answer
Default values for crosstab results
I'm trying to write my first crosstab query in Postgres. Some of the results have no entries and so the rows that are returned have null entries. How do I set a default value for those entries?
Here is a row that is returned:
-[ RECORD 1…
agent nate
- 173
- 1
- 7