Can you rename a table using the keyword 'AS' and a select statement?

Question

Hi All,

This is probably a stupid question to most of you but I was wondering whether you can rename a column using the 'AS' keyword and a select statement?

Here is my SQL:

Select Main.EmpId
        , Associate_List.costCenter, Assignments.Area
        , Main.Assignments_1 AS (
            Select Assignment_Name 
                from Assignments 
                where Assignment_Number = 1 
                    and Assignments.Area = '@Someparemeter'
        )
from associate_list
    , main
    , APU_CC
    , Assignments 
where Main.Empid = Associate_List.Empid 
    and substring(Associate_List.CostCenter,1,4) = APU_CC.CostCentre

The only part of SQL I'm wondering about is:

Main.Assignments_1 AS (
    Select Assignment_Name 
        from Assignments 
        where Assignment_Number = 1 
            and Assignments.Area = '@Someparemeter'
)

Is this possible or am I talking jibberish or is this just a stupid thing to do?

Many Thanks

Answer 1

The part after as is not a value but a variable name; the SQL database will use it to reference the value of the result set so you can compare/sort/filter them. Therefore this is not possible.

If you must do this, you must read the documentation of your database how to build dynamic queries. But I suggest against it because it will cause strange errors that will be very hard to debug.

Answer 2

I'm not quite sure what you are driving at.

If there is a column in the Main table called Assignments_1, you can rename it in your query (to give a different header at the top of the output or for some other reason) like this...

SELECT MAIN.ASSIGNMENT_1 AS MY_NEW_NAME
    FROM etc.

If you want a derived table in your query you name it like this...

SELECT MAIN.ASSIGNMENT_1,
       SELECT *
           FROM (SELECT THIS, THAT, THE_OTHER
                     FROM SOME_TABLE) AS DERIVED_TABLE
    FROM etc.

If you didn't want either of those things, please clarify and we'll try to help.

Answer 3

But you can do this:

Select m.EmpId, l.costCenter, 
 (Select Area From Assignments a 
  Where Assignment_Number = 1 
     And Area = '@Someparemeter') As Area, 
 (Select Assignment_Name From Assignments a 
  Where Assignment_Number = 1 
     And Area = '@Someparemeter') As Assignments_1
From associate_list l 
   Join main m On m.Empid = l.Empid  
   Join APU_CC c On c.CostCentre = substring(l.CostCenter,1,4)

or this:

Select m.EmpId, l.costCenter, Asgn.Area,
  Asgn.Assignment_Name as Assignments_1 
From associate_list l 
   Join main m On m.Empid = l.Empid  
   Join APU_CC c On c.CostCentre = substring(l.CostCenter,1,4)
   Cross Join (Select Assignment_Name From Assignments a 
               Where Assignment_Number = 1 
                  And Area = '@Someparemeter') as Asgn

Answer 4

In SQL Server, you can assign an alias to a column with AS like so:

...
ColumnName AS ColumnAlias,
...

And you can do it for a "sub-select" like you have in your example. (I wouldn't write the query quite like that--I'd run the subquery first and then plop the result into the second query like so:

DECLARE @Assignment_Name varca(100) -- or however long

SELECT @Assignment_Name = Assignment_Name
 from Assignments
 where Assignment_Number = 1
  and Assignments.Area = @Someparemeter

SELECT
 ...
 @Assignment_Name = Assignment_Name,
 ...