Questions tagged [coalesce]
The coalesce tag has no usage guidance.
41
questions
22
votes
2
answers
6k
views
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 ...
- 629
1
vote
2
answers
128
views
coalesce more than 1 column in sql server
I have the following query where I search 2 tables for the item price
select productSerial, productName
coalesce(
(select top 1 price
from productPrice pp
where pp.id = wi.id
order by pp.date),
(...
- 117
0
votes
1
answer
71
views
Why is PostgreSQL `coalesce` not working as expected/hoped inside a function? [closed]
A simple function that looks like this:
CREATE OR REPLACE FUNCTION text_to_tsvector(config regconfig, source text)
RETURNS tsvector
LANGUAGE sql
IMMUTABLE PARALLEL SAFE STRICT
RETURN
to_tsvector(...
0
votes
1
answer
17
views
coalesce works only once
I have a table with redundant values in the first two columns:
sqlite> select * from amino where "lebensmittel" = 'Walnüsse';
Lebensmittel Protein pro 100g Histidin Isoleucin Leucin ...
- 349
2
votes
1
answer
2k
views
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 #...
user253549
6
votes
5
answers
26k
views
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.
...
- 61
0
votes
0
answers
928
views
PostgreSQL COALESCE arguments change resulting timezone for DateTime
I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:
SELECT
"CreatedOn", -- 7/24/2020 8:15:03 AM
COALESCE(&...
- 11
6
votes
2
answers
2k
views
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 |
----------...
- 219
0
votes
1
answer
860
views
PostgreSQL 9.6.17 COALESCE function error
I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error:
invalid syntax for type double precision: ""
Example query:
SELECT COALESCE(date_part('...
0
votes
1
answer
448
views
How do I join on a timestamp and include NULL entries?
I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in ...
2
votes
1
answer
3k
views
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 ...
- 180
4
votes
1
answer
7k
views
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) ...
- 428
0
votes
1
answer
329
views
SELECT COALESCE with subselects is throwing error 'Operand should contain 1 column'
I made this query:
SELECT
COALESCE(
(SELECT *
FROM JournalEntryTitleAndContent jetc
WHERE jetc.language_id = 2
...
- 147
0
votes
2
answers
3k
views
MySQL Coalesce date missing results, why?
Running MySQL 8.0.16, settings left at defaults as far as I know.
I have a table, let's call it the_table, with columns
id: bigint primary key auto_increment
the_time: datetime
I have a record ...
- 101
-2
votes
1
answer
111
views
Calculation showing wrong result [closed]
I am using postgresql to display QA% in my project. Now issue is that, QA% for one of the user is showing wrong data.
this is my calculation I am using to show QA%
(to_char(( (coalesce(s_qa_num,0) ...
1
vote
1
answer
2k
views
How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?
I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc...
These are stored in a table with a Hirarchy-ID to ...
- 573
1
vote
3
answers
785
views
Returning 0 if value is NULL
SELECT
p.id
, ig.NumReceived
, og.NumShipped
, coalesce(
(p.StartingInventory - og.NumShipped + ig.NumReceived),
p.StartingInventory
) as OnHand
, p.ProductName
, p....
- 121
2
votes
1
answer
5k
views
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 ...
- 173
3
votes
2
answers
8k
views
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:
...
- 259
1
vote
2
answers
4k
views
Merging continous date range using T-SQL
I have a table containing a part time working pattern consisting of start and end dates together with hours values.
Where there is an gap between an end date and a subsequent start date, this ...
- 23
0
votes
2
answers
3k
views
MySQL - Why is my BASH Script insert/update query not working correctly? [closed]
Background: I have a MySQL table that stores ip addresses for a very large amount of hosts. It's a simple table (host xyz -> ip1, ip2, ip3). These hosts will write a flat file with it's ip addresses ...
- 11
0
votes
2
answers
11k
views
How to select TOP 1 value from each column where value is not null
I am trying to select many columns from a table where there can be multiple rows with the same ID. Each row contains data from columns that I want to return in one row. Any time a column has a value ...
1
vote
2
answers
3k
views
Ignore the joins without any result in SQL Server
Situation
I'm making a SQL server query to filter data from the projects table (tblProjecten). This table got a lot of many to many relationships to other tables like region (tblProjectenRegio), ...
- 930
4
votes
2
answers
126
views
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 ...
- 1,513
2
votes
2
answers
3k
views
How can I group the null results in this FULL OUTER JOIN with non-null responses?
I am looking at the overlap and non-overlap (unique values) of users-ids from two different select statements using a full join. The main differentiation being that one table will have a deal_id = 0 ...
- 31
3
votes
2
answers
14k
views
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 ...
- 33
1
vote
2
answers
2k
views
Outer join on a view with calculated columns performs calculation for missing ("outer") records
I have a view in MySQL, which has some calculated fields — COALESCE and the like. If I do a left outer join on that view (so records from that view may be missing), then the calculated fields are ...
- 480
39
votes
2
answers
41k
views
'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.
...
- 565
5
votes
2
answers
132
views
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 ...
- 199
0
votes
1
answer
766
views
UPDATE and CASE problem [closed]
It is my first question in this forum.
I wrote this code, but it is incorrect and I can't find a problem.
I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom).
If table sciana is empty I ...
-2
votes
1
answer
693
views
COALESCE on a single table [duplicate]
I have the below table and i would like to get the below result with using the COALESCE . Any help will be appreciated.
Thanks
sam
- 1
2
votes
1
answer
3k
views
Addition With Null Values
I have inherited a table that has NULL values for float fields, instead of a 0 value. I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same ...
- 960
0
votes
1
answer
1k
views
Coalesce on right hand table field with left join, or a better way?
I have two SQL Server 2014 tables one has "en-GB" cultured names and descriptions, and a second has translations for other cultures.
The "en-GB" table...
CREATE TABLE [dbo].[HomePages]
(
[Id] ...
- 447
1
vote
1
answer
48
views
Oracle database - get two rows (A,B) for every (COD) row
I have a Oracle database and a variable that can have a value for A and a value for B like in this example:
cod cat v1 v2 v3
123 A 1 10 11
123 B 0 0 0
111 A 2 3 ...
- 349
3
votes
1
answer
170
views
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 (
...
- 199
0
votes
1
answer
366
views
Joining Oracle tables with 2 strings to be oa a single row (in a distinct SQL filter)
I have an Oracle Database and if I fire that SQL:
SELECT distinct RAP.ID_PL, SES_RAP.AN, RAP.DENUMIRE,
A3T2.COD_DESEU, A3T2.TIP_AMB,
A3T2.COD_REC, A3T2.COD_VAL
FROM chest cht
INNER JOIN ...
- 349
1
vote
1
answer
108
views
oracle database filter with full outer join and coalesce intersect
in a Oracle Database if I "shoot"
SELECT
RAP.IDPL,
C41.TIP_COD_DESEU AS CD41,C41.STOC_VAL AS CANT41,
C42.TIP_COD_DESEU AS CD42,C42.STOC_VAL AS CANT42,
C43.TIP_COD_DESEU AS CD43,C43....
- 349
0
votes
1
answer
5k
views
joining tables with full outer join and... COALESCE?
I have a few tables in an Oracle database linked by id - fk_id, if I do:
select RAP.ID_PCT_LUCRU,SES_RAP.AN,
TIS.CANT as c1,'R12' as modc1,
TIT.CANT as c2,'D13' as modc2,
TIMB....
- 349
2
votes
1
answer
793
views
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 ...
- 2,933
0
votes
1
answer
103
views
Coalesce per row - get all Detail IDs grouped per HeaderRow e.g. Invoice1 | 1,2,3 Invoice 2 | 4,5,6
I have the following demo data (taken from Adventureworks)
SalesOrderNumber SalesOrderDetailID
SO43659 12
SO43659 11
SO43659 8
SO43659 9
SO43660 ...
- 1,395
103
votes
8
answers
21k
views
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:
...
- 1,255