Questions tagged [case]
191 questions
66
votes
4 answers
Does SQL Server CASE statement evaluate all conditions or exit on first TRUE condition?
Does the SQL Server (2008 or 2012, specifically) CASE statement evaluate all the WHEN conditions or does it exit once it finds a WHEN clause that evaluates to true? If it does go through the entire set of conditions, does that mean that the last…
Juan Velez
- 3,303
- 19
- 57
- 75
10
votes
2 answers
How do I select data with a case statement and group by?
I'm trying to manipulate data a little by using a case statement but can't seem to figure it out.
Stops
--------------------
1Stop
1-Stop
1 stop
1 Stop
1stop
1 Stop
2-Stop
Im trying to come up with:
1-Stop.... 6
2-Stop.... 1
What I've tried…
KingKongFrog
- 373
- 3
- 5
- 11
10
votes
1 answer
How to multiply rows for a column that contains negative and zero values?
I'm trying to get the Product of all rows for a specific column in a grouped by query. Most examples I've found point me towards combining exp, sum and log
exp(sum(log([Column A])))
The problem I'm having is that the column contains some zeros for…
bluetoft
- 203
- 2
- 6
9
votes
1 answer
Strange conditional Syntax in TSQL Query: "<=+" What does it do?
I am working on diagnosing a problem with a legacy query in TSQL. One of the returned columns in the query is:
CASE WHEN PaidDate >= '2021-01-01' AND PaidDate <=+'2021-10-31' THEN WO ELSE 0 END AS dYTDWO,
I have not seen "<=+" as a condition…
user2238903
- 93
- 3
9
votes
3 answers
postgres simple number to string mapping
I have a an Enum at the programming language level which is stored as a simple integer on the table. Think:
APPLE = 1
GOOGLE = 2
MSFT = 3
AMAZON = 4
... (100s more)
I just wanna query the table and instead of the number return the corresponding…
Sam R.
- 177
- 1
- 9
8
votes
1 answer
Null Values in a CASE statement
I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to alter/delete any of the already created tables…
user2921015
- 111
- 1
- 3
8
votes
3 answers
Oracle SQL - CASE in a WHERE clause
Is it possible to somehow do this?
WITH T1 AS
(
SELECT 1 AS SEQ, 'NOTHING 1' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 2 AS SEQ, 'NOTHING 2' AS SOME_TYPE FROM DUAL UNION ALL
SELECT 3 AS SEQ, 'SOMETHING 1' AS SOME_TYPE FROM DUAL UNION ALL
…
Pranav Shah
- 183
- 1
- 1
- 5
7
votes
1 answer
ORDER BY items must appear in the select list [...]
Using Microsoft SQL Server 2008, I get the following error.
Msg 104, Level 16, State 1, Line 43
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The query is I am using is kind of…
Evan Carroll
- 65,432
- 50
- 254
- 507
7
votes
1 answer
Improve CASE select performance
I have the following query which is taking up to 2 seconds to run on a data set of less than 100K rows.
My SQL is very rusty but this just looks like it's doing a ton more work than it should. Can anyone offer some pointers on where to look to…
rism
- 203
- 2
- 7
6
votes
2 answers
CASE ORDER BY with multiple columns, and different sort options
I am attempting to use a T-SQL CASE ORDER BY in a stored procedure where I am passed @OrderBy parameter as a TINYINT.
@Orderby = 1 Then Date column should be ASC
@Orderby = 2 Then Date column should be DESC
My question is: How can I get the date…
Eric Reid
- 75
- 1
- 1
- 5
6
votes
3 answers
Return multiple rows from matching on multiple CASE matches?
I want to add a column to my query which will specify one or more categories a row matches. I want to take this:
+--------------+---------------+
Row | Product | Quantity_Sold |
+--------------+---------------+
1 | Coca-Cola | 15 …
Nathan C. Lee
- 73
- 1
- 1
- 5
6
votes
1 answer
Can CASE be used to select which table to JOIN?
I'm attempting to use a CASE expression to select which table each row should be joined with.
Say I've got a table of item_instances in my game, of all the items in the game world, and some of the items are cookie-cutter copies of generic items from…
baudot
- 235
- 1
- 4
- 7
6
votes
1 answer
SUM CASE WHEN CLAUSE
I have the below Query and I want the Ageing profile to show as a new column
my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+
I basically want the results to appear lie this with the Net due appearing in the appropriate…
Chris
- 61
- 2
5
votes
1 answer
Include non-inserted rows as result of INSERT INTO ... RETURNING
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For example
CREATE TABLE all_sums (sum…
Ell
- 153
- 4
5
votes
1 answer
Group values: CASE text, ELSE field value
I have a ROAD table:
+----+------------+
| ID | ROAD_CLASS |
+----+------------+
| 1 | ARTERIAL A |
| 2 | ARTERIAL B |
| 3 | ARTERIAL B |
| 4 | ARTERIAL C |
| 5 | ARTERIAL C |
| 6 | ARTERIAL C |
| 7 | COLLECTOR |
| 8 | COLLECTOR |
| 9 |…
User1974
- 1,517
- 25
- 54