Visual Query Builder--Connection on part of another field

[cowannbell]

Okay.  I'm trying to use visual query builder and I need to connect two tables on a location #.  The issue that I have is that one table has the location #plus the number17 at the end.  Son on table 1 location number it is listed as 1234 but on table 2 locatin number is listed as 123417.  How can I make the connection?

I'm thinking that it would be this connection but not sure how:

left outer join table2.location_number on table1.location_number = table2.location_number

Any ideas?  

[TUSHAR050392]

Hey @cowannbell, Have you tried joining the two tables using substring. So you can use the substring command to get rid of the 17 from your one dataset and join on field from other one. It will be like below-

left outer join table2.location_number on table1.location_number = substring(table2.location_number, 0, LEN(table2.location_number)-1)

In place of substring, you can also try LEFT(table2.location_number, LEN(table2.location_number)-2)

Let me know if it was helpful.

[cowannbell]

I wanted to use the left function but this is a numeric field.  I need to convert it but I wasn't quite sure how to use left and convert to numeric at the same time.

[OllieClarke]

@cowannbell if your database has the FLOOR() function, then you could join table1 to FLOOR(table2/100) as this would effectively remove the 17 from the end.
(divide by 100, then round down to the nearest integer)

[TUSHAR050392]

@cowannbell Are both your fields numeric on which you want to join? If yes then I think the left formula should work and you don't have to convert into a string I think.

[cowannbell]

I just used 123 as an example.  That field varies in length.

[cowannbell]

I tried this and I get the below error.

Please note, this is the last line before the where statement.

left outer join IWH.PRVDR_X_ADDRESS PRVDR_X_ADDRESS on CLAIM_LINE.SRV_PRVDR_ID = PRVDR_X_ADDRESS.PROVIDER_ID and CLAIM_LINE.SRV_LOCATION_NBR = Left(PRVDR_X_ADDRESS.ADDRESS_ID,Len(PRVDR_X_ADDRESS.ADDRESS_ID)-2

[OllieClarke · Accepted solution]

Hi @cowannbell 

I tested this on my MSSql Server and it worked fine

[TUSHAR050392]

@cowannbell That is strange because I have used LEN function in Sql server in join clause. You can try the substring function as well. Both the formula I have sent will take care of variation in length of the string. The solution provided by @OllieClarke is also a good one.

[cowannbell]

Thank you so much.  This did the trick.