The tag has no usage guidance.

learn more… | top users | synonyms

-1
votes
1answer
11 views

Calling based on 2 date limits for 2 scenarios in one query [on hold]

select * from PERFM where date_value in (case when funds_id = 15315 then date_value >= '20160501' else date_value >= '20150101' end) I want results for 15315 id starting from 2016 and for ...
1
vote
1answer
51 views

Matching empty string is not working

I have the following query SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength, DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII, CAST(Box.ice AS ...
0
votes
0answers
30 views

How can I return output from different DB2 selects using CASE statement

Can someone please help on this.. I have a situation where i need to check o/p of one query and based on results i need to check another query. SELECT CASE WHEN (SELECT count(*) FROM fcdp001....
1
vote
1answer
56 views

SUM (CASE WHEN) AS.. query

I'm very new to SQL and I'm trying and failing to get the right syntax on what I presumed would be a pretty easy query. I have a table (CustomerSales) with four columns: Period: a combination of ...
1
vote
1answer
23 views

Using Case with no column result

I made this solution but it continues not returning any value If the user not exists. select case when TypeId = 1 then 1 when TypeId = 0 then 2 when count(1) < 1 then 3 ...
0
votes
0answers
34 views

Unable to fetch data based on latest date and time

I have a table which stores application details. Applications are nothing but a set of data showing scores. Each application has 6 parts namely 1,2,3,4,5, and 6. So, in the database table parts can ...
0
votes
2answers
38 views

CASE statement multiple conditions

I've got a simple table below, Select * from message_log where Message = 'BUILD' order by 1; +------------+-------------+ | **Message | Log** | +------------+-------------+ | Build | ...
0
votes
1answer
42 views

ORACLE - Trying to return MAX or MIN based on a CASE statement

I'm working on a query to give me either a MAX or a MIN based on whether another column is divisible by 2. SELECT CASE mod(ROLE_TYPE, 2) WHEN 1 THEN MAX(SALARY) ELSE ...
0
votes
1answer
40 views

Update a valid column when another column has changed

We have a valid_phone column that just stores 1 or 0 for valid or not valid. When the user changes their phone number we want that field to go back to 0 so they will have to validate their number ...
1
vote
2answers
127 views

Return multiple columns (int datatype) inside a CASE expression

How can I get multiple columns from a CASE expression? with cte as ( select ROW_NUMBER() over (order by id)'Rownumber', id from dbo.Test ) select case when (b.id-a.id) > 1 ...
-3
votes
1answer
37 views

How to use a conditonal update using cases

Case when oldvalue like 'val1','val2','val3' then newvalue = 'newVal1' Case when oldvalue like 'val4' then newvalue = newvalue + 'newval2' else newvalue = newalue + 'othernewval' Then I would need ...
0
votes
0answers
20 views

MySQL IF Statement works for PIVOT scenario… Until I GROUP BY

I've been trying for days to figure out how to pivot data to give me this appearance: I've written the following code to generate the data requested and it works. But When I use GROUP BY to ...
0
votes
0answers
32 views

Case Statement to Identify Multiple Errors

I'm trying to write a statement that identifies any of 200 potential errors / illogical combinations in a data set for each line item. My first thought was to write a case statement for each error ...
1
vote
0answers
58 views

Multiple conditional counts in the select clause - what's the best way?

(Bit of a long one, sorry) To set the scene: N.b. This is on SQL SERVER 2012 We have a table [CardLog] in an access control system database. Every time an ID card is printed, a row is added to this ...
1
vote
1answer
127 views

How to exclude rows containing null values within columns?

This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes ...
1
vote
0answers
33 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
3
votes
1answer
101 views

How to use a CASE expression in the WHERE clause?

I am using a CASE expression in my WHERE clause like this: SELECT * FROM ASPECT.WR_AM_ADT_SUMM INNER JOIN ASPECT.WR_AM_DLR_DTL ON AS_CNTRY_CD = DD_CNTRY_CD AND AS_DLR_CD = ...
0
votes
0answers
15 views

Forcing OBIEE to Act Like Excel… If, Then, Else Evaluates ONCE

I get duplicates that I do not want using the following expression with the subsequent data: Case When Locate('Stuffed',"Journals"."Journal Text") > 0 THEN 'Stuffed' When Locate('Full',"Journals"."...
1
vote
2answers
55 views

SQL Server If statement assigning values [closed]

Please see the pseudocode below. Basically what I'm trying to do is write a statement that when an account number contains one of the specified codes it will assign that Level to the entire account ...
1
vote
2answers
201 views

How to use SET operation in CASE clause in SQL?

I have the following structure, and I'm doing a SET operation in a CASE clause but it is producing an error: Incorrect syntax near SET Query: DECLARE @tbl_Result TABLE ( RowID INT IDENTITY(...
1
vote
1answer
34 views

Oracle database - get two rows (A,B) for every (COD) row

I have a Oracle database and a variable that can have a value for A and a value for B like in this example: cod cat v1 v2 v3 123 A 1 10 11 123 B 0 0 0 111 A 2 3 ...
0
votes
0answers
46 views

How to pull sales for every single month based on my query below using ORACLE

So I am working on a report right now that pulls customer information, and sales for a particular date range, who used a PROMOTION CODE. My current query pulls all the data I need and gives me a sum ...
0
votes
0answers
83 views

Alternative to CASE

What alternatives are there to replace the use of CASE, and will also improve the speed of the query. SELECT created_at, COUNT(CASE WHEN cp > 0 THEN 1 END)::int4 rk, COUNT((CASE WHEN ...
0
votes
0answers
46 views

Aggregate function within CASE or Joins

Database - MySQL I have 3 tables Table1 - Order Columns -OrderId,OrderDate Contraints- PrimaryKey-OrderId Table2 - Product Columns-ProductId, InsertDate, ...
0
votes
1answer
66 views

how can I use a CASE with an alias defined befor case?

how can I use an alias like pop in a further case like in this example (Oracle database). select (select sum(ccs_pop) from rap4) as pop, case when pop+x=a+b+c then pop+x end as sum1, case ...
0
votes
2answers
69 views

Calculating Margin in CASE Statement and GROUP BY Clause

I think I am running into an error in how I have chosen to aggregate my columns in a group by statement. I am working out Margin % and grouping into months using a case statement like so: SUM(CASE ...
0
votes
1answer
53 views

order by case with multiple order criteria

I would like to know how to use multiple order columnes with case. For example case 1, order by FirstName, GivenName case 2 order by GivenName, FirstName. I tried this, but it looks like the colon ...
0
votes
2answers
57 views

Order By Case String Not Working

I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I ...
1
vote
1answer
63 views

Adding 2 Case statements

I have 2 separate case statements which are producing the correct results: Case when A_staff_Service_Type.type = 'v' then isnull(Indirect_Duration,0)/60.00 else null end The above produces 6.5 ...
2
votes
1answer
90 views

Case When over 2 columns return results into 1 output column

I have an issue in SQL Server (using 2014 but not sure that's relevant). I have 2 variables Q20 and Q46 which depending on result I want to output a value to the same column for each result. For ...
3
votes
1answer
135 views

Display non-null and null count using case statement

In total there are 10 values in my table, 5 of which are null. I need to display the count using case statement in Oracle, like output below: Sample Data: +------+ | Eid | +------+ | 1 | | 2 |...
1
vote
1answer
59 views

Selecting 1st and 3rd Tuesdays

So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various ...
0
votes
1answer
43 views

How to do an efficient case insensitive search

As an example, take a city table with 2 columns city_ref (bigint) city_name (varchar) with indexes on both. I want to do a case independent search by city_name. I know I can use like but that'...
2
votes
3answers
731 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to '...
1
vote
2answers
289 views

How to order by alphanumeric codes in SQL Server [closed]

I have a table with some project codes. These codes are e.g.: B001, B002, Z15001, Z14001, P003,... I need this result table: Z15001 Z15002 ... B001 B002 ... C001 C002 ... Z14099 Z14098 Z14097 ... ...
2
votes
1answer
109 views

Regular expression in a SQL case statement

I have records formatted as such: CBS405/505 Fourth Week(Wtr Qtr) BR 404-504-604 Fifth Week(Fall Qtr) AP490-QB-09 Weekly Sessions(Spr Qtr) APR501 Fourth Week(Wtr Qtr) MP 300 Fourth Week(Wtr Qtr) ACX/...
0
votes
1answer
48 views

Restricting multiple records to one by preference?

Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables. SELECT products.UPC, T2.Price1, vProducts_PriceQty_Union.Price2, ...
1
vote
1answer
134 views

Student Attendance, null not returning [closed]

My question is based on the answer given here Attendance: id | student_id | date | ------------------------------ 1 | 2 | 2013-10-24 | Students: student_id | student_name | ---...
7
votes
1answer
355 views

Null Values in a CASE statement

I am playing around with some stuff in SSMS to learn a little more as I study for my 70-461 exam and I came across a little hangup. I am trying to create a table to play around with so I don't have to ...
1
vote
3answers
3k views

A more efficient way to write multiple CASE statements [closed]

SELECT CASE WHEN ( Table.COLUMN1) IS NOT NULL THEN 1 ELSE 0 END, CASE WHEN ( CASE WHEN ( Table.COLUMN1) IS NOT NULL AND ( Table.COLUMN2) IS NOT NULL THEN CASE WHEN ( Table.COLUMN1) - ( Table....
4
votes
0answers
200 views

How to format T-SQL identifiers in camel case [closed]

A number of tools exist which are designed to facilitate formatting the T-SQL in an inherited script or stored procedure to match one's personal preferences. One feature I would like to see in such ...
2
votes
1answer
2k views

How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements. CASE 1 -- GROUP BY and CASE SELECT SQL_NO_CACHE m.id, m.sku, m....
1
vote
0answers
156 views

How to replace empty, multi-column result with single 'none'

If it is possible to make query in Oracle which will return single column/row with value "none" instead of empty, multi-column result? Otherwise return proper amount of columns with result. This is ...
0
votes
0answers
45 views

Where statement involving isnull/case

Here is my query: [SuggestedRetailPrice] > 0 AND (ISNULL( CASE WHEN [AdEnd] < GETDATE() THEN [AdPrice] END, [BasePrice]) )/[SuggestedRetailPrice] > 0.8 ...
0
votes
0answers
17 views

MySQL Case always executes the same [duplicate]

What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is ...
1
vote
2answers
67 views

MySQL Case problem

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The ...
0
votes
1answer
6k views

How do I select data with a case statement and group by?

I'm trying to manipulate data a little by using a case statement but can't seem to figure it out. Stops -------------------- 1Stop 1-Stop 1 stop 1 Stop 1stop 1 Stop 2-Stop Im trying to come up with:...
2
votes
2answers
1k views

Update a date_modified MySQL Record Field ONLY when other fields have changed using CASE statement not working?

I have asked my question on Stackoverflow but have not really gotten any answers so I am trying over here now. I have the MySQL query that is ran in a PHP script below. What it does is Inserts a new ...
2
votes
3answers
354 views

Using expressions in the SELECT list to thin down a CASE statement

I have a query I'm working through rewriting - it has a huge bunch of case statements in the select list that all basically look like this: select Column1 = case when (Something = 2) and ...
1
vote
1answer
130 views

CASE conditions in SELECT statement [closed]

I have the following SELECT statement: SELECT expt_id, iso_stain, test_stain, stain_type CASE WHEN stain_type = 'Blue' THEN iso_stain = 'NA' CASE WHEN statin_type = 'Hematoxylin' THEN ...