Oracle Database Link + Inner Join + To_Number Query

Question

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.

It's unclear to me what you mean to have happen with NULL values. TO_NUMBER(NULL) returns NULL, so in what way does the need to convert the values conflict with the NULL-handling? Perhaps it would help if you provide sample inputs, sample query, actual result, and expected result... — Mark Adelsberger
– Mark Adelsberger, Commented Jan 17, 2017 at 14:11
I would like to disregard NULL values, have them removed from the result set. — wervdon
– wervdon, Commented Jan 17, 2017 at 14:13
Repeating what you wrote in the question neither answers my specific question nor provides the requested general clarification. If for some reason you can't or won't provide sample input, query you've tried, actual results and how they differ from expectation... then it's on you to find a different way to make your request clear. Repetition isn't it if you want my help; but maybe someone else is a better mind reader. — Mark Adelsberger
– Mark Adelsberger, Commented Jan 17, 2017 at 14:21
Sarcasm definitely helps. I would very much love to provide input here, however I am unable to due to 2 complications I have included in my question. I cannot get past the errors cause I don't know how to compare VARCHAR2 to NUMBER while also taking NULL values into account. — wervdon
– wervdon, Commented Jan 17, 2017 at 14:27
Is that the error you got from the very beginning? That seems to be related to JDBC - it probably doesn't have anything to do with the SQL query itself. Did you do some research on it on the web (or otherwise)? I'd be shocked if similar questions haven't already been asked and answered on SO. — user5683823
– user5683823, Commented Jan 18, 2017 at 7:27

BobC · Accepted Answer · 2017-01-17 15:12:48Z

1

NULLs will never match with equals, so your join already takes care of that.
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.

answered Jan 17, 2017 at 15:12

BobC

4,4421 gold badge14 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wervdon Over a year ago

I provided more details about the data in the question.

Collectives™ on Stack Overflow

Oracle Database Link + Inner Join + To_Number Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related