none
aliased column in the other columns

    Вопрос

  • select top 1 case when price<3.88 then low else high end as rate, price*rate as tax, price*(1+tax) as marketPrice from TABLE1

    returns me 'Invalid column name 'rate'.'

    How can i use aliased column in the other columns?

    8 июня 2012 г. 22:11

Ответы

  • You can use CROSS APPLY or DERIVED table technique, e.g.

    select top (1) Rate, Price * Rate as Tax, MarketPrice 
    from (select case when price <3.88 then low else high end as rate, price, price *(1+tax) as marketPrice from Table1) X
    
    -- or
    select top (1) F.Rate, T.Price * F.Rate as Tax,
    price * (1+tax) as MarketPrice -- I assume tax is a column in a table
    
    FROM Table1 T
    CROSS APPLY (select case when Price < 3.88 then Low else High END) as Rate) F



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Предложено в качестве ответа Hasham NiazEditor 8 июня 2012 г. 22:24
    • Помечено в качестве ответа emperorIT 9 июня 2012 г. 10:24
    8 июня 2012 г. 22:18
    Модератор

Все ответы

  • You can use CROSS APPLY or DERIVED table technique, e.g.

    select top (1) Rate, Price * Rate as Tax, MarketPrice 
    from (select case when price <3.88 then low else high end as rate, price, price *(1+tax) as marketPrice from Table1) X
    
    -- or
    select top (1) F.Rate, T.Price * F.Rate as Tax,
    price * (1+tax) as MarketPrice -- I assume tax is a column in a table
    
    FROM Table1 T
    CROSS APPLY (select case when Price < 3.88 then Low else High END) as Rate) F



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Предложено в качестве ответа Hasham NiazEditor 8 июня 2012 г. 22:24
    • Помечено в качестве ответа emperorIT 9 июня 2012 г. 10:24
    8 июня 2012 г. 22:18
    Модератор
  • Hi empoerorIT !

    You can not use use Column Aliases in the same query level, You can use it referencing the outer level query.

    You can use the same CASE in every column calculation.

    Thanks, Hasham

    8 июня 2012 г. 22:23
    Отвечающий
  • In SQL, the principle is all-at-once, so you cannot use an alias defined earlier in the column list. Which is a good thing. What if there also is a column called "rate" in the table.

    What you can do is to use a common-table expression:

    ; WITH CTE AS (
       select top 1 case when price<3.88 then low else high end as rate,
              price
       FROM   tbl
    )
    SELECT rate, tax = price*rate
    FROM   CTE

    There are engines that permit what you are asking for like Access. They are in disagreement with the SQL standard.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Предложено в качестве ответа Hasham NiazEditor 9 июня 2012 г. 6:40
    8 июня 2012 г. 22:29
  • i don't know cross apply keyword. it helps me.
    9 июня 2012 г. 10:24
  • SQL is a declarative set-oriented language. The rows of a table come into existence all at once and there is no ordering (you use keys); the columns of a table come into existence all at once and there is no ordering (you use names).

    This is covered in the first week of any SQL class


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    10 июня 2012 г. 0:22
  • Oh. the proprietary TOP(mn) construct returns a random row without an ORDER BY. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    10 июня 2012 г. 0:24