Questions tagged [case]
The case tag has no usage guidance.
124
questions
1
vote
1answer
29 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
34 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
34 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
20 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
29 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
14 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
25 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
32 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
0answers
30 views
SQL Server - CASE for multiple tables
hope I can explain myself right in this question.
I have three (3) tables from which I have to build a report, this is the data selection process I need to do in order to correctly output the data to ...
0
votes
2answers
70 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
71 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].[...
1
vote
1answer
173 views
Case Statement for on same month
i have a CASE statement, which classify my Customer-Revenue and set Flag, when Revenue <> 0 is. But my problem i should search also same month record on my case statement. Logic : if two records (...
0
votes
1answer
76 views
Get percentage from results from two case results
How i can get percentage from two resuts from case staments?
SELECT *,
COUNT(CASE WHEN estado_rep >=11 and RELATION=1 THEN 1 ELSE NULL END) as pendiente,
COUNT(CASE WHEN estado_rep >=...
0
votes
1answer
839 views
CASE in WHERE clause in MySQL
I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:
DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` ...
1
vote
0answers
34 views
Using conditions to return specific rows
From the following table i am trying to retrieve specific rows based on a criteria
+----+--------------+------+
| ID | UPDATED_DATE | TYPE |
+----+--------------+------+
| 1 | 01/13 | P |
|...
0
votes
0answers
36 views
Collect data checklist from different table
I want to collect data checklist from 5 different table. in example table c have data with same foreign key "id_name" then its state "yes" else "no". This is my sql statement
select a.id_name
case ...
1
vote
2answers
91 views
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 ...
-1
votes
2answers
302 views
DB2 Join with case when statement or union [closed]
How can I use a case statement in a join?
Given: 3 Tables
Dealer
DEALER_NO CUST_ID
001 100
002 200
003 300
Table: Customer
CUSTOMER_NO NAME CUSTOMER_MAIL
100 JOHN ...
-2
votes
1answer
49 views
Looking for help with this shipping report! The Case Syntax is throwing me for a loop
So, the goal of this report is to get all orders that have been shipped in a month (October here). I was initially just using the 'PickedUpOn' field but soon found that there were instances where this ...
2
votes
1answer
69 views
Filtering previous month data with case statement
These are my available attributes:
DT_CURRENT_DT Month Year
11-OCT-18 10 2018
12-OCT-18 10 2018
13-OCT-18 10 2018
14-OCT-18 10 2018
15-OCT-18 10 2018
16-OCT-18 ...
0
votes
1answer
406 views
MYSQL Using CASE Function between Dates
SQL Version: MySQL 5.6.32-78.1
I have this table below:
+-----+----------+--------------+--------------+--------------+------------+--------------+
| id | amount | currency | date_created | ...
0
votes
2answers
38 views
subquery inside a case statement
I have a huge view -the view gathers information of players.
In a specific part the view assigns agents for players, those players are selected from table 'S' and are being assigned using a case ...
1
vote
3answers
110 views
Return a value only when conditions satisfy for entire column
Can anyone help me to how to achieve below result
Input data
ORDER_NUMBER LINE_ITEM FLAG
LP-13288 1 TRUE
LP-13288 2 FALSE
LP-13288 3 (null)
LP-13288 4 FALSE
LP-13289 1 ...
1
vote
1answer
2k views
How to write case when in where statement in Oracle [closed]
I wrote below statement. In MySQL it executes ok, but in Oracle it fails with error:
meg:ORA-00905: missing keyword
How should I write this SQL?
SELECT count(*)
FROM userTable
WHERE ( CASE ...
3
votes
3answers
3k views
How do I write a conditional statement (IF) with MySQL?
What is wrong in below simple query? I tried to google it but not found
IF 1 = 1 THEN
SELECT 1;
ELSE
SELECT 12;
END IF;
1
vote
2answers
547 views
SQL case statement not executing 'else' [closed]
I am on Oracle 12.1.0.2.0
I have a query that I wrote utilizing a CASE statement. I have 2 "WHEN" statements and one "ELSE" statement. The query is working as expected except for the "ELSE" statement. ...
4
votes
2answers
115 views
String comparison without CASE statements?
Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do
SELECT c, c='foo'
FROM ( VALUES ('foo'),('bar') ) ...
3
votes
2answers
2k views
case statement sub query need to return multiple values
Below query need to return multiple values
select
CASE WHEN LTRIM(RTRIM([Order].ClientProject_Id))!=''
THEN (SELECT StationeryClientProject.ImpFile from StationeryClientProject
WHERE ...
4
votes
2answers
210 views
Sargability of queries against a view with a case expression
I have a view that looks similar to this:
CREATE VIEW Shipment.Shipment AS
SELECT CASE
WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
...
0
votes
1answer
5k views
Use of null in case expression [closed]
In the customers' table, many customers do not have state data in the state column therefore when we select customers, the state column displays NULL values, which is not meaningful for the reporting ...
1
vote
2answers
155 views
Collapsing a column to yes/no on query
I have a table with two columns name and value.
There are many rows with the same name, but with different values.
I want to create a function that returns a table with 2 columns. The first column ...
0
votes
1answer
541 views
How can I stop script execution if insert value is null and set value if not null?
I have tried to use Case and SET NOEXEC ON but get the following errors:
Syntax Error: unexpected 'SET' (set)
Syntax Error: missing 'closing parenthesis'
INSERT INTO tag (table, repr, tag, ...
0
votes
1answer
2k views
MYSQL - Operand should contain 1 column(s) - Nested Case statement [closed]
I have written the below query with nested case statements. The syntax checks out but when I run it I receive error code 1241: Operand should contain only 1 column(s). I have attempted to remove the ...
2
votes
1answer
10k views
Empty result set and Case When = 1
I'm pretty sure this is a implicit conversion but I wasn't able to find information about it.
Suppose I have this SQL:
SELECT
CASE WHEN (SELECT 1 WHERE (1=1)) = 1 THEN 1 ELSE 0 END
My subquery is ...
0
votes
1answer
906 views
case when IsNull Alternatives?
Is there a way, to optimize this query, where several IsNull in relation with dates are used? Unfortunately the query is a way slow and actually I have no glue, how to make it way faster.
case
when ...
0
votes
1answer
61 views
SQL CASE and ELSE
Hello I am considering the following:
CASE
WHEN xyz.something = 1 THEN 'SOMETEXT'
WHEN xyz.somethingelse = 1 THEN 'SOMEOTHERTEXT'
WHEN xyz.somethingelseagain = 2 THEN 'SOMEOTHERTEXTGOESHERE'...
10
votes
1answer
5k views
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 ...
0
votes
1answer
55 views
Looking for another method to count in a single query
So, I found this article is very useful Join two tables and return data and count in a single query
and here Single query to return counts over different IDs in a single record for some IDs or ...
0
votes
2answers
60 views
Categorize rows as first, last or in between
I have a table called road_events.
create table road_events
(
event_id number(5,0),
road_id number(5,0),
event_type nvarchar2(50),
lifecycle_number number(5,0)
);
insert ...
6
votes
1answer
679 views
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 ...
-4
votes
4answers
30k views
Using IS NULL in CASE Expression in WHERE Clause [closed]
I have a WHERE clause that I want to use a CASE expression in. However, my CASE expression needs to check if a field IS NULL.
If the @UserRole variable value = 'Analyst', then the ...
2
votes
1answer
108 views
Using Oracle XE, how do I create a CASE statement To return a specific date
I am Trying to create a view based on a business calendar table called Business Payment Calendar view with two new calculated columns:
Payment Warning Date (Payment_Warning_Date)
Payment Due Date (...
2
votes
3answers
925 views
How to avoid redundant case/when in Postgres?
Based on this simple example table:
create table some_data (x integer, y integer);
insert into some_data (x,y) values
(1,1),
(1,2),
(1,1000),
(1,2),
(2,1),
(2,1),
(2,2);
I query it trying to sum ...
1
vote
1answer
258 views
Converting a Crystal Report Formula to a CASE expression
With attempting to convert a crystal report formula into a SQL CASE expression, I seem to be having trouble understanding the concept of "total_pallet_weight" and "order_no" utilizing the "AND" in the ...
2
votes
1answer
993 views
Why can't “NEXT VALUE FOR” be used in a case statement
I was writing an INSTEAD OF INSERT trigger and hit an error on this code:
CASE
WHEN Inserted.KeyId IS NOT NULL THEN Inserted.KeyId
ELSE NEXT VALUE FOR SomeSchema.NextKeyId
END
The error was:
...
5
votes
1answer
1k views
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 |...
0
votes
2answers
3k views
UPDATE Column values with Case and JOIN
I have 2 tables "a" and "b".
Table "a"
| property | element | ply | ..
Table "b"
| property | ply | material | ..
What I want to do is to add a column to the first table called "material" and ...
2
votes
1answer
7k views
set variable result in case statement to be used again in the same select statement
Is it possible to create, inside a single select statement, like this:
SELECT
name,
CASE
WHEN name = 'a' THEN 'a'
ELSE 'b'
END tag,
CASE
WHEN tag = 'a' ...
-1
votes
1answer
5k views
Use multiple OUTER JOINS in a CASE statement
I tried to put :
Select .... , (CASE WHEN (D.C_ZONE is null or D.C_ZONE = '') THEN
coalesce(DL.C_ZONE, ' ') ELSE coalesce(D.C_ZONE, ' ' ) END ) AS C_ZONE,
coalesce(Z.N_ZONE,'') as N_ZONE, ...
3
votes
2answers
2k views
Group By With Rollup results table has totals at the top
I have the following query that returns a set of counts by weeks beginning with Monday, with a row for the aggregate totals.
select distinct
DATEPART(ISO_WEEK, ig.IN_EVT_DT_TM) as B2B_Week,
...
