How to Join on a calculate column

1

Hi everybody,

Sorry in advance if this was already answered. I am working on a query that needs to pull data from two tables (table "a" and "b"). I need to add a username from table "b" to all applicable table "a" records. The only one field that I can join them on is the KeySting field in table "b" which is a string that contains multiple fields from table table "a". I had to create a concatenated field using fields from table a so it can be used to join table "b".

When I tried to use the concat formula in the join statement it did not work. Do you have any idea on how to resolve it?

This is how it looks like right now:

SELECT a.Name, a. LastName, a. UIMth, a.UISeq, Concat(Convert(varchar(10), a.UIMth,1), a.UISeq ) as [KeyString], b.UserName

FROM b on Convert(varchar(10), i.UIMth,1),' ', 'UISeq:',' ', i.UISeq ) as [DateSeq], b.UserName)=b.Keystring

Thanks in advance!

2

You're adding extra literals and spaces into the join string. You don't give any sample values so I don't exactly what the combined string looks like, but this should be close:

FROM b on Convert(varchar(10), i.UIMth,1) + Convert(varchar(10), i.UISeq, 1) = b.Keystring