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

  • anujkumar.mca

    SSC-Addicted

    Points: 479

    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,  
     b.Status,  
     '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.

  • John Mitchell-245523

    SSC Guru

    Points: 148245

    "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.

    John

  • IrfanHyd

    SSCommitted

    Points: 1598

    Use 
    --------
    ORDER BY b.CmpntNum Or

    ORDER BY Component

    ---------------------------------------------------------------------------------------------------------------------------------
    IrfanHyd

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

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