Sql Case Operator

The SQL CASE is used to provide if-then-else type of logic to SQL. It is used to control the execution of other sets of statements.

The SQL CASE statement goes through conditions and return a value or result when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading next condition and return the result or value. If no conditions are true, it returns the value or data in the ELSE clause.

If there is no ELSE statement (is optional) part and no conditions are true, then it returns NULL value.

The SQL CASE can be used in any valid sql statement or expression.

• You can provide any valid sql expression in the condition section.

1. Check to see if average is greater than 80. "58" is not greater than 80, so move on to the next WHEN/THEN
2. Check to see if average is between 60 and 80. "58" is not between 60 and 80, so move on to the next WHEN/THEN
3. Check to see if average is between 40 and 60. "58" is between 40 and 60, so record "C Grade" in the "Grade" column.

According to the Microsoft document, the aim of SQL Server Coalesce and SQL Case statements is to verify expression value. A syntactic shortcut for the CASE expression is the Coalesce expression.

When the calls CASE and COALESCE are interchangeable. The following are the intended actions for this investigation: Replace one with a 'COALESCE' call and the other with a CASE expression.

Syntax:

Syntax of CASE Condition statement and Coalesce function

CASE
WHEN (expression1 is not NULL) THEN expresssion1
WHEN (expression2 is not NULL) THEN expression2
……
ELSE expression
END
SELECT COALESCE (expression1, expression2…) as ‘Result value’

Example:

SELECT
  -- this CASE may be replaced by COALESCE
	CASE
		WHEN C1 IS NOT NULL THEN C1
		ELSE 0
		END
FROM dual;

The CASE statement in the instance can be replaced with SELECT COALESCE(C1, 0), which yields the same results.

Choose Prefer CASE expressions over COALESCE function on the inspection page if you choose using CASE expressions.

Example 1: combine a CASE expression with a concatenation operator:

SELECT prodID, CASE prodSTATUS 
    WHEN 1 
    THEN 'SENT' 
    ELSE 'BACK ORDER' 
    END || ' STATUS'
   FROM t1;

Example 2: There are various fields with 1 or 0 values. Each column name represents the name of one of our products. So, just as with "FirstName + '' + LastName," I'd like you to aggregate all of these fields into one field.

CASE
 WHEN column1 = 1 THEN 'column1'
 ELSE ''
END
 + ' '
 + CASE
 WHEN column2 = 1 THEN 'column2'
 ELSE ''
 END as ConcatenatedColumn

SQL counts a column while also including a case statement, resulting in a count with case query.

Example 1: There are many ways to count and summarize with a case statement:

select
    first_name|| ' ' || last_name "NAME",
   case when upper(attk.status) like 'IN PROCESS%'
      then count(attk.status) else 0 end "IN PROCESS",
   case when upper(attk.status) like 'PENDING%'
      then count(attk.status) else 0 end "PENDING",
   case when upper(attk.status) like 'CLOSE%'
      then count(attk.status) else 0 end "CLOSE"
from mytab;

Example 2: Query for CASE WHEN to set condition and count :

select count( case when Score=45 then 1 else NULL end) as  SpecificCondition from DemoTable1374;

Output :

Example 3: count distinct records( eg:count distinct column1||column2) using the case statement.i could able to count total records in a group using following sample query but its including the duplicate records.

select dptno,count (case when condition1 then 1 end )cnt1,
count (case when condition2 then 1 end )cnt2,
count (case when condition3 then 1 end )cnt3
from emp
group by dptno

In this blog post titled "Different data data types in case expression," we'll look at how CASE expression behaves when dealing with various data types in the THEN (true) and ELSE (false) parts. This post's conversation will be limited to data conversion issues alone.

Example 1: A case statement can only return one data type. So convert the numbers to strings:

SELECT CASE
  WHEN fourthlevel.case_type IN ('Complaint')
  THEN
 (SELECT cast(COUNT(*) as varchar2(255))
   FROM work_days1
   WHERE     work_days1.business_date > fourthlevel.cdate
 AND work_days1.business_date <=
  COALESCE (fourthlevel.close_date, SYSDATE))
 WHEN fourthlevel.case_type IN ('Enquiry')
 THEN
 (SELECT cast(COUNT(*) as varchar2(255))
   FROM work_days1
   WHERE work_days1.business_date > fourthlevel.create_date
 AND work_days1.business_date <=
   COALESCE (fourthlevel.close_date, SYSDATE))
  WHEN fourthlevel.case_status = 'Cancelled'
 THEN
 'N/A'
END AS sla_days
FROM fourthlevel

When the two requirements do not meet, you could also return NULL.

Example 2: Handling different data types in CASE:

To deal with such situations, we can type cast both portions of the CASE expressions (THEN and ELSE) into the same data type, as seen below.

After type cast – In case true part returns

DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 1 THEN CAST(@RetValTHEN AS VARCHAR(50)) ELSE CAST(@RetValELSE AS VARCHAR(50)) END

