•Returns the result_expression of the first input_expression = when_expression that evaluates to TRUE.
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 ...
SQL Server usually does short-circuit evaluation for CASE statements (SQLFiddle):
--Does not fail on the divide by zero.
WHEN 1/1 = 1 THEN 'Case 1'
WHEN 2/0 = 1 THEN 'Case 2'
--Fails on the divide by zero.
WHEN 1/1 = 99 THEN 'Case 1'
WHEN 2/0 = 99 THEN 'Case 2'
There are however ...
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:
SET col = CASE
WHEN cond1 THEN expr1
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 =
WHEN @UserRole = 'Analyst' THEN
CASE WHEN ...
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),
INSERT INTO dbo.Ids ( Name, Value )
VALUES ( 'a', 1 );
INSERT INTO dbo.Ids (...
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),
(3, 1)) V(A, B)
ORDER BY B
Returns (ordered by alias)
| B ...
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
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END
CASE WHEN (...
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
when Stops in ('1Stop', '1 Stop', '1 stop') then '1-Stop'
END as ...
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
SET @rand =...
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);
on lk.id = t.lk;
id | name
-: | :-----
2 | apple
1 | ...
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 ...
Instead of doing a lookup like that for each row, you could join the AddressRole and Setting tables like this:
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 = ...
You should use the GROUPING function
GROUPING returns 1 for aggregated or 0 for not aggregated in the
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.
AS (SELECT DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) AS B2B_Week,
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
MAX( CASE WHEN <<condition1>> then group else null end ) as "1.group",
MAX( CASE WHEN <<condition2>> then group ...
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 ...
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:
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 '%') ) ...
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')
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 |.
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:
CREATE TABLE dbo.K
( KOD VARCHAR(255) NOT NULL CONSTRAINT PK_K
PRIMARY KEY CLUSTERED
CREATE TABLE dbo....
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
OK, I'll take the provided example, and modify it a bit:
(<insanely complex expression>) as pop,
when pop is null then 'isnull'
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 ...
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 ...
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, ...
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 );
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 ...
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:
To check the columns (a much, much, much more likely culprit for this symptom IMHO):
FROM [Temp].sys.columns AS c
INNER JOIN [Temp].sys.objects ...
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.
You can't combine boolean expressions like that. Try it this way:
SET STATE = 2,
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')
WHERE STATE = 1;
This may not directly help you, since I only know SQL Server, but in any case this is how I would approach this:
CREATE TABLE #Example
Eid integer NULL
My idea revolves around the idea that COUNT(...