Joining two tables on oracle, where one column on table A is looking for values on two columns on Table B

Question

I am trying to join two tables, Table A have one column "Col1" with Account no and ID values, and Table B have two columns Name Account "Dev" and ID column "Ops", I am trying to join table B to table A in a condition that Col1 = dev column on table B, or Col1 v= Ops column on table B

select * from Table a, Table b where a.Col1 = b.Dev or a.Col1 = b.Ops

Answer 1

It is about datatype mismatch. As you didn't post your tables' description, I'll show it on Scott's EMP and DEPT tables.

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)  --> I'll join it to DEPT.DEPTNO
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)  --> I'll join it to DEPT.LOC
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

---

This is how your current query looks like:

SQL> select *
  2  from emp e, dept d
  3  where e.ename = d.deptno
  4     or e.sal = d.loc;
where e.ename = d.deptno
      *
ERROR at line 3:
ORA-01722: invalid number

See? Exactly the same error as you got, because ENAME's datatype is VARCHAR2, while DEPTNO is a NUMBER. So let's convert number to a string:

SQL> select *
  2  from emp e, dept d
  3  where e.ename = to_char(d.deptno)
  4     or e.sal   = d.loc;
   or e.sal   = d.loc
                *
ERROR at line 4:
ORA-01722: invalid number

Ah, still the same error, but now asterisk points to a different column. Why? Because SAL is a NUMBER, while LOC is a VARCHAR2. So, convert SAL to a string:

SQL> select *
  2  from emp e, dept d
  3  where e.ename        = to_char(d.deptno)
  4     or to_char(e.sal) = d.loc;

no rows selected

SQL>

Right, now it works. Though, no rows are returned as there are no matches, but - there's no error either.

---

Side note: you'd rather properly JOIN tables; leave WHERE clause for conditions data should meet.

SQL> select *
  2  from emp e join dept d on e.ename        = to_char(d.deptno)
  3                        and to_char(e.sal) = d.loc;

no rows selected

SQL>

Answer 2

The clue is in the error message - which you disclosed in a comment instead of including it in your original question (as you should have done).

It indicates that the Ops (ID) column in the second table is number data type. When the query attempts to match a name like 'Joe' from the Col1 column in the first table against a number like 2381 (an id) - as it will do when 'Joe' does not match the name column in the second table - it will attempt to convert the string 'Joe' to a number, resulting in the error you saw. In a case like that, Oracle attempts to convert the string to a number, not the other way around.

The workaround is simple - compare a.Col1 to to_char(b.Ops).

Hopefully you will be able to fix the first table - its design is fatally flawed. You shouldn't have to put up with such nonsense in the first place.