July 12, 2017 at 3:11 am
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
July 12, 2017 at 3:27 am
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
July 12, 2017 at 4:29 am
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
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
July 12, 2017 at 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.
July 12, 2017 at 5:53 am
Lorna-331036 - Wednesday, July 12, 2017 5:34 AMThank 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply