52
•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
Reference
https://docs.microsoft.com/sql/t-sql/language-elements/case-transact-sql
This is standard SQL behaviour:
A CASE expression evaluates to the first true condition.
If there is no true condition, it evaluates to the ELSE part.
If there is no true ...
16
SQL Server usually does short-circuit evaluation for CASE statements (SQLFiddle):
--Does not fail on the divide by zero.
SELECT
CASE
WHEN 1/1 = 1 THEN 'Case 1'
WHEN 2/0 = 1 THEN 'Case 2'
END;
--Fails on the divide by zero.
SELECT
CASE
WHEN 1/1 = 99 THEN 'Case 1'
WHEN 2/0 = 99 THEN 'Case 2'
END;
There are however ...
13
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 simplified that away from your question. You probably have code like this:
UPDATE LinkedServer.database.dbo.table
SET col = CASE
WHEN cond1 THEN expr1
WHEN ...
13
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 pass the WHERE test).
You can rewrite with nested CASE expressions:
WHERE 1 =
CASE
WHEN @UserRole = 'Analyst' THEN
CASE WHEN ...
13
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 dbo.Ids
(
Id INT NOT NULL IDENTITY(1, 1),
Value INT,
Name NVARCHAR(3)
);
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 1 );
INSERT INTO dbo.Ids (...
12
If an alias is used in an ORDER BY it must be used on its own, not inside an expression.
If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.
So for example
SELECT A AS B
FROM (VALUES (1, 3),
(2, 2),
(3, 1)) V(A, B)
ORDER BY B
Returns (ordered by alias)
+---+
| B ...
10
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 considered NULL and null isn't equal to anything.
As you found out
SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END
Returns 0
But
SELECT
CASE WHEN (...
9
The problem is that you can't use the alias Stops in the GROUP BY. In your query when you are using the GROUP BY it is using the individual values for each row. You'd need to use a subquery to get the result:
select stops, count(*) Total
from
(
select
CASE
when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
ELSE Stops
END as ...
8
Why this happens has already been answered by @PaulWhite in the SO question: How does this CASE expression reach the ELSE clause?
To solve it, you should calculate the ABS(CHECKSUM(NEWID()))%10 +1 outside/before the INSERT statement so it is calculated once. Something like:
DECLARE @i INT = 1 ;
DECLARE @rand INT ;
WHILE @i <= 10
BEGIN
SET @rand =...
8
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);
insert into t (lk) values (2),(1),(3),(4);
select
t.id,
lk.name
from t
join lk
on lk.id = t.lk;
id | name
-: | :-----
2 | apple
1 | ...
7
It appears that MS SQL Server uses a short-circuit evaluation also.
In the following test I have 3 tests. The first one is always true, the second one fails without referencing the table, and the third fails only when the data is taken into account.
In this particular run both rows are returned successfully. If I comment out the first WHEN, or the first ...
7
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)),
IsPickUp = Max(iif(ar.AddressRoleTypeID = 2, 1, 0)),
IsSender = Max(iif(ar.AddressRoleTypeID = 3, 1, 0)),
IsDelivery = Max(iif(ar.AddressRoleTypeID = ...
7
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 is itself NULL and not incorrectly treat it as a total row.
WITH T
AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
bm....
6
You are trying to write a PIVOT query. If you're trying to use a CASE to do so (which implies that you are not on 11g where you can simply use the PIVOT operator), you need to do an aggregate
SELECT work_id,
MAX( CASE WHEN <<condition1>> then group else null end ) as "1.group",
MAX( CASE WHEN <<condition2>> then group ...
6
A join would be the best solution here.
Create a table that contains a list of categories and which products are associated with them, something like Product, Category we'll call it ProductCategories. Then join on this table.
SELECT p.Product, p.Quantity_Sold, pc.Category
FROM sales_table p
JOIN ProductCategories pc ON pc.Product = p.Product'
This has the ...
6
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 code:
WHERE
COUNTRY_CD = '81930' AND
LANG_CD = '02' AND (
( PARM_ADTR_ID = 'ALL'
AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') ) ...
6
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 SupervisorApprovedBy IS NULL )
OR (ISNULL(@UserRole, '') <> 'Analyst')
5
T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.
5
In case you are interested on running this kind of sort repeatedly, or on a larger dataset, you may want to consider adding a key table to control the sort order, and JOINing to that table.
First, we setup the test environment:
USE tempdb;
CREATE TABLE dbo.K
( KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K
PRIMARY KEY CLUSTERED
);
CREATE TABLE dbo....
5
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 THEN FirstName ELSE GivenName END
, CASE WHEN x = 1 THEN GivenName ELSE FirstName END
5
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 will of course produce an error because an alias defined in the SELECT list, cannot be used in another expression in the same SELECT list or the (same level) WHERE ...
5
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 above where it explains:
Unlike other SQL Server data types, a Boolean data type cannot be specified as the data type of a table column or variable, and cannot ...
5
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 string (which includes 4 quote characters and a comma): 'Value1','Value2'
You could rewrite your condition using a CASE expression that evaluates to boolean, ...
5
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 totally silly and proprietary IF statement
SELECT IF( 1=1, 1, 12 );
5
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 in MySQL. You don't state that this is inside a stored program, so I'll assume it's not. If it is, please edit your question to provide more detail.
If you're ...
4
You need to check that the collation of the servers and columns match (collation isn't only determined at the database level).
To check the servers:
SELECT SERVERPROPERTY('COLLATION');
To check the columns (a much, much, much more likely culprit for this symptom IMHO):
SELECT collation_name
FROM [Temp].sys.columns AS c
INNER JOIN [Temp].sys.objects ...
4
There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.
My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.
4
You can't combine boolean expressions like that. Try it this way:
UPDATE BILLS
SET STATE = 2,
DATE_REACT =
CASE
WHEN (DATE_EM <= CURTIME() AND CURTIME() <= ADDTIME(DATE_EM, '00:02:00')) THEN ADDTIME(CURTIME(), '00:13:00')
ELSE ADDTIME(DATE_REACT, '00:13:00')
END
WHERE STATE = 1;
4
May I suggest doing it in a couple of passes:
SQL> select * from records;
SUBJ_NO
--------------------------------------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX ...
4
This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:
Sample Data:
CREATE TABLE #Example
(
Eid integer NULL
);
INSERT #Example
(Eid)
VALUES
(1),
(2),
(3),
(4),
(5),
(NULL),
(NULL),
(NULL),
(NULL),
(NULL);
My idea revolves around the idea that COUNT(...
Only top voted, non community-wiki answers of a minimum length are eligible