Example 1: We have this sales table recording the name of item sold and name of the customer who bought it.

item		customer_name
book		Alex
pen		    Bob
book		Alex
book		Jim
shoes		Jim
pen		    Bob
backpack	Bob
book		Bob
wallet		Alex
wallet		Alex
book		Bob
backpack	Jim

A customer might purchase an item multiple times, so to count the number of unique item purchased by each customer:

SELECT
  COUNT(DISTINCT CASE WHEN customer_name = 'Alex' THEN item END) AS by_Alex,
  COUNT(DISTINCT CASE WHEN customer_name = 'Bob' THEN item END) AS by_Bob,
  COUNT(DISTINCT CASE WHEN customer_name = 'Jim' THEN item END) AS by_Jim
FROM
	sales;

Output:

Example 2: Include DISTINCT in your CASE WHEN statements with COUNT. Take a look:

SELECT
  COUNT(DISTINCT CASE
    WHEN pay_date BETWEEN '2015-06-01' AND '2015-06-05'
      THEN candidate_id
  END) AS accepted_student,

  COUNT(DISTINCT CASE
    WHEN pay_date = '2015-06-06'
      THEN candidate_id
  END) AS conditionally_accepted_student,

  COUNT(DISTINCT CASE
    WHEN pay_date > '2015-06-06'
      THEN candidate_id
  END) AS not_accepted
FROM application;

I counted the number of approved, conditionally acceptable, and rejected payments. Now we're going to count how many candidates were accepted for at least one degree course, how many were conditionally approved for at least one degree course, and how many were not permitted for at least one degree course.

We should have included the keyword DISTINCT since a single candidate can apply for several degree courses, thus if a candidate paid on time for two courses, that candidate would be included twice (the candidate could still be counted twice in two distinct columns if the candidate paid for one degree course but did not pay for another, but that's another matter).

Example 3: Trying to do a case on a distinct value and it isn't going well:

SELECT
CASE WHEN DISTINCT(Response_Master_Incident.Battalion) = '' THEN 'Unknown'
    ELSE DISTINCT(Response_Master_Incident.Battalion) END AS 'Zone'

Example 1: Using EXISTS clause in the CASE statement to check the existence of a record:

DECLARE @CustId INT = 2
SELECT (CASE WHEN EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
    WHERE CustId = @CustId) THEN 'Record Exists'
    ELSE 'Record doesn''t Exists' END) AS [Employee?]

Example 2: Oracle's Case-When-Exists expression is really useful. Here's an instance of how to return a status using it in a sub-select. To establish the person status, this SQL looks for a match between the PS PERSON and PSOPRDEFN records. The concept is that if an operator isn't in PS PERSON, they aren't a legitimate PeopleSoft person.

select O.OPRID,
    O.EMPLID,
    case when exists (
        select 1
        from PS_PERSON P
        where P.EMPLID = O.EMPLID
    ) then 'Person' else 'Not a Person' end as PERSON_STATUS
from PSOPRDEFN O;

Example 3: CASE EXISTS :

SELECT * FROM dbo.CompanyMaster
WHERE AreaId=
(CASE WHEN EXISTS (SELECT BusinessId
      FROM dbo.AreaSubscription
      WHERE AreaSubscription.BusinessId = CompanyMaster.BusinessId) 
      THEN @AreaId ELSE AreaId END)
AND  AreaId IN (SELECT [@Areas].AreaId FROM @Areas)

To limit the rows returned by the SELECT operation, utilize the CASE expression in a HAVING clause.

Example 1: The HumanResources statement returns the maximum hourly wage for each job title. Table for employees. The HAVING clause limits the titles to those held by males earning more than $40 per hour or women earning more than $42 per hour.

USE AdventureWorks2008R2;
GO

SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = ‘M’
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender  = ‘F’
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

Example 2: HAVING clause with CASE

