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: "Pickett, David" <David.Pickett AT phlx.com>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] LEFT OUTER JOIN fails on Oracle database
  • Date: Wed, 18 Jan 2006 12:49:20 -0500

BTW, Sybase/SQLServer:
x *= y
in Oracle is:
(+) x = y
but I can never remember which side is outer without Googling, probably x as
the plus is mnemonic for 'add rows when I am missing'. I usually use the
ANSI join, which should be supported, is more explicit and much more
portable.

-----Original Message-----
From: Ian Robinson [mailto:ian.robinson AT hoaresbank.co.uk]
Sent: Wednesday, January 18, 2006 11:36 AM
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