0

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.
wervdon
  • 557
  • 2
  • 13
  • 24
  • 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 Jan 17 '17 at 14:11
  • I would like to disregard NULL values, have them removed from the result set. – wervdon Jan 17 '17 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 Jan 17 '17 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 Jan 17 '17 at 14:27
  • If you don't like my tone, focus on the help others provide. As they don't seem to be here yet, I'll offer once more to help *if you do your part*. You say you "can't get past the errors"? That's the first you mentioned of any errors. Provide the error messages. And you still haven't provided the query you tried. – Mark Adelsberger Jan 17 '17 at 14:33
  • There seems to be a query in the question. Your keen eyes must have missed it. I assume you will come up with some clever way to explain that it actually is not a query. – wervdon Jan 17 '17 at 14:39
  • I didn't say "post any old query"; I said post the query you tried *along with an indication of the result*. That exact query works for me, by the way, in every respect you've described; further reason I don't believe it's (a/the) query you've tried. All of this is academic to me, though. I'm blind, so you've finally ticked me off enough that I will not help you further. – Mark Adelsberger Jan 17 '17 at 14:44
  • OK, so let's start fresh. Null is not equal to anything, not even another null, so you do not have that complication (not sure why you think that would be an issue). Then - you either need to use `to_char()` (perhaps with a specific format model, so that 222 becomes 000222 for comparison to the string '000222') on the numeric column, or `to_number()` on the VARCHAR2 column. You know your data, we don't - we can't tell you which is the right choice. What's in Field A in Table A - both numeric and non-numeric values, like 222 and 22C? In that case `to_number()` won't work. –  Jan 17 '17 at 14:53
  • @mathguy I edited the question to include more details about the data – wervdon Jan 18 '17 at 07:15
  • 1
    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. –  Jan 18 '17 at 07:27
  • @mathguy That is a strange error which only shows up on queries including 2 tables. No errors when I query them separately. While trying to write a more detailed answer to you, I kept researching which made me find a solution to the problem. Thank you. – wervdon Jan 18 '17 at 09:15
  • When you get a chance, and if you think it's an error others may encounter too, it would be good - if you have the time - if you could share your findings in an Answer. Good for others to see if they run into the same issue in the future. (And if you found the answer on the web, something as simple as a pointer there would suffice.) Cheers! –  Jan 18 '17 at 15:16

1 Answers1

1
  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.

BobC
  • 4,208
  • 1
  • 12
  • 15