USE AdventureWorks2008R2;
GO
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 
    ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M' 
    THEN ph1.Rate 
    ELSE NULL END) > 40.00
     OR MAX(CASE WHEN Gender  = 'F' 
    THEN ph1.Rate  
    ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;

From top to bottom, a CASE statement and a sequence of IF/ELSE IF statements are assessed. Only one branch will be performed at most: the first one with a true condition.

Example 1: CASE inside IF ELSE. Below is the example MS-SQL code

DECLARE @Flight_Ticket int;
SET @Flight_Ticket = 190;
IF @Flight_Ticket > 400
   PRINT 'Visit Nearby Tourist Location';
ELSE 
BEGIN
    SELECT
	CASE 
	WHEN @Flight_Ticket BETWEEN 0 AND 100 THEN 'Visit Los Angeles'
	WHEN @Flight_Ticket BETWEEN 101 AND 200 THEN 'Visit New York'
	WHEN @Flight_Ticket BETWEEN 201 AND 400 THEN 'Visit Europe'
	END AS Location	
END

In the above example CASE is NESTED inside IF…ELSE statement:

If the Case Condition in SQL Server is False, the IF statement will be executed first, followed by the ELSE statement.

Example 2: Duplicating a condition will always result in dead code. A copy/paste error is usually to blame. At most, it's just dead code; at worst, it's a flaw that will almost certainly cause other defects as the code is managed, and it might obviously cause unexpected behaviour.

Compliant Solution

IF @x = 1
  PRINT 'A'
ELSE IF @x = 2
  PRINT 'B'
ELSE IF @x = 3
  PRINT 'C'

SELECT
  CASE col1
    WHEN 1
      THEN 'A'
    WHEN 2
      THEN 'B'
    WHEN 3
      THEN 'C'
    ELSE 'D'
  END
FROM table1

Example: You appear to be attempting to create Where-clauses in the case, but you should alternatively compare the case's outcome to Call_Type_ ID (or any other field you like). I also use brackets above my casing to make it easier to understand where they begin and end.

INNER JOIN datamartend.dbo.Call_Type_Dim ON 
(CASE 
 WHEN  CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0
     AND CallState IS NULL
     THEN 10
WHEN CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned  IS NULL
     AND AnsTime > 0  
     AND CallState = 1
     THEN 11
WHEN 
     CTICallType_ID = 1
     AND CTIAgentCallType_ID = 0
     AND Abandoned = 1
     AND AnsTime IS NULL
     AND CallState IS NULL
     THEN 12
ELSE 1
END) = Call_Type_ID  -- Insert something here to join on.

In SQL, the IS NOT NULL condition is used to check for a value that is not NULL. If a non-NULL value is detected, TRUE is returned; otherwise, FALSE is returned. In a SELECT, INSERT, UPDATE, or DELETE statement, it can be used.

This occurs because the values IS NULL and IS NOT NULL cannot be compared. So, the proper syntax is CASE WHEN, followed by the conditional expression including IS NULL or IS NOT NULL.

Syntax:

CASE and IS NOT NULL syntax.

CASE
WHEN (ID IS NULL)
   THEN 'YES'
WHEN (ID IS NOT NULL)
   THEN 'NO'
END AS ID_Value,

Example: We must say that this technique wouldn’t work in all cases, though. For instance, consider the following query:

SELECT  e.emp_no,
    e.first_name,
    e.last_name,
    CASE
        WHEN dm.emp_no IS NOT NULL THEN 'Manager'
        ELSE 'Employee'
    END AS is_manager
FROM
    employees e
        LEFT JOIN
    dept_manager dm ON dm.emp_no = e.emp_no
 WHERE
     e.emp_no > 109990;

If no TRUE value/condition is detected, the CASE statement returns the value specified in the ELSE clause. The CASE statement will return NULL if the ELSE clause is missing and no condition is discovered to be true.

The result sets are based on a data comparison with some values, and any values in the result set that are null must be replaced with an empty string acquired using the ISNULL function.

Example 1: If a column has blank values, -, and NULL values, and only regular text values in 0, then it is considered a blank column. I can only do the first two or the NULL values, not both. I tried putting ISNULL ([Family Level 2],0) in a separate case statement, as well as the existing case statement and a when statement.

CASE  
WHEN column IS NULL THEN 'value'
WHEN column = 'value2' THEN 'valuexx'
ELSE 'othervalue'
END;

Example 2: We may use the CASE structure to accomplish this, however the CASE fieldname WHEN technique does not allow us to call a function, therefore we must use the CASE WHEN fieldname condition instead:

SELECT Name,
        CASE WHEN color = 'black' THEN 'b'
            WHEN color = 'red' THEN 'r'
            WHEN color IS NULL THEN  'empty'
            else 'n/a'
           END AS Color_code
FROM SalesLT.Product;

Example 3: This should be included in a SELECT query with a CASE expression. I coded something similar, except the column's result set returns 0 instead of an empty string. This column ABC's data type is varchar(10):

SELECT top 20
        ABC
	   ,CASE 
        WHEN ABC IN (8,9,10,13,14,15,16,17,19,20,21) THEN 10
		WHEN ABC IN (31,32,33) THEN 30
		WHEN ABC IN (53,52,54,55) THEN 50
		WHEN ABC IN (61,62) THEN 60
		WHEN (ABC IS NULL AND DEF IS NOT NULL) THEN 99
		else ISNULL(ABC,'')
		END
FROM XYZ 

After ORDER BY, use CASE to check for column values. You can also utilise ORDER BY with CASE statements that are simple and searched.

The ORDER BY clause can include the CASE statement, which then tests for column values. The ORDER BY clause in SQL is used to sort the results either ascending or descending.

Example 1: The following example demonstrates how to sort data using CASE. Consider retrieving data from the furniture data database, but only the rows with an even number of points should be shown at the top of the results. This cannot be accomplished using columns as sorting criteria; instead, CASE is required.

SELECT id, furniture, points
FROM furniture _data
ORDER BY CASE 
              WHEN points%2=0  THEN 1
              ELSE 2
         END ;

Output:

id	furniture	points
2	sofa		8
3	table		2
1	chair		5
4	bookcase	5
5	bed		    3

It determined if the number in the points column could be divided by two without leaving a remainder. If it could, it returned a 1 and the row was displayed at the top of the results.

Example 2: we use the CASE after ORDER BY and then checks for column value:

SELECT * FROM PersonalDetails
ORDER BY
       CASE Active WHEN 1 THEN Active END ASC,
       CASE WHEN Active = 0 THEN LastName
       ELSE FirstName END DESC

In above case, all records having Active = 1 is sorted on “Active ASC” order. All records having Active = 0 is sorted on ‘LastName DESC’ else ‘FirstName DESC’ order.

Example 3: SELECT with ORDER BY CASE

The ORDER BY and CASE statements are used in conjunction in the following SQL query.

SELECT * FROM Country
ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END

Output:

ID	cname
1	Afghanistan
2	Australia
3	France
4	Oman
6	Singapore
7	United States
5	Other

The ORDER BY clause combined with CASE moves the "Other" option to the bottom. This reasoning can be used for other things besides country, such as university name, city, state, and so on.

Example 4: Continuing with the sales manager's request, imagine she also wants to see the products sorted by price range, then by product name. We've seen how to make the price ranges appear as a column, but how do we arrange them?

Because CASE is an expression, it can be used as one of the values used to sort the results. Remember that we can sort an expression as well as table columns.

Here is the query to sort by the price range.

SELECT   Name,
         ListPrice
FROM     Production.Product
ORDER BY CASE
            WHEN ListPrice = 0 THEN 'No Price'
            WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
            WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
            WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
            WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
            WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
            WHEN ListPrice > 2000 THEN 'Luxury'
            ELSE 'UNLISTED'
         END,
         Name

Use a subquery with a 'where' condition inside a Case statement to bind to the parent query.

Example 1: use SUBQUERY in the Case Statement :

CASE
WHEN (SELECT COUNT(MEDAL) FROM Table
= 10 THEN 'GOOD '
WHEN (SELECT COUNT(MEDAL) FROM Table
= 5 THEN 'POOR'

END
AS Rank

Example 2:

select
 case when salary between 6 and 8 then '6-8'
      when salary in (9,10)       then '9-10'
      when exists (select null from avg_sal where avg_sal = salary)
      then 'EXISTS'
      when to_char(salary) like '2%' then 'Like2'
      when salary is null then 'Null'
      else 'ELSE Empno: '|| emp_no
      end
      AS case_test
    from emp

Output:

Example 3:

Select T.idperson , CASE WHEN  T.type = 'C' THEN (SELECT name from Customers where C.idcustomer = T.idperson) 
ELSE
 (SELECT name from Providers where idprovider = T.idperson)
 END Name 
from myTable T

You know that CASE WHEN can yield numbers because of the method SUM() and the constructs CASE WHEN.

Example 1: Use SUM and CASE in the SQL statement.

To fix this, use MySQL's SUM and CASE functions on the statement. As a result, the statement should be:

SELECT c.id, c.name, 
    SUM(CASE WHEN p.status=1 THEN 1 ELSE 0 END) as product_count
FROM product_categories c left join products p on c.id=p.category_id
GROUP BY c.id, c.name

Output:

id	name		product_count
1	category A	1
2	category B	2
3	category C	0
4	category D	0

Example 2: There are many ways to count and summarize with a case statement, To work with sum Aggregared function:

select
    first_name|| ' ' || last_name "NAME",
   sum(case when upper(attk.status)
    like 'IN PROCESS%'
    then 1 else 0 end) "IN PROCESS",
 sum(case when upper(attk.status)
    like 'PENDING%'
    then 1 else 0 end) "PENDING",
 sum(case when upper(attk.status)
     like 'CLOSE%'
   then 1 else 0 end) "CLOSE"
from mytab;

Example 3: Let's combine all of this data to create a query that can count many groups of rows at once. Take a look:

SELECT
  SUM(CASE
    WHEN scholarship IS TRUE THEN place_limit
    ELSE 0
  END) AS scholarship_places,

  SUM(CASE
    WHEN scholarship IS FALSE THEN place_limit
    ELSE 0
  END) AS no_scholarship_places
FROM course;

The query above counts two SUMs: the total number of spots available in all courses that grant scholarships and the total number of places available in those that do not.

We have a CASE WHEN statement inside SUM. We add the value from the column place_limit to the total named scholarship_places when the value in the column scholarship is true. We add 0 else. In the same fashion, the other SUM is computed.

Receive session information, but just for the current day. Sessions must be organised by their start time in descending order. We'll need an ascending sequence for the rest of the days.

The column alias should be inserted after the CASE expression's END clause, as the date format string is incorrect.

Example 1: You could try the following to achieve the result:

desc info

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
START_DATE                                         DATE
END_DATE                                           DATE
AMOUNT                                             NUMBER

select * from info;

Output:

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 14-SEP-16       1200
14-SEP-16 30-JUN-16       1300
30-MAY-16 30-JUN-16       1300
14-SEP-16 20-JUL-16       1400

select start_date,
case 
when end_date > to_date('2016-06-30', 'yyyy-mm-dd') then to_date('06-30-2016', 'MM/DD/YYYY') end as end_date, amount from info 
order by end_date asc;  2    3    4  

Output:

START_DAT END_DATE      AMOUNT
--------- --------- ----------
30-JUN-16 30-JUN-16       1200
14-SEP-16 30-JUN-16       1400
30-MAY-16                 1300
14-SEP-16                 1300

Example 2: To solve this task, we can use CASE logic and using DATE in the ORDER BY clause and get the desired result easily:

USE TestDB
GO
 
SELECT *
FROM [Session]
ORDER BY CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))=0 THEN StartTime END DESC,
CASE WHEN DATEDIFF(DAY, CAST(StartTime AS Date),CAST(N'2019-11-28' AS Date))>0 THEN StartTime END ASC

The query returns the column 'Databasename' from the derived list values clause to evaluate Null using the two Case expressions; example 1 has a value of Null, and example 2 has a value of 'SQLUndercover,' as shown below:

When a statement is fulfilled, the program will cease reading and return the result. If none of the requirements are met, the value in the ELSE clause is returned. It returns NULL if there is no ELSE component and no criteria are met.

Example 1:

SELECT Databasename,
CASE Databasename
    WHEN NULL THEN 'It''s a NULL'
    WHEN 'SQLUndercover' THEN 'Match Found'
    ELSE 'No match Found'
END AS CaseExample1,
CASE
    WHEN Databasename IS NULL THEN 'It''s a NULL'
    WHEN Databasename = 'SQLUndercover' THEN 'Match Found'
    ELSE 'No match Found'
END AS CaseExample2
FROM (VALUES(NULL)) AS DerivedList (Databasename)

Example 2: If the state is not NULL, the CASE expression is used to sort customers by states; if the state is NULL, the CASE expression is used to sort customers by country:

SELECT customerName, 
    state, 
    country
FROM
    customers
ORDER BY (
    CASE
	WHEN state IS NULL 
            THEN country
	ELSE state
END);

The SUM(CASE WHEN x THEN 1 ELSE 0) used for multiple columns.

The WHERE clause in PostgreSQL contains a CASE statement. CASE expressions can be used anywhere where two values are compared, such as SELECT, WHERE, GROUP BY, and HAVING clauses. So let's look at a CASE statement in the WHERE clause as an example.

Example 1: PostgreSQL CASE expression in WHERE clause:

SELECT * 
FROM Employee
WHERE Emp_ID = CASE WHEN Dept='IT' THEN 1
ELSE 0
END;

In this example, we have compared the Emp_ID with the value generated by the CASE expression to find the employee whose department is ‘ IT ‘ and its Emp_ID is ‘ 1 ‘.

Example 2: To illustrate how I can achieve the same result as when I use simply WHERE, I'll use the CASE statement inside of COUNT.

SELECT COUNT(email)
FROM users
WHERE email LIKE ‘%.com’;

And 1000 results by using this:
SELECT COUNT(
CASE
WHEN email LIKE ‘%.com’ THEN 1 ELSE 0 END)
FROM users;

Example 3: I'm looking to see if there is a better approach to the query below. What I'm trying to do is create a summary report, compiling stats by date.

SELECT CAST(Detail.ReceiptDate AS DATE) AS 'DATE'
, SUM(CASE WHEN Detail.Type = 'TotalMailed' THEN 1 ELSE 0 END) AS 'TOTALMAILED'
, SUM(CASE WHEN Detail.Type = 'TotalReturnMail' THEN 1 ELSE 0 END) AS 'TOTALUNDELINOTICESRECEIVED'
, SUM(CASE WHEN Detail.Type = 'TraceReturnedMail' THEN 1 ELSE 0 END) AS 'TRACEUNDELNOTICESRECEIVED'
FROM
(
select SentDate AS 'ReceiptDate', 'TotalMailed' AS 'Type'
from MailDataExtract
where sentdate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TotalReturnMail' AS 'Type'
from MailDataExtract MDE
where MDE.ReturnMailDate is not null
UNION ALL
select MDE.ReturnMailDate AS 'ReceiptDate', 'TraceReturnedMail' AS 'Type'
from MailDataExtract MDE
    inner join DTSharedData.dbo.ScanData SD ON SD.ScanDataID = MDE.ReturnScanDataID
where MDE.ReturnMailDate is not null AND SD.ReturnMailTypeID = 1
) AS Detail
GROUP BY CAST(Detail.ReceiptDate AS DATE)
ORDER BY 1  

Boolean expressions or comparison operators are used in the Searched Case expression.

Only one value is returned by the subqueries.

The abbreviated version of CASE compares a set of data against an expression (often a column). This version is a little shorter and eliminates the need to repeatedly evaluate the evaluated expression. The ELSE clause is still available.

The case statement returns scalar values when there is an outside comparison such as = or >.

Syntax:

The general syntax of the searched CASE expression is:

CASE
   WHEN condition_1 THEN result_1
    [WHEN condition_2 THEN result_2]
   [..................]
   [WHEN condition_n THEN result_n]
   [ELSE expression]
[else result_n]
END

Example 1: A scalar value is "equivalent" to a row with one column and one value. As a result, the following would be permitted:

where col = (CASE WHEN date > '2014-02-28' THEN (SELECT max(col2) FROM TABLEC WHERE...)
    ELSE (SELECT min(col3) FROM TABLE B WHERE...)
   END)

Example 2:

SELECT Id, ItemId, Price,
  CASE Price WHEN 5  THEN 'CHEAP'
             WHEN 15 THEN 'AFFORDABLE'
             ELSE         'EXPENSIVE'
  END as PriceRating
FROM ItemSales

A word of warning: When utilising the short option, keep in mind that the complete statement is evaluated at each WHEN. As a result, the following statement:

SELECT 
    CASE ABS(CHECKSUM(NEWID())) % 4
        WHEN 0 THEN 'Dr'
        WHEN 1 THEN 'Master'
        WHEN 2 THEN 'Mr'
        WHEN 3 THEN 'Mrs'
    END

In SQL, the Case statement is typically used when equality expressions are present. To change the output, the SQL Case statement is commonly placed inside of a Select list. Another option is to put the alias name first, followed by equals SQL Case.

MySql and MsSql will not allow this because they will try to match all columns in the CASE clause to tables in the WHERE clause with the same ALIAS name.

Example 1: Inside the same query, column alias references are not enabled; alternatively, you should create a subquery:

SELECT
 q.*,
 CASE WHEN q.flag = 1 THEN 'Y' END AS COL2
FROM (
 SELECT
   F AS flag
   FROM table
) AS q

Alternatively, if the alias is simple, you can utilize the column directly instead of the alias, as illustrated below.

SELECT
   F AS flag,
CASE WHEN F = 1 THEN 'Y' END AS COL2
   FROM table

NOTE: The column alias resolution is presently not handled by the Roboquery converter. It must be completed manually.

Example 2: alias a column name and then use that in a CASE statement:

SELECT col1 as a, CASE WHEN a = 'test' THEN 'yes' END as value FROM table;

I'm trying to alias the column since my CASE statement will be generated programmatically, and I'd like the column that the case statement utilizes to be stated in the SQL rather than supplying additional input to the program.

Example 3: I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:

SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (
   SELECT col1 as a FROM table
) q

In SQL, the case statement returns a value based on a condition. In select queries, we can utilise a Case statement in conjunction with the Where, Order By, and Group By clauses. It can also be used in an Insert statement.

When copying/pasting the full CASE statement into the GROUP BY clause, omit the AS year group column naming:

Example 1: If a set of data is required by different organisations inside the company in slightly different contexts. This, like many of my example queries, should be written better. It, like many of my sample queries, reflects what I observe in the wild (and for those keeping track at home, I'm now testing with the WideWorldImporters database):

CREATE PROCEDURE dbo.InvoiceGrouping (@x INT)
AS
SELECT  SUM(il.UnitPrice),
        COUNT(i.ContactPersonID),
        COUNT(i.AccountsPersonID),
        COUNT(i.SalespersonPersonID)
FROM    Sales.Invoices AS i
JOIN    Sales.InvoiceLines AS il
        ON il.InvoiceID = i.InvoiceID
GROUP BY CASE WHEN @x = 7 THEN i.ContactPersonID
              WHEN @x = 15 THEN i.AccountsPersonID
              ELSE i.SalespersonPersonID
         END;
GO

Depending on the column used in the GROUP BY, if you run this for any value above 7, 15, or other, you'll receive the same execution plan. Parameter Sniffing, on the other hand, is still a factor. You only get 10 rows back when you group this data by SalesPersonID. If a parameter value other than 7 or 15 is provided, this will be displayed as the estimated number of rows returned.

Example 2: In this article, we would explore the CASE statement and its various use cases.

SELECT CASE WHEN year = 'FR' THEN 'FR'
            WHEN year = 'SO' THEN 'SO'
            WHEN year = 'JR' THEN 'JR'
            WHEN year = 'SR' THEN 'SR'
            ELSE 'No Year Data' END AS year_group,
            COUNT(1) AS count
  FROM benn.college_football_players
 GROUP BY CASE WHEN year = 'FR' THEN 'FR'
               WHEN year = 'SO' THEN 'SO'
               WHEN year = 'JR' THEN 'JR'
               WHEN year = 'SR' THEN 'SR'
               ELSE 'No Year Data' END

Example 3: Anyway, the good news is that we can make summary groups with the CASE expression we've created. The data is grouped by PriceRange in the following SQL. The minimum, maximum, and average ListPrice data are calculated.

SELECT
   CASE  WHEN ListPrice = 0 THEN 'No Price'
         WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
         WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
         WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
         WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
         WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
         WHEN ListPrice > 2000 THEN 'Luxury'
         ELSE 'UNLISTED'
         END as PriceRange,
         Min(ListPrice) as MinPrice,
         Max(ListPrice) as MaxPrice,
         AVG(ListPrice) as AvgPrice,
         Count(ListPrice) as NumberOfProducts
 FROM    Production.Product
 GROUP BY  CASE
         WHEN ListPrice = 0 THEN 'No Price'
         WHEN ListPrice > 0 AND ListPrice <= 50 THEN 'Low'
         WHEN ListPrice > 50 AND ListPrice <= 150 THEN 'Medium'
         WHEN ListPrice > 150 AND ListPrice <= 500 THEN 'High'
         WHEN ListPrice > 500 AND ListPrice <= 1000 THEN 'Very High'
         WHEN ListPrice > 1000 AND ListPrice <= 2000 THEN 'Highest'
         WHEN ListPrice > 2000 THEN 'Luxury'
         ELSE 'UNLISTED'
         END
ORDER BY MinPrice

We can't utilize the column alias PriceRange in the GROUP BY clause, unlike the ORDER BY clause. It's necessary to repeat the complete CASE phrase.

Example 4: To compute the overall count of products such as "SQL" and "Oracle," use the query below, which includes a Group By clause and a CASE statement:

SELECT
CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END Products ,
SUM(Count) OverallCount
FROM @Tmp
GROUP BY CASE WHEN Products LIKE '%SQL%' THEN 'MSSQL'
              WHEN Products LIKE '%ORACLE%' THEN 'Oracle'
              ELSE NULL END

Example 1: A CASE expression returns a value from the THEN portion of the clause and it join the case with some conditions:

SELECT  * 
FROM    sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

Example 2: A CASE expression returns a value from the THEN portion of the clause.

Relationship between sys.partitions and sys.allocation_units depends on the value of sys.allocation_units.type. So to join them together with case statement I would write something similar to this:

SELECT  * 
FROM sys.indexes i 
    JOIN sys.partitions p 
        ON i.index_id = p.index_id  
    JOIN sys.allocation_units a 
        ON CASE 
           WHEN a.type IN (1, 3) AND a.container_id = p.hobt_id THEN 1
           WHEN a.type IN (2) AND a.container_id = p.partition_id THEN 1
           ELSE 0
           END = 1

This SQL example uses a select query with a where clause. Now I need to add a condition depending on user access to the where clause. If the user does not have access, an extra condition must be added to the where clause; otherwise, if the user has access, no more logic is required.

Example 1: You can use CASE in WHERE as Shown below:

WHERE 1=(
        CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
             WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
             WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
             ELSE 0 END
            )

this will not return any row if @x=1 and @y=1. If you want to return all rows if @x=1 and @y=1

WHERE 1=(
         CASE WHEN @X = 0 and @y = 0 and pagecode not in ('admin','external') THEN 1
              WHEN @x = 0 and @y = 1 and pagecode not in ('admin') THEN 1
              WHEN @x = 1 and @y = 0 and pagecode not in ('external') THEN 1
              WHEN @x = 1 and @y = 1 THEN 1
              ELSE 0 END
            )

Example 2:

SELECT * FROM mytransactions m 
WHERE mytransactions.id NOT IN 
	CASE WHEN @OnlyNonExported = 0 
		THEN  (SELECT -1) 
		ELSE  (SELECT id FROM trackedtransactions) 
	END

However, SQL does not appreciate this syntax, and it turns out that you can only employ = or != conditions in a WHERE clause when using a CASE statement.

Example 1: SELECT option used inside CASE WHEN clause:

SELECT (CASE WHEN ISNULL(s.rate, 0) = 0 
             THEN (SELECT TOP 1 pr.rate
                   FROM ProjectRates pr
                   WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE())
                   ORDER BY pr.effectiveDate DESC
                  )
             ELSE (SELECT TOP 1 sr.rate
                   FROM ShiftRates sr
                   WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE()) 
                   ORDER BY pr.effectiveDate DESC                
                  ) --s.rate 
        END) AS rate
