Questions tagged [case]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
12 views

MySQL case sensitivity setting

Does anyone know the file name or path where I can change the lower_case_tables_names value on the latest version of MySQL Windows 11?
Tubatse Katlego Moloi's user avatar
0 votes
2 answers
200 views

Comparing 2 count columns with case

I have 2 tables, size and cost. My query does a count with a greater than comparison of cost and counts different sizes. table1: size u table2: cred t Here is my query: select u.size as size ,...
tkmagnet's user avatar
-1 votes
1 answer
43 views

how to GROUP BY so that not all results are grouped

When I use my query to select all data from the table I have a problem with the custnumber field. If custnumber is returned within the same month then it should group the results but only then. This ...
James's user avatar
  • 1
0 votes
1 answer
48 views

Set variable on multiple case within each group

Say I have below table cis aid cid apid ntime buid flg jid 30 1208 229067737 1026 9 DDDD400C 0 0 30 1209 229067737 1026 11 DDDD400C 0 0 30 0 229067737 1026 12 DDDD400C 1 100 30 1210 229067737 ...
Himanshuman's user avatar
2 votes
1 answer
80 views

MySQL - multiple CASE ordering with joined table

I have two tables: EVENTS ID Name 1 First event 2 Second event 3 Third event 4 Fourth event 5 Fifth event EVENTS_META ID EventID MetaKey MetaValue 1 2 date_expired 2023-02-09 00:00:00 2 2 ...
Martin J's user avatar
0 votes
2 answers
103 views

SQL multiple condition case when

SQL Case When - I have a column with job status options B & D. I want to create a case when SQL statement that will check and retrieve employee IDs with both statuses in the column. In thise case ...
jones0207's user avatar
1 vote
1 answer
231 views

Problem with case statement and dynamic table name

I have the following case statement case %1$s::text when ''lookup_relation_job'' then case %1$s.relation when ''followers'' then ''get-followers'' when ''following'' then ''get-...
Sumit Ghosh's user avatar
0 votes
0 answers
312 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(...
Joey Freeman's user avatar
0 votes
1 answer
728 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: ...
Josh Brower's user avatar
0 votes
2 answers
109 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. ...
NasahT's user avatar
  • 25
-1 votes
2 answers
2k 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 ...
nicole33789's user avatar
0 votes
1 answer
1k 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 ...
THpubs's user avatar
  • 153
-1 votes
1 answer
10k 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 ...
Harshita's user avatar
0 votes
2 answers
709 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 ...
ItsKJ's user avatar
  • 11
3 votes
1 answer
2k 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....
Gulfhawk's user avatar
0 votes
2 answers
833 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 ...
php-b-grader's user avatar
-2 votes
2 answers
35 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 ...
GeoH's user avatar
  • 3
6 votes
1 answer
10k 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 ...
baudot's user avatar
  • 225
1 vote
1 answer
656 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 ...
daniel3141's user avatar
-1 votes
1 answer
339 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 ...
Prachi Karekar's user avatar
0 votes
3 answers
646 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 ...
Mark_72's user avatar
0 votes
0 answers
823 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(&...
Artem Repko's user avatar
1 vote
2 answers
3k views

MySQL - Update column based on multiple conditions

I have a table which has Rows like these: +----------+--------------+---------------------+------+ | CASE_ID | CAT | BIRTH | TYPE | +----------+--------------+------------------...
Stefanie's user avatar
0 votes
0 answers
33 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 ...
adolf garlic 's user avatar
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 ...
user2238903's user avatar
0 votes
1 answer
826 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 ...
user973347's user avatar
0 votes
2 answers
2k 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 ...
Marc Preuss's user avatar
0 votes
3 answers
1k 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, ...
JJM50's user avatar
  • 103
3 votes
2 answers
576 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 ...
User1974's user avatar
  • 1,457
-4 votes
1 answer
2k 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....
hemu_k's user avatar
  • 1
0 votes
2 answers
1k 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 = ...
Taras's user avatar
  • 177
4 votes
3 answers
531 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/...
Mike S's user avatar
  • 135
0 votes
2 answers
949 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 ...
noblerthanoedipus's user avatar
2 votes
2 answers
2k 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 ...
abdou31's user avatar
  • 61
1 vote
1 answer
255 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 ...
linux478's user avatar
-2 votes
1 answer
446 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,...
user45867's user avatar
  • 1,683
1 vote
1 answer
2k 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 ...
TheRealPapa's user avatar
-1 votes
3 answers
363 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 ...
User1974's user avatar
  • 1,457
-1 votes
4 answers
56 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 ...
Sandy's user avatar
  • 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&...
Pantea's user avatar
  • 1,412
1 vote
1 answer
79 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 ...
Jeremy Thompson's user avatar
0 votes
1 answer
456 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 ...
Karol Daniluk's user avatar
1 vote
0 answers
320 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' ...
Artm94's user avatar
  • 11
0 votes
2 answers
41 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 ...
Aww's user avatar
  • 11
-1 votes
1 answer
1k 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 ...
Aldee's user avatar
  • 41
0 votes
1 answer
81 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 ...
stan's user avatar
  • 1
0 votes
1 answer
1k 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 ...
TheDemonLord's user avatar
0 votes
1 answer
40 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 ...
Timmy Turner's user avatar
1 vote
1 answer
60 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 ...
query_question's user avatar
0 votes
1 answer
8k 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?...
Calvin's user avatar
  • 3