войти
 
ГлавнаяНовостиБиблиотекаОбучениеСкачатьМероприятияСообществоФорумыПоддержкаО проекте
Ресурсы для ИТ-профессионалов >
> 
> 
aliased column in the other columns

Отвечено aliased column in the other columns

  • 8 июня 2012 г. 22:11
     
     
    Нужно войти
    0

    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:18
    Модератор
     
     Отвечено С кодом
    Нужно войти
    0

    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:23
    Отвечающий
     
     
    Нужно войти
    0

    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:29
     
     Предложенный ответ
    Нужно войти
    0

    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
    •  
     
  • 9 июня 2012 г. 10:24
     
     
    Нужно войти
    0
    i don't know cross apply keyword. it helps me.
     
  • 10 июня 2012 г. 0:22
     
     
    Нужно войти
    0

    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:24
     
     
    Нужно войти
    0
    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