We’re rewarding the question askers & reputations are being recalculated! Read more.
96

Nope. Here's a simple test: SELECT COALESCE(1, (SELECT 1/0)) -- runs fine SELECT COALESCE(NULL, (SELECT 1/0)) -- throws error If the second condition is evaluated, an exception is thrown for divide-by-zero. Per the MSDN Documentation this is related to how COALESCE is viewed by the interpreter - it's just an easy way to write a CASE statement. CASE is ...


74

How about this one - as reported to me by Itzik Ben-Gan, who was told about it by Jaime Lafargue? DECLARE @i INT = 1; SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END; Result: Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered. There are trivial workarounds of course, but the point is still that CASE does not always guarantee left-to-...


38

The documentation makes it reasonably clear that the intention is for CASE to short-circuit. As Aaron mentions, there have been several reported instances where this has been shown to not always be true. So far, most of these have been acknowledged as bugs and fixed. There are other issues with CASE (and therefore COALESCE) where side-effecting functions ...


20

I've come across another case where CASE / COALESCE do not short circuit. The following TVF will raise a PK violation if passed 1 as a parameter. CREATE FUNCTION F (@P INT) RETURNS @T TABLE ( C INT PRIMARY KEY) AS BEGIN INSERT INTO @T VALUES (1), (@P) RETURN END If called as follows DECLARE @Number INT = 1 ...


13

From the docs, conflict_action specifies an alternative ON CONFLICT action. It can be either DO NOTHING, or a DO UPDATE clause specifying the exact details of the UPDATE action to be performed in case of a conflict. The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed ...


9

Another example CREATE TABLE T1 (C INT PRIMARY KEY) CREATE TABLE T2 (C INT PRIMARY KEY) INSERT INTO T1 OUTPUT inserted.* INTO T2 VALUES (1),(2),(3); The query SET STATISTICS IO ON; SELECT T1.C, COALESCE(T1.C , CASE WHEN EXISTS (SELECT * FROM T2 WHERE T2.C = T1.C) THEN -1 END) FROM T1 OPTION (LOOP JOIN) Shows no reads against T2 at all. The ...


7

I just wanted to mention a strategy you may not have considered. It may not be a match here, but it does come in handy sometimes. See if this modification gives you any better performance: SELECT COALESCE(c.FirstName ,(SELECT TOP 1 b.FirstName FROM TableA a JOIN TableB b ON ..... WHERE C.FirstName IS ...


5

I'm not sure that your MCVE fully represents the problem that you have, but I'll answer the question as given. This question is about performance, so having just a few rows in the table won't cut it. I duplicated your sample data a million times for a total of 6 million rows for FeeTest and 3 million rows for FeeTestClient. Code to do that below: DROP TABLE ...


5

You are basically building two tables, which then has to be joined on the date column. In your query you miss the join condition. One clear way of solving this is building the two result sets as CTEs (WITH queries): WITH pc AS (SELECT planning_day, sum(sot_contribution) AS minutes_planned FROM planning_constraints ...), p ...


4

Try this select a.Date, a.Foo, b.Bar from TableA a LEFT JOIN TableB b ON a.Date = b.Date UNION select b.Date, a.Foo, b.Bar from TableB b LEFT JOIN TableA a ON a.Date = b.Date Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left ...


4

Using @dezso's solution, which is the accepted answer above, here is the amended query and result for reference. WITH pc AS (SELECT date_trunc('day', start_time - INTERVAL '18 hours')::date as planning_day, sum(sot_allowed) AS minutes_allowed FROM planning_constraints WHERE start_time>='2016-11-26 18:00:00+00' AND start_time<'2016-12-03 18:00:...


4

To answer your question COALESCE would be utterly pointless where you placed it to begin with. The aggregate function count() never returns NULL. And logic dictates that num_60_79 can never be lower than 1 in the subquery. Related: Query optimization or missing indexes? But that's irrelevant, because if a subquery returns no row, then it cannot return any ...


4

I've been reading and re-reading the original question. What I have come to is this: Return records from tblProjecten where matches exist for parameters that have been supplied (are not null). Where the parameters are not supplied (or null) then assume all matches for that child table Effectively you're making your joins dynamic based on whether a ...


3

Given an index exactly like (note Fee is the only included column): CREATE INDEX [IX dbo.FeeTest ClientId, Val, Val2 (Fee)] ON dbo.FeeTest ( ClientId, Val, Val2 ) INCLUDE ( Fee ); Two alternatives: 1. Cross apply This uses an inner loop join, a reduced number of comparisons, and avoids a sort: SELECT FTC.Id, FTC.[Name], ...


3

If the two queries: SELECT * FROM foo f LEFT JOIN baz b ON f.x = b.y; and SELECT * FROM foo f LEFT JOIN (SELECT * FROM baz) b ON f.x = b.y; return different results, then it's a bug and the table and view definitions are irrelevant. That should never happen. Before you go and post a bug report, check your view and queries in the version ...


3

The actual standard says that all of the WHEN clauses (as well as the ELSE clause) have to be parsed to determine the data type of the expression as a whole. I'd really have to get out some of my old notes to determine how an error is handled. But just off hand, 1/0 uses integers, so I would assume that while it's an error. It's an error with the integer ...


3

Assuming hours are added to table in chronological order. If not, sort them before inserting them! First we need to relate each record to successive records. You say that is done by matching their hrs1 & hrs2, and making sure the initial record ends one day before the next starts. Your sample data only includes two records that are sequential, so I ...


2

No, it would not. It would only run when c.FirstName is NULL. However, you should try it yourself. Experiment. You said your subquery is lengthy. Benchmark. Draw your own conclusions on this. @Aaron answer on the sub-query being run is more complete. However, I still think you should rework your query and use LEFT JOIN. Most of the time, sub queries ...


2

You can simply group by IPDL: SELECT RAP.IDPL, MAX(C41.TIP_COD_DESEU) AS CD41, MAX(C41.STOC_VAL) AS CANT41, MAX(C42.TIP_COD_DESEU) AS CD42, MAX(C42.STOC_VAL) AS CANT42, MAX(C43.TIP_COD_DESEU) AS CD43, MAX(C43.STOC_VAL) AS CANT43 FROM CHEST_UL CHT INNER JOIN UL_PUNCT_LUCRU rap ON cht.fk_id_raportor = rap.id FULL OUTER JOIN ...


2

I think you want to do something like UPDATE sciana1 SET id = gid + COALESCE((SELECT max(id) FROM sciana), 0); COALESCE() will return you the first non-NULL parameter. Then max(id) is NULL for an empty table, which means the COALESCE expression returns 0 when the table is empty, and the actual maximum when it is not. You can further improve this by ...


2

This is the generally accepted standard for how to concatenate values contained over multiple rows. SELECT ID, LEFT(brands, LEN(brands) -2) brands FROM ( SELECT DISTINCT ID, ( SELECT brand + ', ' [text()] FROM myTable brands WHERE ...


2

All this approach does in to make your original query a derived table then group by pub_id. SET hive.auto.convert.join = TRUE; SELECT max(DEAL_ID) as DEAL_ID , EXCHANGE_ID , PUB_ID , sum(INCREMENTAL) as INCREMENTAL , sum(FIRST_IMPS) as FIRST_IMPS , sum(SECOND_IMPS) as SECOND_IMPS FROM ( SELECT First.deal_id , ...


2

More importantly, if ... some of the results have no entries You need crosstab(text, text) - the variant with 2 input parameters instead of crosstab(text) you are using now to avoid incorrect results. Detailed explanation: PostgreSQL Crosstab Query So, assuming the respective values in the column stage are 'some_column_name_1' and 'some_column_name_2':...


1

I think you are using LEFT joins incorrectly. The way the query is now, with a column from each table involved in the WHERE clause, the joins effectively act as INNER joins, not LEFT joins. As @Kent explained in comment: When the condition is in the join statement (the ON part) the restriction is applied before the join. When it's in the WHERE, it's ...


1

I've found that this solution works. It's not very elegant and I'm worried over scale(does it run the subquery twice or once) but it works. Here is the fiddle WITH subquery as ( SELECT First.deal_id ,COALESCE( First.exchange, Second.exchange ) as exchange_id ,COALESCE( First.publisher, Second.publisher ) as pub_id ,COUNT (DISTINCT(...


1

move it to up level more correct form: SELECT col1, .. colx, COALESCE(num_60_79,0) as num_60_79, ... colY FROM (second_table NATURAL FULL JOIN (SELECT assignment_id, count(*) AS num_60_79 FROM (SELECT assignment_id, mark_as_percent FROM avg_required_table ...


1

On MySQL 5.7.11 we are getting the expected results. Seems like a bug in prior versions. +---+--------+--------+------------+ | x | x | y | z | +---+--------+--------+------------+ | 1 | 2 | 1 | 1 | +---+--------+--------+------------+ | 2 | (null) | (null) | (null) | +---+--------+--------+------------+ | 3 | (null)...


1

COALESCE can take a whole list of values to check for NULL where ISNULL takes one check value and would require nesting to check more values. SELECT COALESCE(col1, col2, col3, col4, col5, 0) AS value_returned FROM myTable vs SELECT ISNULL(col1, ISNULL(col2, ISNULL(col3, ISNULL(col4, 0)))) AS value_returned FROM myTable Since your case is using float ...


1

no one has bothered with an answer an index on [HomePages] [Key] an index on [HomePageTranslations] [CultureName], [HomePageId] and make it a unique constraint if appropriate the query itself looks fine to me you could probably just use a view and an index on [HomePageTranslations] [HomePageId] and have fine response 10s of thousands is not that ...


1

I solve it with UNION ALL: with cte as (select rap.cod, case when (coalesce(val1_a.v1,0)<>0 or coalesce(val1_a.v2,0)<>0 or coalesce(val1_a.v3,0)<>0) then 'A' end as cat, ...


Only top voted, non community-wiki answers of a minimum length are eligible