I use SQL Server, and I created view and added new column which contain mathematical equation

Let's say I have this:

create view 
as
    select 
        a.date, a.sale, a.buy,
        profit = a.sale - a.buy,
        profit_prs = (a.sale - a.buy) / a.sale 
    from 
        tableA a

In line 5 how can I use [profit] column in [profit_prs] expression to be like this:

profit_prs = profit  / a.sale

CodePudding user response：

• First-off: you need to address the divide-by-zero error that's looming in your query...
  • Change (a.sale - a.buy) / a.sale to ( a.sale - a.buy ) / NULLIF( a.sale, 0.0 ) - that way you'll get a NULL in profit_prs instead of your query aborting entirely.
    • Moving on....
• ISO/ANSI SQL does not allow a SELECT projection to refer to new column expressions in the same query "level" (i.e. within the same SELECT clause of the same derived-table or CTE or other table expression).
  • Some RDBMS extend SQL to allow it, such as MySQL.
• Instead, uou will need to use a new derived-table or a CTE to add a new "logical step" to the query which will SELECT your base-table's date, sale, and buy columns, and define the new profit column.
  • ...and the next (outer-er) SELECT can then define the profit_prs column based on the previous step's profit column.
  • However, when you do this, the end-result is very verbose and probably harder to maintain:

This is what the VIEW looks like when using a new CTE to represent the inner step:

CREATE VIEW dbo.MyView AS

WITH q1 AS (

    SELECT
        a.date,
        a.sale,
        a.buy,
        profit = ( a.sale - a.buy )
    FROM
        dbo.TableA AS a
)
SELECT
    q1.date,
    q1.sale,
    q1.buy,
    q1.profit,
    profit_prs = q1.profit / NULLIF( a.sale, 0.0 )
FROM
    q1;

Whereas if you don't mind the repetition of ( a.sale - a.buy ) you still end-up with a much shorter query:

CREATE VIEW dbo.MyView AS

SELECT
    a.date,
    a.sale,
    a.buy,
    profit     = ( a.sale - a.buy ),
    profit_prs = ( a.sale - a.buy ) / NULLIF( a.sale, 0.0 )
FROM
    dbo.TableA AS a;

CodePudding user response：

That’s a good spot to use cross apply; you can move the calculation to to an inline table of values, then reuse it as you wish in the select clause:

create view myview as
select a.date, a.sale, a.buy,
    x.profit,
    x.profit / nullif(a.sale, 0) profit_prs
from tableA a
cross apply ( values (a.sale - a.buy) ) x(profit)