FROM Projects p INNER JOIN
     Shifts s 
     ON p.ID = s.projectID
WHERE p.ID = @projectID;

Example 2: Select statment with in a CASE Statement:

SrTeamManagerDate
,'TeamMentor' =
CASE
WHEN TeamMentorDate IS NOT NULL THEN 'Team Mentor'
WHEN (SELECT r.AchieveTitle FROM Repromotes r WHERE RepFlag = 'X' AND AchieveTitle = 'Team Mentor) THEN 'Team Mentor*'
ELSE NULL
END

Example 1: For SUBSTRING with CASE statement:

DECLARE @tmp TABLE (FullFileName VARCHAR(100))
INSERT @tmp SELECT 'File_2013-04-21.txt'
INSERT @tmp SELECT 'File_2013-04-21.txt -1'
INSERT @tmp SELECT 'File_2013-04-21.txt -2'

SELECT FullFileName,
SUBSTRING(FullFileName,1,
CASE WHEN (CHARINDEX(' -',FullFileName)) > 0
THEN CHARINDEX(' -',FullFileName)
ELSE LEN(FullFileName) END ) [FileName]
,CASE WHEN CHARINDEX(' -',SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))) > 0
THEN SUBSTRING(FullFileName,CHARINDEX(' -',FullFileName,0),LEN(FullFileName))
ELSE NULL END Filesuffix
FROM @tmp

