oracle-databaseinner-joinvarchar2database-link

Oracle Database Link + Inner Join + To_Number Query


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. 

Solution

    1. NULLs will never match with equals, so your join already takes care of that.
    2. You would get an implicit type conversion of (probably) the NUMBER to VARCHAR, so that should also be taken care of.

    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.