Questions tagged [case]
The case tag has no usage guidance.
162
questions
2
votes
2answers
203 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 ...
-4
votes
1answer
34 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....
0
votes
2answers
72 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 = ...
4
votes
3answers
226 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/...
0
votes
2answers
54 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
2answers
345 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 ...
1
vote
1answer
31 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 ...
-2
votes
1answer
31 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
vote
1answer
58 views
CASE statement not returning expected results
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 ...
-1
votes
3answers
217 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
votes
4answers
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 ...
0
votes
1answer
34 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
vote
1answer
42 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
...
0
votes
1answer
21 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 ...
1
vote
0answers
53 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' ...
0
votes
2answers
14 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 ...
0
votes
0answers
37 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 ' '
...
-1
votes
1answer
307 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 ...
0
votes
1answer
30 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 ...
0
votes
1answer
41 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 ...
0
votes
1answer
15 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
1answer
40 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 ...
0
votes
1answer
320 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?...
0
votes
1answer
32 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
1answer
60 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 ...
1
vote
1answer
35 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 ...
5
votes
1answer
356 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 ...
0
votes
2answers
18 views
Combining previously different rows after changing value during query
I'm currently running a query that is supposed to return a salesperson's aggregated revenue, grouped by individual clients.
Because of how user input was handled, there are a lot of near-duplicate ...
0
votes
1answer
1k views
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
SELECT DISTINCT *
FROM (
SELECT a.Title, a.ID FROM TableA a
LEFT JOIN TableB b
ON a.ID = b.XID) c
ORDER BY
CASE WHEN c.ID > 10 THEN c.Title
ELSE c....
1
vote
4answers
124 views
SUM several CASE results
Given:
CASE
WHEN A IS NULL THEN 1 ELSE 0
END AS [A]
, CASE
WHEN B IS NULL THEN 1 ELSE 0
END AS [B]
, CASE
WHEN C IS NULL THEN 1 ELSE 0
END AS [C]
How can I SUM A+B+C being the ...
0
votes
1answer
81 views
Help with SQL CASE WHEN using % Query
I have two columns in a SQL Query ( Estimate and Billed ) which contain un-formatted numbers.
I am trying to write a CASE WHEN query for when the BILLED column is between 50% and 70% of the BILLED ...
0
votes
2answers
67 views
Is there a way to use a Case statement to read from one field and change another field? [closed]
I have a simple MS SQL database created from an import from a CSV file. I need to check a field to see if a word is in that field, and if it is, change the contents of another field. Like this:
When [...
0
votes
2answers
56 views
Improve CASE select performance on view creation
I am new to SQL, and the below view query is taking a long time to return values because of the case statement, can someone help in identifying how I can speed things up?
CREATE OR REPLACE FORCE VIEW ...
1
vote
1answer
38 views
Null check condition in Where clause
DECLARE @SubDesc1 nvarchar(100)
SET @SubDesc1 = null
I would like to do something like that.
Query 1
SELECT *
From Table1
where Category = 'Category1'
AND [Description] ='ABC'
AND ...
-2
votes
1answer
375 views
How to concatenate rows into one comma seperated value when used with case statements
I have a query that has multiple case statements. I am trying to reduce the results to one row per USER_ID that has one of the columns (specialty_id) comma separated because some users have more than ...
0
votes
3answers
124 views
Nullable smallint column usage in a CASE Statement nested in a STUFF function
I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (...
0
votes
0answers
42 views
Case inside case in oracle query
Can anyone help me?
I want to use query using CASE WHEN with logic where time less than 03:00 PM on Monday until Friday will get value start from tomorrow 03:00 PM until today 03:00 PM
SELECT ...
0
votes
3answers
53 views
I want to know which table the value comes from in inner join
I have a simple query where i do 2 inner joins to combine data from 3 tables. within the table i would like to know which table each value comes from. i attempted the following but the syntax is wrong....
4
votes
1answer
120 views
Conditional selection in FROM clause
I have two tables:
parent with columns identifier (pkey) and period;
child with columns identifier (pkey), parent_identifier and period.
There are additional columns, but I did not list them here as ...
0
votes
1answer
374 views
Conditional Join in DB2
2 tables are given
Table T_PRICE
P_Price P_Type P_Route
15 red long
10 long
5 small
Table T_ROUTE
R_Type R_Route
blue long
red ...
1
vote
1answer
197 views
Case statements for multiple fields when only certain cases exist for each field
We have an inventory feature where we generate Bills. There is an Edit Bill API call. We have implemented it as PATCH call.
A Bill with id = 1 has 2 LineItems :
| Stock Id | Qty | Rate ...
0
votes
1answer
196 views
Postgresql, case, select in when
System
PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit
Case
Does select work in case condition? I need return 1 state ...
2
votes
1answer
184 views
How to combine date and time with a string value in column created by Case in SQL Query
I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression.
I ...
0
votes
1answer
24 views
Building distinct Column from a common column from two tables
Good afternoon team,
hope all is well
I am hoping you can help me.
I have following two tables "GODS.DQ_TOBE" & "GODS.DQ_CURRENT". Within each table there is a common column called "...
0
votes
2answers
180 views
Update with rolling sum grouped by another column
I'm trying to build a query to update a MySQL 8.0 table that looks like this
-----------------------
user_id | total | delta
-----------------------
1 | 0 | 30
1 | 0 | -10
...
0
votes
1answer
21 views
Postgresql Date Function Returning Range In Readable Format
I am wondering if it is possible to use postgresql's date-time , concat and case functions to give one of the following results:
June 4th to 11th 2019 (where the 1st and 2nd date are within the same ...
0
votes
1answer
36 views
Multiple THEN outputs on a PostgresQL query
Helllo there,
I've been playing around with a materialised view, and my goal is to pre-process as much information as I can, aggregating the info into this view that would speed up the data analysis.
...
1
vote
2answers
208 views
Split cases in WHERE clause based on boolean variable
I have to split a WHERE clause based on a boolean variable:
If ShowZeroVariable is true, I only what to show rows value 0 in a certain integer column.
If false I want to show all that are greater ...
0
votes
2answers
499 views
How to use OR operator in CASE THEN statement
I have a situation like this
select col1, col2
from table1
where col1 = case when (booleanexp1) then a OR col1 = b
when (booleanexp1) then c OR col1 = d end
I mean I need to get ...
-3
votes
3answers
482 views
if statement in a query [closed]
I am getting an error while running this code:
Incorrect syntax near the keyword 'IF'
I need the output through function only with table return .
CREATE FUNCTION [dbo].[...
