Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Posts
    21

    Unanswered: Whats is the syntax to do a join of 2 @tables ?

    I am trying to do a going of 2 @table varibles

    Hence,

    declare @RANK_FIPPR table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPR decimal (10,2))

    declare @RANK_FIPPU table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPU decimal (10,2))

    After I insert records into the @tables, I am then trying this command,

    select @RANK_FIPPR.FINAME as NAME, @RANK_FIPPR.RANK as FIPPR_RANK, @RANK_FIPPU.RANK as FIPPU_RANK
    into RANK_FI_COMPLETE
    from @RANK_FIPPR, @RANK_FIPPU
    where @RANK_FIPPR.FINAME = @RANK_FIPPU.FINAME

    Below is the error I get

    Server: Msg 170, Level 15, State 1, Line 57
    Line 57: Incorrect syntax near '.'.

    I also get this same error if I try this simple query,

    select @RANK_FIPPU.RANK from @RANK_FIPPU

    SO the question is, What am I missing ??

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Whats is the syntax to do a join of 2 @tables ?

    There are a couple of errors in your code.

    To answer your second part of the question,
    I also get this same error if I try this simple query,

    select @RANK_FIPPU.RANK from @RANK_FIPPU

    SO the question is, What am I missing ?? [/SIZE][/QUOTE]

    Alias the table variable with another name and qualify the columns in your table variable with this alias and it will work.

    As far as I know, You cannot create a table variable with a SELECT INTO clause.

    - CB

    [QUOTE][SIZE=1]Originally posted by ChrisWBates
    I am trying to do a going of 2 @table varibles

    Hence,

    declare @RANK_FIPPR table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPR decimal (10,2))

    declare @RANK_FIPPU table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPU decimal (10,2))

    After I insert records into the @tables, I am then trying this command,

    select @RANK_FIPPR.FINAME as NAME, @RANK_FIPPR.RANK as FIPPR_RANK, @RANK_FIPPU.RANK as FIPPU_RANK
    into RANK_FI_COMPLETE
    from @RANK_FIPPR, @RANK_FIPPU
    where @RANK_FIPPR.FINAME = @RANK_FIPPU.FINAME

    Below is the error I get

    Server: Msg 170, Level 15, State 1, Line 57
    Line 57: Incorrect syntax near '.'.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Whats is the syntax to do a join of 2 @tables ?

    So to fix the first part of the query, define the third table variable and use
    INSERT INTO @ThirdTableVariable
    SELECT ... FROM

    - CB

    Originally posted by sbaru
    There are a couple of errors in your code.

    To answer your second part of the question,
    I also get this same error if I try this simple query,

    select @RANK_FIPPU.RANK from @RANK_FIPPU

    SO the question is, What am I missing ??
    Alias the table variable with another name and qualify the columns in your table variable with this alias and it will work.

    As far as I know, You cannot create a table variable with a SELECT INTO clause.

    - CB

    Originally posted by ChrisWBates
    I am trying to do a going of 2 @table varibles

    Hence,

    declare @RANK_FIPPR table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPR decimal (10,2))

    declare @RANK_FIPPU table
    (
    RANK int NOT NULL IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
    FINAME nvarchar(255) NULL,
    FIPPU decimal (10,2))

    After I insert records into the @tables, I am then trying this command,

    select @RANK_FIPPR.FINAME as NAME, @RANK_FIPPR.RANK as FIPPR_RANK, @RANK_FIPPU.RANK as FIPPU_RANK
    into RANK_FI_COMPLETE
    from @RANK_FIPPR, @RANK_FIPPU
    where @RANK_FIPPR.FINAME = @RANK_FIPPU.FINAME

    Below is the error I get

    Server: Msg 170, Level 15, State 1, Line 57
    Line 57: Incorrect syntax near '.'.

  4. #4
    Join Date
    Nov 2003
    Posts
    21

    Thanks

    Yep, the alias trick worked, so I am good to go

Posting Permissions

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