CODEWITHSUNDEEP
sqlsql-serverview
Ben
Ben

Reputation: 4319

Expressions in SQL Views

I'm new to SQL Views so be gentle!

I have the following SQL view:

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 it seems inefficient as I am rewriting a lot of this.

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 be used elswhere instead of rewriting it.

e.g so I could use:

[Pack Cost] / [Units]

To define unit cost instead of:

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

Not sure if I'm getting the right end of the stick though, or whether it is appropriate to do this.

Upvotes: 4

Views: 2446

Answers (6)

NakedBrunch
NakedBrunch

Reputation: 49413

Create a new User-Defined Scalar-valued 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 query, you would select is as follows:

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

Upvotes: 3

HLGEM
HLGEM

Reputation: 96572

Alternatively, if you wil be doing the calculation often, you could add a calulated field to the orginal table that is called PackCost. then caclis only done on data entry or change not with every query.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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

Upvotes: 1

Ronnis
Ronnis

Reputation: 12833

My experience is that the "cost" of aquiring the rows is so large compared to the cost of performing simple calculations that I just never care about it. I'd say that readability and/or abstraction is of greater concern.

Plus, it is likely that your dbms already perform those optimizations under the hood, but you should measure both ways to be sure.

Upvotes: 0

Larry Lustig
Larry Lustig

Reputation: 50970

Create an intermediate view with the calculation logic. Call it (for example) ProductEx. This view can have the PackCost column calculated and named for you. Then write all your other views against the view ProductEx instead of the table Product.

Upvotes: 1

Eric Burdo
Eric Burdo

Reputation: 814

You could create a scalar function that did that logic for you. Then you can just call the function in your view.

Upvotes: 2

Related Questions