Hot answers tagged

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
  • 25.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
  • 38.8k
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.1k
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.6k
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.6k
7 votes
Accepted

how can I use a CASE with an alias defined befor case?

OK, I'll take the provided example, and modify it a bit: select (<insanely complex expression>) as pop, case when pop is null then 'isnull' end from rap4 ; The above ...
ypercubeᵀᴹ's user avatar
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,057
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
  • 83.3k
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
Accepted

How to use a CASE expression in the WHERE clause?

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your ...
mustaccio's user avatar
  • 25.1k
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
  • 83k
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
Accepted

order by case with multiple order criteria

Case is supposed to return one value, not a tuple. Beside, you should compare 1 with something. If you want conditional ordering you can do that with two case statements: ORDER BY CASE WHEN x = 1 ...
Lennart - Slava Ukraini'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

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

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
  • 62.2k
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
  • 83k
4 votes
Accepted

How to exclude rows containing null values within columns?

SELECT * FROM ( SELECT student_id, salutation, CASE WHEN salutation IN('Dr.') THEN 'Doctor' END AS NewSalutation FROM student.student ) WHERE NewSalutation IS NOT NULL;...
Philᵀᴹ's user avatar
  • 31.6k
4 votes
Accepted

How to use SET operation in CASE clause in SQL?

Based on your comment, you seem to be looking for a way to avoid repeating the same calculation over and over again. You could use APPLY VALUES to calculate the required values once, then use the ...
Mark Sinkinson's user avatar
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

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,509
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
  • 22.8k
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

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