Desktop productivity for business analysts and programmers

Join 2 tables with Different Field type types

Reply
Contributor
Posts: 24

Join 2 tables with Different Field type types

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.
Super User
Posts: 3,240

Re: Join 2 tables with Different Field type types

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.
Contributor
Posts: 24

Re: Join 2 tables with Different Field type types

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?
Super User
Posts: 3,240

Re: Join 2 tables with Different Field type types

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.
N/A
Posts: 0

Re: Join 2 tables with Different Field type types

Nice to see a kiwi here lol
Ask a Question
Discussion stats
  • 4 replies
  • 4823 views
  • 0 likes
  • 3 in conversation