Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [case]

The tag has no usage guidance.

0
votes
0answers
33 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 ...
0
votes
2answers
22 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
83 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
38 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
63 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
40 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
31 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
89 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
796 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
794 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
193 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
66 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
813 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
202 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
129 views

Add a 'true' or 'false' in field depending on mix of values in joined table

I have a MASTER table which holds three columns - client, emailAddress and allArtReceived. It's a list of clients who have booked jobs with us, and we're waiting for them to send us artwork. There ...
0
votes
1answer
2k 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
63 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
0answers
23 views

mysql Case in Join doesn't give expected output

I' trying to search a value from table A in table B in 3 columns and if i found it then select the 4th column from table B. I'm usig case statement but the case conditions never checks in those 3 ...
0
votes
1answer
366 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 ...
1
vote
1answer
4k 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
610 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
40 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
3k 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
53 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
53 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
572 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 ...
-5
votes
4answers
15k 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 ...
1
vote
0answers
51 views

How to display a column's different values in multiple columns? [duplicate]

I have a table name 'myTable', and one of its column name is 'myValues' which take random values, would you please assist me how can I show each value in multiple columns with header name as VALUE of ...
2
votes
1answer
97 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
530 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
183 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 ...
3
votes
1answer
648 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: ...
4
votes
1answer
702 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
1k 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 ...
3
votes
1answer
4k 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
3k 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, ...
4
votes
2answers
1k 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, ...
0
votes
1answer
59 views

How to query and group based on matched multiple conditions?

I have a tableName with records as sample as following, the mainID is unique at all for same "name", but subID has different values (one or more) for same mainID(s), name mainID subID ...
0
votes
1answer
324 views

Problem with SELECT CASE WHEN THEN, 0 result [closed]

Need an advice in SELECT CASE WHEN THEN. In main, removed all unimportant function, usual select i.fav_id FROM users; i see original table: fav_id 1 2 6 7 but i need change some id's for result ...
0
votes
1answer
91 views

Combining nullif while checking if field is an integer with a case statement

As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some ...
0
votes
1answer
153 views

UPDATE and CASE problem [closed]

It is my first question in this forum. I wrote this code, but it is incorrect and I can't find a problem. I have 2 tables: sciana (gid,id,geom) and sciana1(gid,id,geom). If table sciana is empty I ...
0
votes
1answer
333 views

How to print text in select query instead of NULL value in DATE field in postgresql?

I have a table test as follows : create table test (id int, enroll date) I have inserted some rows as follows : insert into test values (1,'2012-01-02'); insert into test(id) values (2); ...
2
votes
4answers
374 views

Building where clause based upon parameters

In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN For example: select fld1, count(fld1) from xyz where fld1 in ( ...
7
votes
1answer
1k views

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 ...
0
votes
0answers
165 views

Postgres: split count depending on boolean column

I want to transform my table to generate a panel data set. So some columns were grouped and I use count as aggregator. But I want to count, depending on a value of the current row (e.g. likes) and ...
-1
votes
1answer
44k views

How to Update a Column Using CASE Statement with More Than 10 Conditions [closed]

I'm trying to update a column in SQL Server 2016 using CASE statement because I have to change the value based on different conditions. The problem is that I have more than 10 conditions and it seems ...
1
vote
1answer
26 views

Get another table row on the basis of first table

Scenario, I have three table like , Table - 1 table_1_id status 1 pending 2 progress 3 Table - 2 table_2_id status table_1_id 1 pending 1 ...
-1
votes
2answers
58 views

Expression for a result in case

I want to give an expression for a result in a case expression in SQL Server. When I checked the Links : CASE , EXPRESSIONS. But I'm still confused. Eg. Simple CASE expression: CASE ...
0
votes
1answer
46 views

Simple CASE Usage

Given: TABLE test x y a 1 b 2 TABLE test3 x y a 1 b 3 I wrote a query to retrieve all values from test, choosing the row with the higher y value. select t1.x, CASE WHEN (EXISTS (select 1 ...