SQL Server use the new column name in other column in view

Question

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

You will need to use a derived-table or a CTE to add a new "logical step" to the query, as ISO SQL does not let you refer to newly-defined column-expressions in the same query step. — Dai, Nov 05 '22 at 09:29
Also, consider using `WITH SCHEMABINDING` to protect your `VIEW` from breaking-changes to `tableA` - and you should schema-qualify your object-names too (which helps performance). — Dai, Nov 05 '22 at 09:30
BTW, your query will fail if `a.sale == 0` due to divide-by-zero: https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql — Dai, Nov 05 '22 at 09:31

score 1 · Accepted Answer · answered Nov 05 '22 at 09:41

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;

score 0 · Answer 2 · answered Nov 05 '22 at 11:53

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)

SQL Server use the new column name in other column in view

2 Answers2