Simplifying a CASE statement

Question

Post reply

Simplifying a CASE statement

Lorna-331036

SSC Eights!

Points: 867
More actions
July 12, 2017 at 3:11 am

#322484

Hi I'm new to CASE statements and wondering if the following can be simplified:
SELECT   CASE WHEN ExchangeRate > 0 THEN Sales_Price / ExchangeRate ELSE Sales_Price END AS Sales_Price,
      CASE WHEN ExchangeRate > 0 THEN TransCost / ExchangeRate ELSE TransCost END AS TransCost,
     CASE WHEN ExchangeRate > 0 THEN CostPrice / ExchangeRate ELSE CostPrice END AS CostPrice,
      CASE WHEN ExchangeRate > 0 THEN (Sales_Price / ExchangeRate) * Total_Kilo_Weight ELSE Sales_Price * Total_Kilo_Weight END AS TotalSales,
      CASE WHEN ExchangeRate > 0 THEN (CostPrice / ExchangeRate) * Total_Kilo_Weight ELSE CostPrice * Total_Kilo_Weight END AS TotalCost
FROM    dbo.Sales_Order_Products
Thanks, Lorna

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Lorna

This will reduce the number of CASE expressions. Whether it's actually simpler is for you to decide!

WITH RemoveZeros AS ( SELECT CASE ExchangeRate WHEN 0 THEN 1 ELSE ExchangeRate END AS NonZeroRate , Sales_Price , Transcost , CostPrioce , Total_Kilo_Weight FROM dbo.Sales_Order_Products ) SELECT TransCost / ExchangeRate AS TransCost, CostPrice / ExchangeRate AS CostPrice, (Sales_Price / ExchangeRate) * Total_Kilo_Weight AS TotalSales, (CostPrice / ExchangeRate) * Total_Kilo_Weight AS TotalCost FROM RemoveZeros

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Here's another way - it works exactly the same way as John's code but makes fewer changes to the original:

SELECT

Sales_Price / x.ExchangeRate AS Sales_Price,

TransCost / x.ExchangeRate AS TransCost,

CostPrice / x.ExchangeRate AS CostPrice,

(Sales_Price / x.ExchangeRate) * Total_Kilo_Weight AS TotalSales,

(CostPrice / x.ExchangeRate) * Total_Kilo_Weight AS TotalCost

FROM dbo.Sales_Order_Products

CROSS APPLY (SELECT ExchangeRate = ISNULL(NULLIF(ExchangeRate,0),1)) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lorna-331036 SSC Eights! Points: 867 More actions · Answer 3

Thank you John and Chris. I pasted the code from Chris (as that looked simpler) into a VIEW, but it doesn't work, do I need to declare X?? Sorry, not experienced in using variables :-).
Oh just ran it again, and it works perfectly, it just couldn't express it in table view. Would have deleted this post but couldn't find a way to.

Thom A SSC Guru Points: 99129 More actions · Answer 4

Lorna-331036 - Wednesday, July 12, 2017 5:34 AM
Thank you John and Chris. I pasted the code from Chris (as that looked simpler) into a VIEW, but it doesn't work, do I need to declare X?? Sorry, not experienced in using variables :-).
Oh just ran it again, and it works perfectly, it just couldn't express it in table view. Would have deleted this post but couldn't find a way to.

x isn't a variable, it's an alias. Is this case, Chris has name the result set from the CROSS APPLY "x", and he references the field by using x. .

Aliasing objects in SQL is incredibly useful, and also makes reading it a lot easier. Consider the below:
SELECT MyTableHasARatherLongName.ID, ThisTableAlsoHasARatherLargeName.ID, MyTableHasARatherLongName.CustomerName, ThisTableAlsoHasARatherLargeName.CustomerAddress FROM MyTableHasARatherLongName JOIN ThisTableAlsoHasARatherLargeName ON MyTableHasARatherLongName.ID = ThisTableAlsoHasARatherLargeName.FID;
That SQL isn't exactly friendly. The field names are large and bulky, because of the "long" table names and lack of aliasing. Instead, you could do something like this:
SELECT MT.ID, TT.ID, MT.CustomerName, TT.CustomerAddress FROM MyTableHasARatherLongName MT --Aliased as "MT", as the object name starts with "MyTable" JOIN ThisTableAlsoHasARatherLargeName TT ON MT.ID = TT.FID; --Aliased as "TT" as it starts with "ThisTable"
As you can see, the second example is much easier on the eyes, but you can still easily tell which table each column came from by checking the aliases.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk