Column alias in order by working in sql server 2008R2 but intermittently

  • Dear Folks

    My below query was working fine before 2 days at my prod environment (SQL Server 2008 R2 enterprise edition). but Now its throwing error "Invalid column name component" because I used column in order by clause. 
    Select top 1000 with ties  
     1 As LOBID,  
     b.CmpntNum As Component,  
     'S' As NonStock,  
     b.VndrCmpntNum As VendorComponentNumber
      From ABC..MSTComponent a 
      Right Outer Join XYZ b  
       On a.LOBID = 1  
       And a.Component = b.CmpntNum  
      Where a.Component Is Null
      Order By b.Component

    But the same query is working in my stage environment SQL Server 2008 R2 Developer edition. 

    Can someone please suggest on this.

  • "Component" is just an alias.  It's not a column of table b (VndrCmpntNum ).  Remove the "b." from the ORDER BY clause and it should work.


  • Use 
    ORDER BY b.CmpntNum Or

    ORDER BY Component


Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply