If no comparison in a CASE expression evaluates to TRUE, and you don't specify an ELSE argument for the CASE, then NULL is returned.

In this case, you've only...

It won't cause data loss in the sense of data going missing from your tables or anything like that.

What you are effectively saying, however, is that you're willing...

It's the reference to the pre-aggregation functional_amt in the ORDER BY that is the issue.

You'll need to order by the aggregated value (likely what you intended anyway).

Just to add to what Luis said, the trick is that the SELECT to check for existence is only taking shared locks. 

Nothing prevents two instances of that code...

Every year I tell myself I'm going to make a point of going to several of these, and every year I realize that there are very few within my acceptable...

As Phil indicated, there really isn't a one-size-fits-all trick that applies here.

Given what you've stated about your code, I'd suspect that the query is simply too complicated for...

To tell you exactly what is going on would require examining the execution plan.

In general, though, what is happening is that in the second case, you are forcing...

I'm not sure how much traction your request will get, but I suppose you could try.

The trick is that it's not an unusual behavior of undo that's at...

A few points need to be made.

First, if you could post DDL for your table (the CREATE TABLE statement), sample data (in the form of INSERT statements), and...

One straightforward way this can happen is adding some columns to a table.

Under some conditions, this is a metadata-only change and existing rows will not reflect the new...

I'm not aware of such a thing, but that sounds like basically writing your own optimizer, so I'm not too surprised 🙂

On that note, with this particular query...

You won't need a SET QUOTED_IDENTIFIER ON in the dynamic SQL here, just in the main batch.

Your problem is that SET QUOTED_IDENTIFIER takes effect at parse time, not...

When you do not specify a length for char or varchar in variable declaration, the variable will have a length of 1.

See https://docs.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql

If you run a...