Join 2 tables with Different Field type types

[thegraduate]

Hi All,

Quite a simple one for most, i have two tables with a URN for each, one is a text and one is a number. When i link them, it gives me an error due to invalid field type (ERROR: Expression using equals (=) has components that are of different data types.)

How can I convert one of the fields so it is a number?

Thanks.

[SASKiwi]

You haven't said how you are doing the joining.

However in SAS SQL you can do:

create table xxxx as
select *
from table1 as a, table2 as b
where a.urn = input(b.urn,12.);

Here I am assuming that table1 has the numeric URN and table2 the character one.

[thegraduate]

Hi,

I am joining through Query builder.

So Table one has AccountNumber (Text)
Table two has AccNum(Number)

Just wondering how I would do it through there?

[SASKiwi]

I suggest you use the computed column option, and the Advanced Expression Editor to type in the INPUT function as I have already mentioned, to create a new computed column that converts the character column to numeric. Then you should be able to use the new computed column in your join.

[deleted_user]

Nice to see a kiwi here lol