Example 2: CASE statement with SUBSTRING:

SELECT * FROM (
SELECT
CASE 
  WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN 'A'
  WHEN COL LIKE '%9' THEN 'H'
  WHEN SUBSTRING(COL,2,1) = '4' THEN SUBSTRING(COL,1)
END Letter,
CASE 
  WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN substring(col FROM 2 for 1)
  WHEN COL LIKE '%9' THEN '9'
  WHEN SUBSTRING(COL,2,1) = '4' THEN '7'
END Number
FROM A
) 
WHERE (Letter ~ '^[A-Za-z]$') = true AND (Number ~ '^[0-9]$') = true;

You might be able to use a CASE statement instead of a UNION when combining data from different searches.

Example: This simple SQL statement combines the results of three queries against the table using UNION.

select t1.itemcode, t1.y1qty, t1.yr2qty,
 CASE 
    WHEN y1qty = 0 AND yr2qty = 0 
        THEN blah blah
    WHEN y1qty = 0 and y2qty >  
        THEN blah blah
    ELSE blah blah
END AS Diff
FROM
(
     SELECT a.itemcode, 
     SUM(b.quantity) AS 'y1qty', 
     ISNULL(NULL,0) AS 'yr2qty', 
     SUM(c.amt) AS 'yr1amt' 
     ISNULL(null,0) AS 'yr2amt',
     FROM ABC a
    LEFT JOIN DEF b ON a.itemcode = b.itemcode
     GROUP BY a.itemcode) t1
