CODEWITHSUNDEEP
sqlsql-servert-sql
nickc
nickc

Reputation: 49

SQL Nested CASE Statement

I am trying to update the following query segment in a Sales Analysis cube. I'm not deeply familiar with SQL.

The first CASE statement works as it should - returns a negative when OINV.Canceled = 'Y'.

CASE OINV.Canceled WHEN 'Y' THEN - INV1.LineTotal 
ELSE INV1.LineTotal END AS LineTotal, 
CASE INV1.StockSum WHEN 0 THEN INV1.LineTotal 
ELSE INV1.StockSum END AS SaleValue, INV1.GrssProfit

What I'm trying to achieve is for the second CASE statement to also return a negative value (SaleValue) when OINV.Canceled = 'Y'. I think I need to nest a CASE statement here but I'm not sure.

Upvotes: 2

Views: 207

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this:

CASE 
  When OINV.Canceled = 'Y' THEN  INV1.LineTotal *(-1)
  ELSE INV1.LineTotal 
END AS LineTotal, 
CASE 
  When INV1.StockSum = 0 THEN INV1.LineTotal 
  ELSE INV1.StockSum 
END AS SaleValue, 
INV1.GrssProfit

Upvotes: 0

Kaf
Kaf

Reputation: 33839

There are two formats of Case Expression. Simple case and Searched case. You can do it with a Searched case expression.

CASE WHEN  OINV.Canceled = 'Y' THEN -INV1.StockSum 
     WHEN  INV1.StockSum = 0 THEN INV1.LineTotal
     ELSE  INV1.StockSum 
END AS SaleValue

Or it could be (depending on your requirement)

CASE WHEN  OINV.Canceled = 'Y' AND INV1.StockSum = 0 THEN -INV1.LineTotal
     WHEN  OINV.Canceled = 'Y' AND INV1.StockSum <> 0 THEN -INV1.StockSum 
     ELSE  INV1.StockSum 
END AS SaleValue

Or a nested case. Here you have to repeat sub case expression for different values of OINV.Canceled.

 CASE OINV.Canceled WHEN 'Y' THEN
                    -1 * CASE INV1.StockSum WHEN 0 THEN INV1.LineTotal 
                         ELSE INV1.StockSum END
                    ELSE 
                         CASE INV1.StockSum WHEN 0 THEN INV1.LineTotal 
                         ELSE INV1.StockSum END
 END AS SaleValue

Upvotes: 4

Related Questions