Results 1 to 4 of 4

Thread: Table aliasing

  1. #1
    Join Date
    Feb 2003
    Location
    OH
    Posts
    10

    Unanswered: Table aliasing

    Hello all,
    When I assign an alias name for a table in a select statement, how can I use it after the select statement is executed. I want to accomplish somewhat Like this:-
    Declare @name as varchar(20)

    Select TOP 1 * from Table1 t1 -- Making sure it returns only one record
    Set @name = t1.[Name]

    When I do this i get the following error :-

    The column prefix 't1' does not match with a table name or alias name used in the query.

    Here I would like to get the result set as well as access the values in the result set. By doing this I dont have to execute the Select statement two times for better performance. Is there any way to accomplish this? or in SQL Server are we restricted to such a behavior.

    Any input is highly appreciated,
    TIA,
    darbhas.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ah, Another Oracle CONVERT...

    Nope....it doesn't work that way...

    But how about..

    Select TOP 1 * INTO #t1 from Table1

    Set @name = t1.[Name] FROM #t1
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2003
    Location
    OH
    Posts
    10
    Thank you very much for the reply.
    Actually i changed it as follows to get it working:
    Set @name = t1.[Name] FROM #t1
    to
    Set @name = (select t1.[Name] FROM #t1)

    But here instead of accessing the main table two times i'm accessing the main table once and temp table twice. Temp table is accessed once for assigning the value and one more time to select the record, because I need to the result set too. So my question is which one performs better? Accessing main table twice or accessing main table once and temp table twice.

    Thank you.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is the way I would do it

    Code:
    USE Northwind
    GO
    
    DECLARE @OrderId int, @CustomerID nchar(10)
    
    SELECT TOP 1 @OrderID = OrderID
    	   , @CustomerID = CustomerID 
    --	   , ect
          FROM Orders
    
    SELECT @OrderId, @CustomerId
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •