How do I do this join?

[former_member283700]

Hi

Consider 3 tables in a universe, A,B and C. Table A is joined to table B on ClaimNumber and ClaimantNumber and this works fine.

Now, I need to join table A to table C and thought the same joins would work.

However, on closer inspection, though tables A and C both have ClaimNumber and ClaimantNumber, for some reason ClaimantNumber on C is defined as 10 chars, where as elsewhere it is 3 chars. The joins don't work.

I need a way of joining the tables by the trimmed ClaimNumber and ClaimanatNumber, does anyone know how this can be done please?

Many thanks

Eddie

[former_member283700]

Why is it every time I post a question I then discover the answer myself! I should post more often! The simple solution is this:

Edit join in universe. The edit join window appears. At the bottom is the expression text. I changed it to read:

ltrim(rtrim(A.CLAIMANT_NO))=ltrim(rtrim(C.CLAIMANT_NO))

I then did this for the other join and it works great.

Eddie

[Former Member]

Eddie,

The one caveat I would add is that the trim functions could well cancel out your indexing.

If you are on Oracle, ask your DBA to have a look at adding function-based indexes for you.

Regards,

Mark

[former_member283700]

Thanks, we're on SQL server so hopefully OK!

[Former Member]

SQL Server (2008) doesn't use function-based indexes to improve performance but what you can use is competed columns if you find that performance is not great. Have a look at this thread on msdn for reference if it comes to it:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/670fd1c5-5990-4ab4-96d6-fe72a...

Regards,

Mark