Query returns "Invalid column name 'result'. " where 'result' is a column generated by select (SQL SERVER)

Asked

Viewed 230 times

1

SELECT cte,
       (CONVERT(VARCHAR, manifesto) + ' ' + TIPO) as result
  FROM coleta
 WHERE result = '567 TRUCK SIDER'
database sql-server

2 answers

1


The field result does not actually exist in the table, so to use it in the WHERE you must indicate how the field is formed. 

SELECT x.*
  FROM (SELECT cte,
               (CONVERT(VARCHAR, c.manifesto) + ' ' + c.tipo) as result
          FROM coleta c) x
  WHERE x.result = '567 TRUCK SIDER'

1

You cannot use Alias Names from the query itself in the Where clause, so you need to use an auxiliary query so that the result becomes the same column name, as placed by @Sorack. Or in the Where clause you put the "calculus" of the column with alias:

ex: 

SELECT cte, (CONVERT(VARCHAR, c.manifesto) + ' ' + c.tipo) as result
FROM coleta c 
WHERE (CONVERT(VARCHAR, c.manifesto) + ' ' + c.tipo) = '567 TRUCK SIDER'

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.