How Does This Case Expression Reach the Else Clause

How does this CASE expression reach the ELSE clause?

The written form of the query is expanded to:

Channel = 
      CASE
        WHEN ABS(CHECKSUM(NewId())) % 10 = 0 THEN 'Baby Only'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 1 THEN 'Club'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 2 THEN 'Drug'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 3 THEN 'Food'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 4 THEN 'Internet'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 5 THEN 'Liquidators'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 6 THEN 'Mass'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 7 THEN 'Military'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 8 THEN 'Other'
        WHEN ABS(CHECKSUM(NewId())) % 10 = 9 THEN 'Speciality'
        ELSE '*NONE*'            -- How is this ever getting reached?
      END

A new value for NEWID is used in each test.

Do I need an else clause in a case expression?

You do not need an else clause. If one isn't specified, case will return null. In other words, it acts as though it has an else null clause.

SQL Case statement applies else clause when condition is true

A case expression returns a single type. It must decide between numeric(10, 0) and numeric(10, 3). The latter is the more general, so it will choose that.

If you want to control what the result set looks like, then convert the value to a string:

select (case when col = 'Gallons'
             then cast(cast(Oct as numeric(10, 0)) as varchar(255))
             else cast(cast(Oct as numeric(10, 3)) as varchar(255))
        end) as Oct
from (select 'Gallons' as col, 225.00 as Oct) a 

You can also do this using the str() function.

Is the else keyword, used as final case in a cond-expression, a special form in Scheme?

The Scheme standards call else, as used in a cond form, auxiliary syntax. R6RS shows one possible implementation of cond using syntax-rules; here else is called a <literal>:

(define-syntax cond
  (syntax-rules (else =>)
    ((cond (else result1 result2 ...))
     (begin result1 result2 ...))
;; ...

Note that else is not a replacement for #t. A <literal> is an identifier that is used to match input subforms; it is treated as a syntactic keyword within the syntax-rules form.

CASE expression ent returning ELSE value for every account even when condition is True

I have tried putting the case expression in parenthesis but it still
returns two rows for all of the accounts that should just be returning
'Active'

That's what should happen if the SELECT returns rows for multiple years and quarters. Due to the CASE the current year and quarter (2022-Q1) would be considered 'Active' and prior years would be 'Inactive'.

It sounds like you only want to return the most recent year. Try using ROW_NUMBER() to sort and rank the fiscal quarters for each account. Then use where RowNum = 1 to grab the most recent one. The query below partitions results by geo, account, subsegment, but you can adjust it as needed:

WITH cte AS (
   SELECT * 
          , ROW_NUMBER() OVER(
             PARTITION BY geo, account, subsegment 
             ORDER BY fiscal_year_num DESC, fiscal_qtr DESC
          ) AS RowNum
   FROM   ace_global
   WHERE  revenue > 0
   OR   ( fiscal_year_num BETWEEN 2018 AND 2021
           OR fiscal_qtr = '2022-Q1'
        )
)
SELECT geo
      , account
      , subsegment
      , forecast_group
      , CASE WHEN fiscal_qtr = '2022-Q1' THEN 'Active' ELSE 'Inactive' END AS "Active Acct" 
FROM   cte 
WHERE  RowNum = 1

Sample Data

geo | account     | subsegment | forecast_group | revenue | fiscal_year_num | fiscal_qtr
:-- | :---------- | :--------- | :------------- | ------: | --------------: | :---------
ABC | abc company | australia  | dbiq           |   50000 |            2018 | 2018-Q1   
ABC | abc company | australia  | dbiq           | 1000000 |            2022 | 2022-Q1   
EFG | efg company | australia  | dbiq           |   75000 |            2020 | 2020-Q2   
HIJ | hij company | australia  | dbiq           |  787000 |            2021 | 2021-Q3   
HIJ | hij company | australia  | dbiq           | 2000000 |            2022 | 2022-Q1   

Results:

geo | account     | subsegment | forecast_group | Active Acct
:-- | :---------- | :--------- | :------------- | :----------
ABC | abc company | australia  | dbiq           | Active     
EFG | efg company | australia  | dbiq           | Inactive   
HIJ | hij company | australia  | dbiq           | Active     

db<>fiddle here

Can we write case statement without having else statement

A case expression can only manipulate the value of an expression, not remove rows from the result. If you want to omit the nulls from the result, you'll have to add a where clause:

SELECT CASE WHEN id = 1 THEN 'A'
            WHEN id = 2 THEN 'B'
       END 
FROM   test
WHERE  id IN (1, 2) -- HERE

SQL Case Expression Syntax?

The complete syntax depends on the database engine you're working with:

For SQL Server:

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

or:

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

expressions, etc:

case-expression    - something that produces a value
when-expression-x  - something that is compared against the case-expression
value-1            - the result of the CASE statement if:
                         the when-expression == case-expression
                      OR the boolean-when-expression == TRUE
boolean-when-exp.. - something that produces a TRUE/FALSE answer

Link: CASE (Transact-SQL)

Also note that the ordering of the WHEN statements is important. You can easily write multiple WHEN clauses that overlap, and the first one that matches is used.

Note: If no ELSE clause is specified, and no matching WHEN-condition is found, the value of the CASE expression will be NULL.

can you do an ELSE WHEN on a CASE

Standard SQL:

COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT, 
COALESCE(P.UNIT_CODE, '') AS UNIT_CODE, 
CASE 
   WHEN M.SIGN_DATE IS NULL THEN 'UNCOMPLETED' 
   ELSE 'COMPLETED' 
END AS ASSESSMENTS

If your vendor provides a REPLACE() function:

COALESCE(P.NURSING_UNIT, '') AS NURSING_UNIT, 
COALESCE(P.UNIT_CODE, '') AS UNIT_CODE, 
COALESCE(REPLACE(M.SIGN_DATE, M.SIGN_DATE, 'COMPLETED'), 'UNCOMPLETED') AS ASSESSMENTS

---

Related Topics