[freetds] LEFT OUTER JOIN fails on Oracle database

James K. Lowden jklowden at freetds.org
Wed Jan 18 23:45:12 EST 2006


Ian Robinson wrote:
> Running the following SQL from the Oracle database connecting to SQL
> Server 2000 fails with ORA-00997: illegal use of LONG datatype.
> 
> Select c.ref1, c.col
> From tab1 c
> Left outer join tab2 at databaselink e on e.ref1 = c.ref1;
> 
> The column ref1 is of type INT on both tab1 and tab2 tables. By
> selecting the data_type from dba_tab_columns on oracle, the data type is
> confirmed to be NUMBER - therefore the data type conversion appears to
> work fine.
...
> Does FreeTds support the left outer join operation? Has anyone
> experience of this particular error?

FreeTDS neither supports nor does not support any join.  It does not
interpret the SQL at all.  SQL is merely data to be transmitted to the
server.  

I would guess the join is taking place on the Oracle server, with the
Microsoft server appearing as a table to Oracle.  You could confirm that
by inspecting the interaction in a TDSDUMP log.  

Why using an outer join induces an error is a mystery beyond words. 
Almost.  

Hope that at least narrows down where to look.  :-/

--jkl



More information about the FreeTDS mailing list