Skip to main content
Share Your Experience: Take the 2024 Developer Survey
15 votes
Accepted

Strange conditional Syntax in TSQL Query: "<=+" What does it do?

TLDR: this is likely a typo1. AND PaidDate <=+ '2021-10-31' is parsed as AND PaidDate <= (+'2021-10-31'), and the unary plus doesn't change anything so... I'm speculating, but this probably has ...
mustaccio's user avatar
  • 26.1k
13 votes
Accepted

How to Update a Column Using CASE Statement with More Than 10 Conditions

There are two possibilities here. There is a limitation with linked servers that only allows a nest level of 10 when using CASE expressions. I imagine you are using a linked server but have ...
Aaron Bertrand's user avatar
13 votes

Using IS NULL in CASE Expression in WHERE Clause

The problem is likely the comparison to NULL, as explained in David Spillett's answer above. When @UserRole = 'Analyst', the comparison SupervisorApprovedBy = NULL will give UNKNOWN (and the row won't ...
ypercubeᵀᴹ's user avatar
13 votes
Accepted

How to multiply rows for a column that contains negative and zero values?

The magic of NULLIF seems to do the trick for the test case in your question. Since you used a different example than in your SQL Fiddle, I don't know if that's what you want there too. CREATE TABLE ...
Erik Darling's user avatar
11 votes

CASE statement issue

This error is coming from your WHERE clause, not your CASE expression. You say WHERE PurchaseType = 'Top Performer', however, PurchaseType is not a column in your table, so the query optimizer doesn'...
AMtwo's user avatar
  • 16.2k
10 votes

Empty result set and Case When = 1

Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ? No, The query didn't return 0 because the empty result is considered equal to 0 but because the empty result set is ...
Tom V's user avatar
  • 15.7k
8 votes
Accepted

postgres simple number to string mapping

IMHO the easiest way is by using a lookup table. create table lk (id int, name text); insert into lk values (1, 'apple'),(2, 'google'),(3, 'msft'),(4, 'amazon'); create table t (id serial, lk int); ...
McNets's user avatar
  • 23.8k
7 votes

Group By With Rollup results table has totals at the top

You should use the GROUPING function GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. The advantage of this is that it will deal correctly with the case that B2B_Week ...
Martin Smith's user avatar
  • 84.8k
7 votes
Accepted

Improve CASE select performance

Instead of doing a lookup like that for each row, you could join the AddressRole and Setting tables like this: SELECT a.AddressID, IsPrincipal = Max(iif(s.SettingValue Is Not Null, 1, 0)), ...
mendosi's user avatar
  • 2,087
7 votes
Accepted

Using IS NULL in CASE Expression in WHERE Clause

