join conditions have different format

Database
N/A

join conditions have different format

I am tring to join two tables based on account_id

but one of them  is Char another account_id is int,

I tried below

1. Select cast( t2.account_id as  int) , t1. account_id from A as t1 left join B as t2

on t1.account_id = t2.account_id

2. Select t1.account_id, t2.account_id from A as t1 left join B as t2

on cast(t2.account_id as int)= t1.account_id

Both of them got the same error which is

Bad character in format .

can anyone give me the advice, how should i change my query ?

Thanks

1 REPLY
N/A

Re: join conditions have different format

If the cast to an INT fails there are some characters outside of '0' to '9'. You can check which rows fail using

SELECT account_id FROM A WHERE TO_NUMBER(account_id) IS NULL;

You might also try to cast the number to a string using TRIM(t1.account_id), if this works depends on the actual string stored in t2.account_id.

Anyway, this indicates a bad datamodel.