Query returns "Invalid column name 'result'. "Where 'result' is a column generated by select (SQL SERVER) [closed]

1
SELECT cte,
       (CONVERT(VARCHAR, manifesto) + ' ' + TIPO) as result
  FROM coleta
 WHERE result = '567 TRUCK SIDER'
    
asked by anonymous 08.11.2018 / 16:11

2 answers

1

The result field does not actually exist in the table, so to use it in WHERE you should 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'
    
08.11.2018 / 16:31
1

You can not use Alias names of the proper query in the where clause, so you have to use an auxiliary query so that the result is the same column name, as placed by @Sorack. Or, in the where clause poes the "calculation" 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'
    
08.11.2018 / 16:40