SQL Server Inner Query Problem

I have a very simple query in SQL Server. But it is giving errors.

select * from(
 select emp_name + ' ' + emp.surname as employee from ca_contact
)

This query is not working. But when I write like the below, it is working:

select emp_name + ' ' + emp.surname as employee from ca_contact
开发者_如何学Python

---

You'd need an alias. In this case foobar

select * from
   (select emp_name + ' ' + emp.surname as employee from ca_contact) foobar

---

I think you need to specify table alias --

select * from(
 select emp_name + ' ' + emp.surname as employee from ca_contact
) t1

---

In SQL Server, all derived tables must be given an alias [exception is if do not select anything from them, e.g. in an IN/EXISTS clause]. An alternative for what you are doing from SQL Server 2005 onwards is to use a Common Table Expression, which incidentally is also now available in recent versions of Oracle.

A simple meaningless alias

select * from
  (select emp_name + ' ' + surname as employee from ca_contact) [ ]

A Common Table Expression, also naming the column at the sametime

;WITH [ ](employee) AS (
    select emp_name + ' ' + surname
    from ca_contact)
select * from [ ]

FWIW, you can omit the CTE column names and derive them from the query

WITH [ ] AS (
    select emp_name + ' ' + surname as employee
    from ca_contact)
select * from [ ]

Note: Not sure how you can have emp.surname since there is no table/alias emp defined in your query

---

Please try the below query

select employee from (select emp_name + ' ' + emp.surname as employee from ca_contact) as test