Usage of Column Alias in SQL calculated fields and queries.

• Subhash-141882

  Ten Centuries

  Points: 1294

  More actions

  March 7, 2005 at 12:20 am

  #66972

  Hi There,

  Please help me to frame an SQL query using Alias field.

  For Eg:

  SELECT QtyContracted1,ContractPrice, (QtyContracted1*ContractPrice) AS  Amount,    Amount*2    FROM TCA20007

  In the above query we have two columns from the SQL table and using them we created a caculated Column/Field and named it with an Alias called 'Amount'.

  If i need to used the above created Alias called 'Amount' further in my query for some other calculation like mentioned above (fourth column Amount*2 ) its giving "Invalid column name 'Amount'." error.

  How to solve and make use of the alias columns in calculating further fields,

   

  Thanks in advance,

  Waiting for the response.

  Subhash

• Helen-84411

  Hall of Fame

  Points: 3320

  More actions

  March 7, 2005 at 12:46 am

  #545656

  Hi,

  I think this cannot be done within a single sql query. If you are using a derived table that is possible. (Select any key column instead of ID)

  SELECT QtyContracted1,ContractPrice,  Amount*2   

  FROM TCA20007 a,

        (select (QtyContracted1*ContractPrice) AS  Amount from

         TCA20007) as b

  WHERE a.id =b.id

   

  
  Helen
  --------------------------------
  Are you a born again. He is Jehova Jirah unto me

• Subhash-141882

  Ten Centuries

  Points: 1294

  More actions

  March 7, 2005 at 12:54 am

  #545657

  Thank You Helen.

   

• noeld

  SSC Guru

  Points: 96590

  More actions

  March 7, 2005 at 7:42 am

  #545705

  you don't need a join for that, just:

  SELECT QtyContracted1,ContractPrice,  Amount*2   

  FROM 

        (select *, (QtyContracted1*ContractPrice) AS  Amount from  TCA20007 ) a

  HTH

  
  * Noel