Skip to Content.
Sympa Menu

freetds - Re: [freetds] LEFT OUTER JOIN fails on Oracle database

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Зверев Виталий Борисович <VZverev AT genesis.spb.ru>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] LEFT OUTER JOIN fails on Oracle database
  • Date: Thu, 19 Jan 2006 14:20:18 +0300

Hi Ian.
This is "well known" 9i problem (not FreeTDS exactly). The tab2 definitely
has LONG column.
In two words solution described as: Then you wish to build product between
local and remote tables and remote table has LONG column, instead of ANSI
join use (+) join.
Sample:
create public database link test ...
----OK-----
select vl.view_name
from dba_views vl,
dba_views@test vr
where vr.view_name = vl.view_name(+);
----OK-----
select vl.view_name
from dba_views vl,
dba_views@test vr
where vr.view_name(+) = vl.view_name ;
----OK-----

*BUT*
select vl.view_name
from dba_views vl
Full outer join dba_views@test vr on vr.view_name = vl.view_name;

ORA-00997: illegal use of LONG datatype

select vl.view_name
from dba_views vl
Left outer join dba_views@test vr on vr.view_name = vl.view_name;


ORA-00997: illegal use of LONG datatype

select vl.view_name
from dba_views vl
Right outer join dba_views@test vr on vr.view_name = vl.view_name;


ORA-00997: illegal use of LONG datatype

If you have metalink access you could see detailed explation in Note:272371.1.


Sincerely yours.
-------------------------------------------------------
Vitaly Zverev, OCP DBA 8&8i
Oracle E-Business Suite &
Infiniband Linux Cluster Administrator
Genesis Ltd., St.Petersburg, Russia



> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Ian Robinson
> Sent: Wednesday, January 18, 2006 7:36 PM
> To: freetds AT lists.ibiblio.org
> Subject: [freetds] LEFT OUTER JOIN fails on Oracle database
>
> Hi,
>
>
>
> FreeTds Version = 0.63 (tds version = 8.0)
>
> SQL Server version = 2000
>
> Client = Solaris 2.9 / Oracle 9iR2
>
>
>
> 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@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.
>
>
>
> Additionally, I am able to complete a normal join between the
> two tables successfully, e.g.
>
>
>
> Select c.ref1, c.col
>
> >From tab1 c,tab2 e
>
> where e.ref1 = c.ref1;
>
>
>
> I believe SQL Server supports the ANSI left outer join syntax
> (I have tried the Sybase *=, but Oracle can not interpret).
>
>
>
> Does FreeTds support the left outer join operation? Has
> anyone experience of this particular error?
>
>
>
> Regards,
>
> Ian.
>
>
>
>
> Visit our website at http://www.hoaresbank.co.uk
>
> C Hoare & Co is authorised and regulated by the Financial
> Services Authority Registered in England no. 240822:
> Registered office 37 Fleet St, London, EC4P 4DQ
>
> **************************************************************
> *****************
> Disclaimer:
>
> Confidentiality:
> This message and attachments are, and may be, confidential
> and are sent for the personal attention of the addressee(s).
> If you are not the intended addressee, any use, disclosure or
> copying of this document is unauthorised. Information
> transmitted by email may be intercepted, lost, destroyed,
> corrupted or delayed and as a result, C Hoare & Co do not
> accept responsibility for any errors or omissions in the
> contents of this message. If you would like to confirm the
> contents of this email, please request a hard copy version.
>
> Monitoring/Viruses:
> C Hoare & Co may monitor all incoming and outgoing emails in
> line with current legislation. Although emails are screened
> for viruses, C Hoare & Co cannot guarantee that any
> transmissions will be virus free.
> **************************************************************
> *****************
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>




Archive powered by MHonArc 2.6.24.

Top of Page