UNION 

select t2.itemcode, t2.y1qty, t2.yr2qty,
 CASE 
    WHEN y1qty = 0 AND yr2qty = 0 
        THEN blah blah
    WHEN y1qty = 0 and y2qty >  
        THEN blah blah
    ELSE blah blah
END AS Diff
FROM
(
     SELECT  a.itemcode, 
   ISNULL(NULL,0) AS 'yr1qty',
    SUM(b.quantity) AS 'yr2qty', 
   ISNULL(NULL,0) AS 'yr1amt',
   SUM(c.amt) AS 'yr2amt' 
   FROM ABC a
  LEFT JOIN DEF b ON a.itemcode = b.itemcode
   GROUP BY a.itemcode
    ) t2

Use a CASE Statement whenever a valid expression is used in the SELECT statement, like the filter criteria in the WHERE clause.

In the SELECT clause, we can utilize the Case Statement, as most DBAs and developers do. The Case Statement can also be used in the WHERE clause.

The value is returned by the CASE statement based on the condition. In the Where, Order by, and Group by clauses, we can utilize a case statement.

Example 1: A Case Statement is useful for selecting a value from a list of possible values. Consider the case if we wished to base the rental_duration on the rental_rate. We could use a Case Statement to accomplish this:

SELECT film_id, title, rental_rate, rental_duration 
FROM film 
WHERE rental_duration = CASE rental_rate
	       WHEN 0.99 THEN 3
	       WHEN 2.99 THEN 4
	       WHEN 4.99 THEN 5
	       ELSE 6
          END 
ORDER BY title DESC;

Example 2: Let's say we want to get all people with the persontypes VC or IN from the Persons table. You might use CASE to accomplish this:

SELECT FirstName, LastName, PersonType
FROM   Person.Person
WHERE  1 = CASE
              WHEN PersonType = 'VC' THEN 1
              WHEN PersonType = 'IN' THEN 1
              ELSE 0
           END

PersonType will be tested for either VC or IN in this case. Return a 1 if it matches the CASE WHEN. The row is included in the result after the filter matches.

Example 3: If the first name is 'RAM' or the last name is 'SHARMA,' I want to show the salary in the Customer table:

SELECT *
FROM [dbo].[Customer]
WHERE [Salary] =
CASE
WHEN [First_Name] = ‘RAM’ THEN [Salary]
WHEN [Last_Name] = ‘SHARMA’ THEN [Salary]
ELSE NULL
END

The salary of the customer with the first name Ram or the last name Sharma is presented in the Customer table.

The result is displayed by utilising a CASE statement with the where condition.