I think you are making it harder than it should be. If @UserRole is 'Analyst' then also SupervisorApprovedBy should be null? Else return everything? WHERE (@UserRole = 'Analyst' AND ...
Daniel Björk's user avatar
6 votes

postgres simple number to string mapping

JSON FTW. As a JS developer I'm partial to Object mapping. Here's an example of what that would look like in PG: SELECT COALESCE( ( (json_build_object( 1, 'APPLE', 2, 'GOOGLE', ...
andrew.carpenter's user avatar
6 votes

How to create a new column in SELECT CASE when string contains Arabic words?

Since you are using Unicode / UTF-16 (i.e. NVARCHAR), you do not need either the CAST or even an Arabic_* collation. The only requirement for string literals is to prefix them with a capital-"N&...
Solomon Rutzky's user avatar
6 votes
Accepted

Why do subsequent unmet conditions in my CASE statement affect my return data type?

The column defined by your CASE expression has a data type, as all columns do. SQL Server determines the resulting type in its usual way, making use of data type precedence. The type datetime has a ...
Paul White's user avatar
  • 85.7k
6 votes
Accepted

Avoiding a duplicate CASE expression

There are two issues at play here: the code is redundant because the window function is typed out twice the concern is that SQL Server will perform the count twice since it is referenced twice For ...
Aaron Bertrand's user avatar
5 votes

Building where clause based upon parameters

The output of a CASE expression has to be a single value not a list of values. The statement did not give a syntax error for cases 3 and 4 because '''Value1'',''Value2''' is evaluated as a single ...
ypercubeᵀᴹ's user avatar
5 votes
Accepted

Expression for a result in case

SQL Server does not have BOOLEAN type - at least not exposed, internally it has boolean values that are used in conditions - and a CASE expression cannot have a result of such type. See the link ...
ypercubeᵀᴹ's user avatar
5 votes

How do I write a conditional statement (IF) with MySQL?

What you want is a CASE expression which is standard-SQL method of implementing a conditional and supported by every major database, SELECT CASE WHEN 1 = 1 THEN 1 ELSE 12 END; Or you can use the ...
Evan Carroll's user avatar
  • 63.5k
5 votes

How do I write a conditional statement (IF) with MySQL?

I believe that syntax is actually valid; you can find similar statements in the MySQL documentation. However, it's considered a control flow statement, and those are only allowed in stored programs ...
RDFozz's user avatar
  • 11.6k
5 votes

postgres simple number to string mapping

Alright, using @McNets' answer I used with clause without having a temp table: create table t (id serial, lk int); insert into t (lk) values (2),(1),(3),(4); with m (k, v) as (values (1, 'apple'),(2,...
Sam R.'s user avatar
  • 167
5 votes
Accepted

Zero/NULL Case Trick

It is variously known as: conditional sum filtered aggregation conditional aggregation selective aggregation It may be either written explicitly using CASE or equivalently using FILTER syntax ...
Paul White's user avatar
  • 85.7k
5 votes

How do I create a SQL loop that generates future dates based on different frequencies and intervals for each item until a specified end date?

I recommend using a Calendar table. Aaron Bertand has a blog series about them For this code sample, I'll be using my calendar table. It's quite a bit of code so this is a link to my GitHub For the ...
Zikato's user avatar
  • 4,737
4 votes

CASE statement multiple conditions

Just add a WHERE condition. And if you are only displaying one value, RUNNING, then there is no reason for a CASE. Otherwise you will want to evaluate each condition in the CASE including what should ...
Steve Mangiameli's user avatar
4 votes
Accepted

set variable result in case statement to be used again in the same select statement

You need to either repeat the expression, or derive it in a separate scope, e.g.: SELECT name, tag, CASE WHEN tag = 'a' THEN 'a2' ELSE 'b2' END AS tag2 FROM ( SELECT name, CASE ...
Aaron Bertrand's user avatar
4 votes

Use multiple OUTER JOINS in a CASE statement

The code that produces the syntax error: SELECT V.I_VIN_FIRST_9, -- many more columns case when (D.C_ZONE is null or D.C_ZONE = '') then (coalesce(DL.C_ZONE, ' ') as ...
ypercubeᵀᴹ's user avatar
4 votes

Building where clause based upon parameters

Since implementing the filter with the help of a virtual table is an option for you, you could also consider the classic one-to-many form instead of arrays: values (1, 'Value1'), (2, 'Value2'), ...
Andriy M's user avatar
  • 23k
4 votes

Building where clause based upon parameters

Using arrays: select fld1, count(fld1) from xyz where fld1 = any( case $1 when 1 then array('Value1') when 2 then array('Value2') when 3 then array('Value1','Value2') ...
Abelisto's user avatar
  • 1,559
4 votes
Accepted

Why can't "NEXT VALUE FOR" be used in a case statement

To fully answer your question one needs to know intimate details of the SQL Server SQL engine implementation, and most (all?) people who have that knowledge likely have signed all sorts of NDAs and ...
mustaccio's user avatar
  • 26.1k
4 votes
Accepted

SQL CASE and ELSE

The CASE expression is evaluated from top to bottom, if no WHEN is matched ELSE is used. You need an END for each CASE. Your first expression: CASE WHEN xyz.something = 1 THEN 'SOMETEXT' WHEN ...
Lennart - Slava Ukraini's user avatar
4 votes

Collapsing a column to yes/no on query

Here are three simple methods to do it, Returning bool: Just use a simple CASE statement. SELECT name, CASE WHEN value = 'foo' THEN true ELSE false END FROM table; Returning bool compact: You can ...
Evan Carroll's user avatar
  • 63.5k

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