Oracle: join on integer value of field

Question

I realise that the structure of the data in database I am working with leaves a lot to be desired, but I have no control over that, so bear with me.

What I have is a field ADDRESSES.COMPANY that may or may not be an integer ID. Sometimes it's a string. When it is an integer, however, I want to do a join to another table COMPANIES. The original query (pre-join) looks like this:

SELECT *
  FROM (SELECT ROWNUM AS ROW_NUM, INNERQUERY.*
          FROM (  SELECT *
                    FROM ADDRESSES, COMPANIES
                ) INNERQUERY) OUTERQUERY
 WHERE ROW_NUM <= 50 AND ROW_NUM > 0

If I try to join normally, I get an error ORA-01722: invalid number... for obvious reasons (it's not always a number to attempt to join on, or at least I assume that's what it must mean because it's true!). My query after trying to join:

SELECT *
  FROM (SELECT ROWNUM AS ROW_NUM, INNERQUERY.*
          FROM (  SELECT *
                    FROM ADDRESSES, COMPANIES
                    WHERE ADDRESSES.COMPANY(+) = COMPANIES.ID
                ) INNERQUERY) OUTERQUERY
 WHERE ROW_NUM <= 50 AND ROW_NUM > 0

The only two potential solutions I can come up with are both using the TO_NUMBER() function, which I assumed would evaluate a non-numerical string to 0. I tried:

SELECT *
  FROM (SELECT ROWNUM AS ROW_NUM, INNERQUERY.*
          FROM (  SELECT *
                    FROM ADDRESSES, COMPANIES
                    WHERE TO_NUMBER(ADDRESSES.COMPANY)(+) = COMPANIES.ID
                ) INNERQUERY) OUTERQUERY
 WHERE ROW_NUM <= 50 AND ROW_NUM > 0

which errors with ORA-00936: missing expression (on line 7). I assumed my guess at the appropriate place to use it was incorrect and tried the following instead:

SELECT *
  FROM (SELECT ROWNUM AS ROW_NUM, INNERQUERY.*
          FROM (  SELECT *, TO_NUMBER(ADDRESSES.COMPANY) AS COMPANYID
                    FROM ADDRESSES, COMPANIES
                    WHERE COMPANYID(+) = COMPANIES.ID
                ) INNERQUERY) OUTERQUERY
 WHERE ROW_NUM <= 50 AND ROW_NUM > 0

which errors with ORA-00923: FROM keyword not found where expected on line 3.

When my first attempt didn't work, I was sure that my second would. But I am baffled by the error. Am I just completely clueless about Oracle syntax and/or behaviour, or is it something else? Is there a solution to my problem?

Please, read about (SQL-92 standard) `LEFT` and `RIGHT` join syntax. The `(+)` syntax is old, proprietary and prone to errors. — ypercubeᵀᴹ, Dec 06 '12 at 13:47
**[Difference between Oracle's plus notation over ansi join notation?](http://stackoverflow.com/questions/1193654/different-between-oracles-plus-notation-over-ansi-join-notation)** — ypercubeᵀᴹ, Dec 06 '12 at 16:23

score 2 · Accepted Answer · answered Dec 06 '12 at 13:02

1.

WHERE TO_NUMBER(ADDRESSES.COMPANY)(+) = COMPANIES.ID

You cannot do this.

The (+) operator can be applied only to a column, not to an arbitrary expression. However, an arbitrary expression can contain one or more columns marked with the (+) operator.

JOINS

2.

SELECT *, TO_NUMBER(ADDRESSES.COMPANY) AS COMPANYID
  FROM ADDRESSES, COMPANIES
 WHERE COMPANYID(+) = COMPANIES.ID

You cannot reference a column by alias in the same scope this alias was defined (unless it is an order by clause).

The correct way to perform an outer join in this case would be to use ansi join syntax:

select * 
  from companies
  left join addresses on companies.id = addresses.companyid

It doesn't make much sense to use to_number on string columns, as it will yield you the same invalid number error on the first non-numeric value it encounters during join.

You may try, however, casting number to char, as it has been advised.

  select * 
      from companies
       left join addresses 
         on to_char(companies.id) = addresses.companyid

But you have to keep in mind that it would probably drop your perfomance, as you won't be able to use index whenever you transform your column.

score 0 · Answer 2 · answered Dec 06 '12 at 12:16

What If just convert COMPANIES.ID to varchar and join these tables based on strings not integers.

SELECT *
  FROM (SELECT ROWNUM AS ROW_NUM, INNERQUERY.*
          FROM (  SELECT *
                    FROM ADDRESSES, COMPANIES
                    WHERE 
                    ADDRESSES.COMPANY(+) = CAST (COMPANIES.ID AS VARCHAR(100))
                ) INNERQUERY) OUTERQUERY
 WHERE ROW_NUM <= 50 AND ROW_NUM > 0

colemar · Answer 3 · 2012-12-06T13:49:48.727

I infer that ADDRESSES.COMPANY is of string type (varchar2(), char()) while COMPANIES.ID is of number type (number, integer,...).

You cannot always convert a string to a number, therefore to_number() is unreliable. But you can convert every number to a string:

WHERE ADDRESSES.COMPANY(+) = to_char(COMPANIES.ID)

Of course if numericals in ADDRESSES.COMPANY are formatted in a particular way you must use the same format to convert COMPANIES.ID

WHERE ADDRESSES.COMPANY(+) = to_char(COMPANIES.ID,'fm00009')

fm00009 meaning left-padded with zeros and without leading space (for positive numbers).

If there is an index on COMPANIES.ID this will not be used by the optimizer to resolve the query, because the predicate involves a function of COMPANIES.ID. A workaround would be to create an index on to_char(COMPANIES.ID,'fm00009') (so called function based index).

Another approach is using a case switch to decide when COMPANY can be converted to a number

SELECT ...
FROM
  (select ADDRESSES.*,
     case
       when translate(COMPANY,'X0123456789','X') is null -- base 10 non negative integer
       then to_number(COMPANY)
       else null
     end as COMPANY_N
   from ADDRESSES) as a,
  COMPANIES
where a.COMPANY_N(+) = COMPANIES.ID

An index on ADDRESSES.COMPANY will not be used.

Oracle: join on integer value of field

3 Answers3