Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.

Questions tagged [coalesce]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
100
votes
8answers
17k 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: ...
-2
votes
1answer
48 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
1answer
131 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 ...
2
votes
1answer
116 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 ( ...
1
vote
3answers
272 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....
1
vote
1answer
852 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 ...
0
votes
2answers
2k 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: ...
1
vote
2answers
1k 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 ...
1
vote
2answers
504 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 ...
0
votes
2answers
1k 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 ...
0
votes
2answers
6k 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
2answers
406 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), ...
4
votes
2answers
94 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 ...
2
votes
2answers
1k 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 ...
3
votes
2answers
5k 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 ...
11
votes
1answer
12k 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. ...
0
votes
1answer
374 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] ...
0
votes
1answer
198 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 ...
5
votes
2answers
104 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 ...
-2
votes
1answer
79 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
2
votes
1answer
1k 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 ...
1
vote
1answer
43 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 ...
0
votes
1answer
307 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 ...
1
vote
1answer
78 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....
0
votes
1answer
2k 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....
2
votes
1answer
557 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 ...
0
votes
1answer
95 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 ...