Questions tagged [case]
The case tag has no usage guidance.
185
questions
0
votes
0
answers
19
views
Replacing Part of a string within a group Concat
I have part of a string that I would like to remove when selecting. The Problem is that there are a few pieces within the string that changes. This is what I have so far.
Select
GROUP_CONCAT(...
-1
votes
0
answers
29
views
Trying to make multiple columns in CASE in SQL?
Question: For each country/league in the database, find the percentage of the home games won in 2012/2013, 2013/2014 and 2014/2015 seasons respectively. Your output table should have 11 rows, one for ...
0
votes
1
answer
34
views
PostgreSQL-Trigger with CASE WHEN and results combined
PostgreSQL 14
In a trigger function, I have a long CASE...WHEN...THEN statment looking like this :
...
CASE
WHEN a >= 100 AND b <= 200 THEN field = 'bla'
WHEN a >= 200 AND b <= 300 THEN ...
- 177
0
votes
1
answer
65
views
How to conditionally return multiple different columns in sqlite?
I am trying to return multiple columns only if a condition matches, else, return something else. Using Sqlite3, my understanding is that the way to do this is using CASE, so something like this:
...
- 113
0
votes
2
answers
49
views
Limit my ticker count to maximum 12 Months
I'm trying to write a case expression where it looks at the due date and if it has passed chooses the upcoming or closest due date. Now due to certain database restriction I have to do this manually. ...
- 23
-2
votes
2
answers
102
views
SQL Server: left join resulting in multiple rows, want each row result in separate column in result
I need some logic help with a complex join situation. Here is an overall idea of the 3 tables I am working with
Table 1:
ID Number Type 1
A1
A2
Table 2:
ID Number Type 1
ID Number Type 2
A1
B1
...
0
votes
1
answer
72
views
How to use a JOIN inside a CASE expression?
I have four tables like this:
company:
id
companyContactID
customerID
companyTeamID
1
12
21
54
2
14
12
78
document_associated_company:
id
companyID
documentID
1
2
98
2
1
12
document
id
...
- 153
-1
votes
1
answer
968
views
SQL Multiple CASE conditions with SUM and count Functions along with JOIN Condition
Kindly help me with the right syntax of mine, really appreciate it, if you could just edit my existing syntax into correct one without alternate options. thanks in advance
Table schema
CREATE TABLE ...
- 3
0
votes
2
answers
145
views
A = 1 else 0 if B satisfy two conditions in ms sql server(azure data studio)
I am trying to get the distinguish column depending on another column's value.
I was trying to approach with CASE function but could not get exactly what I wanted.
Condition
if ColB has only 1 then ...
- 11
2
votes
1
answer
819
views
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 have a data set that appears like this:
next_generation_date
procedure
interval
frequency
2021-01-17 00:00:00.000
Clean Restroom
1
day
2021-01-17 00:00:00.000
Vacuum
2
week
2021-02-01 00:00:00....
- 21
0
votes
2
answers
412
views
comparing a date value in one table against multiple values in another to categorise by age
I have created a form which allows users to enter multiple, unlimited month number values and label them to use against inventory to categorise the age of each item.
Table 1 - AgeConfig - Manually ...
- 101
-2
votes
2
answers
32
views
I have a table where where each project_id generates multiple rows
My table below shows the rows and columns where the column data is filtered using a CASE syntax to differentiate capital from expense. The problem is I am getting multiple rows per project_ID.
I need ...
- 3
5
votes
1
answer
2k
views
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 ...
- 195
1
vote
1
answer
314
views
search a string to create dummy variables
I have a string variable I have created that is a concatenation of 12 columns, each of the 12 columns contains 4 or 5 digit character. I want to create a series of dummy variables, based on the ...
- 11
-1
votes
1
answer
105
views
Error while declaring procedure
When I run this query
it returns me a count of null values of the column country
(select
sum(case
when (country IS NULL) then 1
else 0 END)
as 'null_flag'
from city)
whereas ...
0
votes
3
answers
186
views
CASE statement issue [duplicate]
Can anyone help with this error?
SQL:
SELECT
InvoiceDate,
BillingAddress,
BillingCity,
Total,
CASE
WHEN Total < 2.00 THEN 'Baseline Purchase'
WHEN Total BETWEEN ...
- 1
0
votes
0
answers
244
views
PostgreSQL COALESCE arguments change resulting timezone for DateTime
I have strange behaviour of COALESCE function of PostgreSQL. Depending on arguments of this function the result is different:
SELECT
"CreatedOn", -- 7/24/2020 8:15:03 AM
COALESCE(&...
- 11
1
vote
2
answers
1k
views
MySQL - Update column based on multiple conditions
I have a table which has Rows like these:
+----------+--------------+---------------------+------+
| CASE_ID | CAT | BIRTH | TYPE |
+----------+--------------+------------------...
- 15
0
votes
0
answers
30
views
Best approach to convert a non trivial function (CASE WHEN) into a lookup (decision) table?
Assume A, B, C etc are attribute values in the current rowset
Current function logic is similar to:
Case A = 'Y' then
Case B in (1,2,3,4,5) OR C in (3,4,5,6) then 'Sales'
Case B in (99,98,102) OR D ...
- 257
9
votes
1
answer
1k
views
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 ...
- 93
0
votes
1
answer
101
views
Avoiding a duplicate CASE expression
SELECT x, y, z,
CASE
WHEN COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
NOT IN (null,0) THEN L_Count
ELSE COUNT (PH.header_id) OVER (PARTITION BY PL.header_id)
END as quantity
Is there a ...
- 27
0
votes
2
answers
825
views
Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB
We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
- 11
0
votes
3
answers
557
views
Query for getting the sum of all unique entries in a column
Currently I'm using mysql to return the sum of all data in a column that have the same word. To do this I'm using the following query:
SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft,
...
- 103
2
votes
2
answers
280
views
Zero/NULL Case Trick
In the book Getting Started with SQL, Thomas Nield talks about a technique he calls the zero/null case trick:
There is a simple but powerful tool that can apply different filtering
conditions to ...
- 1,387
-4
votes
1
answer
732
views
Case statement syntax error [closed]
I am trying the code below but the SQL throws an error
case
when (b.ModuleType in (20) and rtrim(substring(a.PrdAcctId,1,8))='RD')
then (a.Balance1+(a.Balance2-(a.Balance3+a.Balance4))
when (b....
- 1
0
votes
2
answers
429
views
Is it possible to nest CASE-statement into WHERE clause?
I am developing an application with FastAPI. It contains a function that brings two variables into a SQL query using the sqlalchemy.sql.text as shown here.
SELECT ATTR3
FROM TABLE
WHERE ATTR1 = ...
- 177
4
votes
3
answers
345
views
Why do subsequent unmet conditions in my CASE statement affect my return data type?
I have the following variables and CASE statement:
DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'
SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/...
- 135
0
votes
2
answers
460
views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
2
votes
2
answers
822
views
How to create a new column in SELECT CASE when string contains Arabic words?
I have a problem with a select case when statement, I would like to add a new column in the select case when statement, I got the result but with ????? because I have set the column to Arabic words, I ...
- 61
1
vote
1
answer
141
views
SQLite: How do I use the sum function
The code I am using is here
CREATE TABLE timecard (
id INTEGER PRIMARY KEY autoincrement,
clock_in REAL not null,
clock_out REAL,
lunch integer,
pay_type TEXT
);
INSERT INTO timecard ...
- 13
-2
votes
1
answer
150
views
Select Case Statement redundancy -- way to use a recurring expression WITH inequality?
I'm using SQL server.
I want to use a case statement to quickly 'bucket' a bunch of datediff results.
So---
case
when datediff(d,start_time,end_time) > 100 then '100+'
when datediff(d,start_time,...
- 1,533
1
vote
1
answer
1k
views
CASE statement not returning expected results [closed]
First time using Postgres and building a query with a CASE statement. I am trying to end up with a collection of data whereby one of the columns in the data (status) qualifies the row based on ...
- 119
-1
votes
3
answers
317
views
Function: if either x or y are null, return null
I'm looking for an Oracle 18c function that evaluates to null if either of two variables is null.
The Constraints: (details here)
The function/expression can't contain spaces (so CASE won't work).
I ...
- 1,387
-1
votes
4
answers
50
views
CASE expression with OR AND [closed]
I have 5 columns - Order, shipping, tracking, Delivery and Overallstauts.
I have have 4 status, Green means "good", Amber means "ok", Yellow means "average" and Red means ...
- 1
0
votes
1
answer
35
views
Unusual behaviour of a query when using case expression in group by, some records are being missed
I have 4 table with below structure:
Table "Customer_Info"
create table Customer_Info
(
Customer_Number int,
Customer_Issued_Branch int,
<Other Columns related to customer&...
- 1,376
1
vote
1
answer
65
views
A Query with a Dynamic Case clause or how to avoid Case When
Let's take an everyday query with a CASE clause so I can show you the problem, see how we'd have to change the query when a new Certification comes out:
select count(*) as "certCount",
CASE
...
- 181
0
votes
1
answer
39
views
Preserve CASE WHEN output as new column in MySQL
I am new to MySQL and this question may look silly, but:
I have a dogs table and I categorize their size by their weight:
SELECT *,
CASE
WHEN weight < 15 THEN 'Small'
WHEN ...
- 103
1
vote
0
answers
180
views
Oracle error 43914 in CASE WHEN expression: arguments have undefined collation
I want to know which is the difference between this query:
SELECT *
FROM TEST_TABLE
WHERE CHAR_FIELD = CASE WHEN KEY_FIELD = 'Q' THEN 'M'
ELSE CASE WHEN KEY_FIELD = 'T' THEN 'K' ...
- 11
0
votes
2
answers
15
views
Creating a column in dataset for later filter use (mysql)
I have a date column (format: 14.05.2018) and a count of sales. I would like to create an extra column that from the current date perspective flags fields that are +/-6 days from today. The following ...
- 11
0
votes
0
answers
43
views
Why are NULL values not being recognized as null in my CASE expression?
I've tried writing this two ways. The first one ignores the WHEN NULL THEN '' part, and the second one just doesn't work.
SELECT
buffer_mol,
(CASE buffer_mol
WHEN NULL THEN ' '
...
- 283
-1
votes
1
answer
868
views
MySQL CASE WHEN is not working as expected with user-defined variables?
i need help please with the following query. I am pulling zip codes (plz) along with user id (uid) from DB and ORDER them by zip (plz) and uid. Also I use a user-defined variable @prev to check if ...
- 41
0
votes
1
answer
48
views
Case overcounting what is expected
So I am attempting to setup a query that can track actions that are automated within our web system.
The first thing I am attempting to check is the reminders we are sending out to customers.
I have ...
- 1
0
votes
1
answer
550
views
Return single row with one to many relationship based on evaluated conditions
Database is Postgres.
I have an issue whereby I have a table with the following columns: ID, Product_ID Customer_ID, Price and Item_Type.
This table is joined on Product_ID to another table. For a ...
- 173
0
votes
1
answer
25
views
PostgreSQL CASE expression
how to write a case expression with Postgres? I have this
case
when rc.stat = 0 Then unpaid
when rc.stat = 1 Then paid
when rc.stat = 2 Then dispute
else rc.stat
end
but when I go to run ...
1
vote
1
answer
47
views
CASE in WHERE clause - retuns a single-row result instead of 4 rows?
Everyone,
I do not want to bother you with the whole tables schema so I will paste a couple of screenshot only so that my question becomes clear - I need to show all projects that are related to ...
- 151
0
votes
1
answer
3k
views
CASE WHEN DATE not providing correct results
When using the below query the "Result_field" returns "NO" even if the date is greater than 12/31/2020. How can I get it to show "YES" when the date is greater than 12/31/2020 and "NO" When Less Than?...
- 3
0
votes
1
answer
196
views
Sql Statement to display multiple true statement
I have a sql statement that checks for certain special characters in a string and returns that.
SELECT
CASE
WHEN (LEN(DSC1) - CHARINDEX(char(1), DSC1)) <> LEN(DSC1) THEN '[DSC1 - NUL (null)], '...
0
votes
1
answer
246
views
Using case and group by in select statement not working
I'm using a case and group by in a select statement and I would like for the case field name (item number) to not be apart of the group by clause statement. I've calculated the invoice weight as well ...
- 3
1
vote
1
answer
47
views
Conflicting 'LIKE' conditions in MySQL CASE statement
I have two conditions that are causing the CASE statement not to work as intended. I've tried changing which condition comes first, but it still only proceeds with one of them. Any suggestions?
CASE ...
- 25
5
votes
1
answer
430
views
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 ...
- 153