Professional Web Applications Themes

trying to join on a computed value - Microsoft SQL / MS SQL Server

need an answer quick, so I really appreciate your help. I'm trying to join table A with table B, but the data in the column I'm joining on in table 'B' needs to be modified/computed. All the IDs in 'B' have been prefixed with "S0" so I need to first strip these characters off before I can join. The problem is, if I try to create the calculated field in a query, I can't join on it because it doesn't exist in time to do the join. Do I need to make a temp table first? is there a more ...

Sponsored Links
  1. #1

    Default trying to join on a computed value

    need an answer quick, so I really appreciate your help.

    I'm trying to join table A with table B, but the data in the column
    I'm joining on in table 'B' needs to be modified/computed. All the IDs
    in 'B' have been prefixed with "S0" so I need to first strip these
    characters off before I can join. The problem is, if I try to create
    the calculated field in a query, I can't join on it because it doesn't
    exist in time to do the join. Do I need to make a temp table first? is
    there a more straightforward way?

    select cast(right(FAC_IDU,5) as integer) as SITEID, * from
    openquery(corpdb,'select * from FMSS') x
    inner join tblActivityLog on tblActivityLog.SiteID = x.SITEID

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'SITEID'.
    Sponsored Links
    maxhodges Guest

  2. #2

    Default Re: trying to join on a computed value

    maxhodges,

    This may do it. Respond ( preferably with table structure DDL) if it doesn't

    select cast(right(FAC_IDU,5) as integer) as SITEID, * from
    openquery(corpdb,'select * from FMSS') x
    inner join tblActivityLog tal on tal.SiteID = cast(right(x.FAC_IDU,5) as integer)

    Regards
    AJ

    i.e. Don't use the computed alias - use the full computed value.
    ( tal alias is for neatness - not essential )

    "maxhodges" <com> wrote in message news:google.com... 


    Andrew Guest

  3. #3

    Default Re: trying to join on a computed value



    it worked!

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Max Guest

Similar Threads

  1. Replies: 2
    Last Post: September 18th, 09:59 PM
  2. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  3. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  4. Index on Computed Column and ADO Insert
    By Peter Crickman in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 4th, 01:46 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139