Expressions in SQL Views

Question

I am new to SQL views, so be gentle!

I have the following kind of SQL:

SELECT dbo.product.name AS [Product Name], ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) AS [Pack Cost], ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Cost], dbo.purchase.unitsaleprice * dbo.product.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price], dbo.product.units * (dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2)) AS [Pack Profit], dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Profit] FROM dbo.product INNER JOIN dbo.purchase ON dbo.product.id = dbo.purchase.productID 

But that seems ineffective as I rewrite a lot of it.

For example, I would like to define the [Pack Cost] column:

 ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) AS [Pack Cost] 

to use it instead of rewriting it.

for example, so I could use:

 [Pack Cost] / [Units] 

To determine unit cost instead:

 ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Cost] 

Not sure if I get the correct end of the stick, or is it advisable to do so.

Answer 1

Create a new user-defined Scalar-value Function as follows:

 CREATE FUNCTION [dbo].[GetPackCost] ( @vat int ,@packcost decimal ) RETURNS decimal AS BEGIN DECLARE @packcost_calculated decimal SELECT @packcost_calculated = ROUND(CASE @vat WHEN 1 THEN @packcost * 1.2 ELSE @packcost END, 2) RETURN @packcost_calculated END GO 

In your request, you can choose the following:

 SELECT dbo.product.name AS [Product Name], dbo.GetPackCost([vat],[packcost]) AS [Pack Cost], dbo.GetPackCost([vat],[packcost]) / [units] AS [Unit Cost], dbo.purchase.unitsaleprice * dbo.product.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price], dbo.product.units * (dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2)) AS [Pack Profit], dbo.purchase.unitsaleprice - ROUND(CASE [vat] WHEN 1 THEN ([packcost] * 1.2) / [units] ELSE [packcost] / [units] END, 2) AS [Unit Profit] FROM dbo.product INNER JOIN dbo.purchase ON dbo.product.id = dbo.purchase.productID 

Answer 2

You can create a scalar function that would do this logic for you. Then you can just call the function in your view.

Answer 3

Create an intermediate view using calculation logic. Call it (for example) ProductEx. This view may contain a PackCost column calculated and named for you. Then write all your other views on the ProductEx view instead of the Product table.

Answer 4

You can define it in a subquery

 SELECT p.name AS [Product Name], P.[Pack Cost] AS [Pack Cost], Round(P.[Pack Cost] / [units],2) AS [Unit Cost], dbo.purchase.unitsaleprice * p.units AS [ Pack Sale Price], dbo.purchase.unitsaleprice AS [Unit Sale Price], p.units * (dbo.purchase.unitsaleprice - Round(P.[Pack Cost] / [units],2)) AS [Pack Profit], dbo.purchase.unitsaleprice - Round(P.[Pack Cost] / [units],2) AS [Unit Profit] FROM (SELECT *, [Pack Cost] = ROUND(CASE [vat] WHEN 1 THEN [packcost] * 1.2 ELSE [packcost] END, 2) FROM dbo.product) p INNER JOIN dbo.purchase ON p.id = dbo.purchase.productID 

Answer 5

My experience is that the “costs” of acquiring strings are so high compared to the costs of performing simple calculations that I never like. I would say that readability and / or abstraction are of great concern.

In addition, it is likely that your dbms are already performing these optimizations under the hood, but you have to measure both ways to be sure.

Answer 6

Alternatively, if you often do calculations, you can add a highlighted field to the original table called PackCost. then caclis only runs when data is entered or not with every request.