Questions tagged [coalesce]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2 votes
1 answer
79 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 #...
user avatar
  • 25
2 votes
2 answers
495 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. ...
user avatar
0 votes
0 answers
28 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(&...
user avatar
5 votes
2 answers
589 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 | ----------...
user avatar
  • 209
0 votes
1 answer
165 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('...
user avatar
0 votes
1 answer
201 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 ...
user avatar
2 votes
1 answer
1k 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 ...
user avatar
3 votes
1 answer
3k 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) ...
user avatar
  • 358
0 votes
1 answer
139 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 ...
user avatar
  • 125
0 votes
2 answers
1k 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 ...
user avatar
-2 votes
1 answer
59 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) ...
user avatar
1 vote
1 answer
996 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 ...
user avatar
1 vote
3 answers
710 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....
user avatar
  • 121
2 votes
1 answer
3k 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 ...
user avatar
1 vote
2 answers
5k 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: ...
user avatar
1 vote
2 answers
3k 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 ...
user avatar
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 ...
user avatar
0 votes
2 answers
9k 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 ...
user avatar
1 vote
2 answers
2k 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), ...
user avatar
4 votes
2 answers
105 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 ...
user avatar
2 votes
2 answers
2k 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 ...
user avatar
  • 31
3 votes
2 answers
10k 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 ...
user avatar
  • 33
1 vote
2 answers
1k 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 ...
user avatar
  • 455
22 votes
2 answers
27k 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. ...
user avatar
5 votes
2 answers
111 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 ...
user avatar
0 votes
1 answer
530 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 ...
user avatar
-2 votes
1 answer
305 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
user avatar
  • 1
2 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
969 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] ...
user avatar
  • 437
1 vote
1 answer
45 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 ...
user avatar
  • 339
2 votes
1 answer
156 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 ( ...
user avatar
  • 189
0 votes
1 answer
354 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 ...
user avatar
  • 339
1 vote
1 answer
93 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....
user avatar
  • 339
0 votes
1 answer
3k 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....
user avatar
  • 339
2 votes
1 answer
654 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 ...
user avatar
  • 2,813
0 votes
1 answer
96 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 ...
user avatar
102 votes
8 answers
19k 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: ...
user avatar
  • 1,245