DatabaseA - TableA - FieldA VARCHAR2
DatabaseB - TableB - FieldB NUMBER
[dblink created]
SELECT *
FROM TableB@dblink b
INNER JOIN TableA a
ON b.FieldB = a.FieldA
There are 2 complications.
1. FieldA is VARCHAR2
but FieldB is NUMBER
.
2. FieldA contains -
and FieldB contains 0
.
More info about the fields
FieldA: VARCHAR2(15)
, NOT NULL
Sample values
-
123
No non-numeric values, except for -
FieldB: NUMBER(5,0)
Sample values
0
123
No non-numeric values
What I'm trying to do is to ignore the rows if FieldA='-' OR FieldB=0, otherwise compare FieldA to FieldB.
SELECT *
FROM TableB@dblink b
JOIN TableA a
ON to_char(b.FieldB) = a.FieldA
I get the following error:
SQL Error: 17410, SQLState: 08000
No more data to read from socket.
Having said that, I am a big proponent of not relying on implicit datatype conversions. So I would write my query as
SELECT *
FROM TableB@dblink b
JOIN TableA a
ON to_char(b.FieldB) = a.FieldA
If that is not giving the results you want, perhaps posting examples of the data in each table and the results you desire would be helpful.