ODBC Escape Sequence and Outer Joins

ODBC Escape Sequence and Outer Joins

Post by Eb » Fri, 13 Jul 2001 00:58:39



All,

I am trying to port a couple of statements from Oracle to SQL Server.  So
far, it has gone pretty smoothly but I am having some problems converting
some Oracle-specific Outer Joins to ODBC Escape sequences that will still
work in Oracle.

The statment looks like this:

SELECT * FROM A, B, C WHERE
   A.X = B.X(+) AND
   A.Y = C.Y(+)

I have tried re-writting the statement as

SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN C
ON A.Y = C.Y}
and this seems to work with SQL Server, however it doesn't work with Oracle.

Does anyone have any suggestions?
Thanks in advance
Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Carlos J. Quinter » Fri, 13 Jul 2001 02:13:45


It is an outer join between A and B and another between A anc C. So try:

 SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON A.X = B.X ON
A.Y = C.Y }

Even the worst ODBC driver or OLEDB Provider for Oracle should be able to
translate that into native syntax.

--
Best regards,

Carlos J. Quintero

MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
Projects, Close Windows, Review TabIndex and Collections, Add Procedure, Add
Error Handler, Add Procedure or Module Header, Clear Immediate Window,
Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
Properties and more:
www.mztools.com



Quote:> All,

> I am trying to port a couple of statements from Oracle to SQL Server.  So
> far, it has gone pretty smoothly but I am having some problems converting
> some Oracle-specific Outer Joins to ODBC Escape sequences that will still
> work in Oracle.

> The statment looks like this:

> SELECT * FROM A, B, C WHERE
>    A.X = B.X(+) AND
>    A.Y = C.Y(+)

> I have tried re-writting the statement as

> SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN
C
> ON A.Y = C.Y}
> and this seems to work with SQL Server, however it doesn't work with
Oracle.

> Does anyone have any suggestions?
> Thanks in advance
> Eb


 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb Ikonn » Fri, 13 Jul 2001 02:40:20


I tried the format you specified and I get ORA-01468: a predicate may reference only one outer-joined table.

I have also tried the format
SELECT  * FROM {oj WORK_INSTRUCTION left outer join SHIPMENT_DETAIL on WORK_INSTRUCTION.INTERNAL_LINE_NUM = SHIPMENT_DETAIL.INTERNAL_SHIPMENT_LINE_NUM
left outer join ITEM on WORK_INSTRUCTION.ITEM  =  ITEM.ITEM}

and I get ORA-00933: SQL command not properly ended.  I have also tried nested outer joins.

All formats work perfectly with SQL Server yet not with Oracle.

Any further suggestions?

Thanks.
Eb

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb » Fri, 13 Jul 2001 02:41:50


I don't know if this helps, but I am using MS ODBC Driver for Oracle to test
my queries.

Thanks
Eb

Quote:> All,

> I am trying to port a couple of statements from Oracle to SQL Server.  So
> far, it has gone pretty smoothly but I am having some problems converting
> some Oracle-specific Outer Joins to ODBC Escape sequences that will still
> work in Oracle.

> The statment looks like this:

> SELECT * FROM A, B, C WHERE
>    A.X = B.X(+) AND
>    A.Y = C.Y(+)

> I have tried re-writting the statement as

> SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN
C
> ON A.Y = C.Y}
> and this seems to work with SQL Server, however it doesn't work with
Oracle.

> Does anyone have any suggestions?
> Thanks in advance
> Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb » Fri, 13 Jul 2001 03:40:30


I tried the format you sent me, I've also tried nesting the outer joins and
also the format

SELECT  * FROM {oj A left outer join B on A.x = B.x
left outer join  C on A.y  = C.y}
which also doesn't work.

I've mentioned that I'm using the MS ODBC Driver for Oracle.  Any other
suggestions?

Thanks.
Eb



> It is an outer join between A and B and another between A anc C. So try:

>  SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON A.X = B.X ON
> A.Y = C.Y }

> Even the worst ODBC driver or OLEDB Provider for Oracle should be able to
> translate that into native syntax.

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
> Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
> Projects, Close Windows, Review TabIndex and Collections, Add Procedure,
Add
> Error Handler, Add Procedure or Module Header, Clear Immediate Window,
> Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
> Properties and more:
> www.mztools.com



> > All,

> > I am trying to port a couple of statements from Oracle to SQL Server.
So
> > far, it has gone pretty smoothly but I am having some problems
converting
> > some Oracle-specific Outer Joins to ODBC Escape sequences that will
still
> > work in Oracle.

> > The statment looks like this:

> > SELECT * FROM A, B, C WHERE
> >    A.X = B.X(+) AND
> >    A.Y = C.Y(+)

> > I have tried re-writting the statement as

> > SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER
JOIN
> C
> > ON A.Y = C.Y}
> > and this seems to work with SQL Server, however it doesn't work with
> Oracle.

> > Does anyone have any suggestions?
> > Thanks in advance
> > Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Isaac Blan » Fri, 13 Jul 2001 04:01:44


Can you switch to 9i?  It supports ANSI-style joins


Quote:> All,

> I am trying to port a couple of statements from Oracle to SQL Server.  So
> far, it has gone pretty smoothly but I am having some problems converting
> some Oracle-specific Outer Joins to ODBC Escape sequences that will still
> work in Oracle.

> The statment looks like this:

> SELECT * FROM A, B, C WHERE
>    A.X = B.X(+) AND
>    A.Y = C.Y(+)

> I have tried re-writting the statement as

> SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN
C
> ON A.Y = C.Y}
> and this seems to work with SQL Server, however it doesn't work with
Oracle.

> Does anyone have any suggestions?
> Thanks in advance
> Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb » Fri, 13 Jul 2001 06:28:13


With all the bureaucracy involved?

Thanks for the tip, wish Oracle had done this last year.


> Can you switch to 9i?  It supports ANSI-style joins



> > All,

> > I am trying to port a couple of statements from Oracle to SQL Server.
So
> > far, it has gone pretty smoothly but I am having some problems
converting
> > some Oracle-specific Outer Joins to ODBC Escape sequences that will
still
> > work in Oracle.

> > The statment looks like this:

> > SELECT * FROM A, B, C WHERE
> >    A.X = B.X(+) AND
> >    A.Y = C.Y(+)

> > I have tried re-writting the statement as

> > SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER
JOIN
> C
> > ON A.Y = C.Y}
> > and this seems to work with SQL Server, however it doesn't work with
> Oracle.

> > Does anyone have any suggestions?
> > Thanks in advance
> > Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Carlos J. Quinter » Fri, 13 Jul 2001 16:20:30


The right syntax is the original which I sent you:

SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON A.X = B.X ON A.Y
= C.Y }

The Intersolv ODBC driver for Oracle translates it (SQLNativeSql function)
correctly into:

SELECT  * FROM  A , B , C  WHERE A.X = B.X (+)  AND ( A.Y = C.Y (+) )

But the Microsoft ODBC driver for Oracle that I am using translates it
wrongly into:

SELECT  * FROM A, B, C where A.X = B.X(+) and  A.Y(+) = C.Y(+)

Which means that this driver is really buggy.

So, your chances are:

- Use the latest version of the Microsoft ODBC driver for Oracle (MDAC 2.6
or higher), just in case they have fixed the problem.
- Use the ODBC driver from Oracle Corporation, which is also free but it
manages better ODBC outer joins (although it has other bugs)
- Use the Microsoft ODBC driver for Oracle from Intersolv (Merant) which is
not free but it is the best ODBC driver for Oracle
- Use native syntax


SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X) ON
(A.Y = C.Y)  }

The parenthesis makes it work with Microsoft ODBC driver for Oracle. I have
just remembered it and I have tested it. Really buggy driver...

--
Best regards,

Carlos J. Quintero

MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
Projects, Close Windows, Review TabIndex and Collections, Add Procedure, Add
Error Handler, Add Procedure or Module Header, Clear Immediate Window,
Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
Properties and more:
www.mztools.com



Quote:> I tried the format you sent me, I've also tried nesting the outer joins
and
> also the format

> SELECT  * FROM {oj A left outer join B on A.x = B.x
> left outer join  C on A.y  = C.y}
> which also doesn't work.

> I've mentioned that I'm using the MS ODBC Driver for Oracle.  Any other
> suggestions?

> Thanks.
> Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Sanjeev Sharm » Fri, 13 Jul 2001 20:38:53


Why don't you use a userdefined function to get correct OJ syntax. Say to
convert the SQL

SELECT * FROM A, B, C WHERE
    A.X = B.X(+) AND
    A.Y = C.Y(+)

Use a userdefined function say OJ

SELECT * FROM A, B, C WHERE
    OJ('A.X',' B.X','OREACL') + ' AND ' + ...

Now the OJ function will return the correct string depending on third
parameter, which is the DB type.

ORACLE - > A.X = B.X(+)
SQLSERVER -> A.X *= B.X

So the final string is

SELECT * FROM A, B, C WHERE
  A.X *= B.X  AND ....

Sanjeev


Quote:> All,

> I am trying to port a couple of statements from Oracle to SQL Server.  So
> far, it has gone pretty smoothly but I am having some problems converting
> some Oracle-specific Outer Joins to ODBC Escape sequences that will still
> work in Oracle.

> The statment looks like this:

> SELECT * FROM A, B, C WHERE
>    A.X = B.X(+) AND
>    A.Y = C.Y(+)

> I have tried re-writting the statement as

> SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN
C
> ON A.Y = C.Y}
> and this seems to work with SQL Server, however it doesn't work with
Oracle.

> Does anyone have any suggestions?
> Thanks in advance
> Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb » Fri, 13 Jul 2001 20:54:07


The syntax

SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X) ON
(A.Y = C.Y)  }

works perfectly with MS ODBC Driver for Oracle and the Oracle ODBC driver.
However the above syntax does not work SQL Server.

SELECT  * FROM {oj A LEFT OUTER JOIN B  ON (A.X = B.X) LEFT OUTER JOIN C  ON
(A.Y = C.Y)  }

seems to work with SQL Server and I saw a posting that suggested that the
user was able to get the above syntax to work with Oracle, which I have not
succeeded in doing yet.

Any suggestions?

I would also like to know how you determine the statement that the different
driver translates too.  I am new to low-level ODBC programming and need some
direction here.

Thanks
Eb



> The right syntax is the original which I sent you:

> SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON A.X = B.X ON
A.Y
> = C.Y }

> The Intersolv ODBC driver for Oracle translates it (SQLNativeSql function)
> correctly into:

> SELECT  * FROM  A , B , C  WHERE A.X = B.X (+)  AND ( A.Y = C.Y (+) )

> But the Microsoft ODBC driver for Oracle that I am using translates it
> wrongly into:

> SELECT  * FROM A, B, C where A.X = B.X(+) and  A.Y(+) = C.Y(+)

> Which means that this driver is really buggy.

> So, your chances are:

> - Use the latest version of the Microsoft ODBC driver for Oracle (MDAC 2.6
> or higher), just in case they have fixed the problem.
> - Use the ODBC driver from Oracle Corporation, which is also free but it
> manages better ODBC outer joins (although it has other bugs)
> - Use the Microsoft ODBC driver for Oracle from Intersolv (Merant) which
is
> not free but it is the best ODBC driver for Oracle
> - Use native syntax


> SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X) ON
> (A.Y = C.Y)  }

> The parenthesis makes it work with Microsoft ODBC driver for Oracle. I
have
> just remembered it and I have tested it. Really buggy driver...

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
> Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
> Projects, Close Windows, Review TabIndex and Collections, Add Procedure,
Add
> Error Handler, Add Procedure or Module Header, Clear Immediate Window,
> Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
> Properties and more:
> www.mztools.com



> > I tried the format you sent me, I've also tried nesting the outer joins
> and
> > also the format

> > SELECT  * FROM {oj A left outer join B on A.x = B.x
> > left outer join  C on A.y  = C.y}
> > which also doesn't work.

> > I've mentioned that I'm using the MS ODBC Driver for Oracle.  Any other
> > suggestions?

> > Thanks.
> > Eb

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Carlos J. Quinter » Fri, 13 Jul 2001 21:07:32


Quote:> I would also like to know how you determine the statement that the
different
> driver translates too.  I am new to low-level ODBC programming and need
some
> direction here.

You have a handy tool called Microsoft ODBC Test 32-bit and you have a ODBC
API Function called SQLNativeSql in one of the menus. This function takes
ODBC syntax and returns native syntax generated by the driver. It is
intended for debug purposes. You can also call it from VB without external
tools.

I will revisit the other SQL Server issue after lunch.

--
Best regards,

Carlos J. Quintero

MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
Projects, Close Windows, Review TabIndex and Collections, Add Procedure, Add
Error Handler, Add Procedure or Module Header, Clear Immediate Window,
Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
Properties and more:
www.mztools.com

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Carlos J. Quinter » Fri, 13 Jul 2001 23:31:30


It seems that with SQL Server you must use

 SELECT  * FROM {oj A LEFT OUTER JOIN B ON (A.X = B.X) LEFT OUTER JOIN C  ON
(A.Y = C.Y)  }

and not

 SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X) ON
(A.Y = C.Y)  }

Despite native syntax in SQL Server is close to the first statement, the
right one in ODBC syntax is the second one. So I would say this is a bug in
the SQL Server ODBC driver.

Also it seems a bug that SQLNativeSql ODBC API function does not work with
the SQL Server ODBC driver. I am not sure if syntax translation is made at
server level rather than at ODBC driver level (I think I read something
about that).

--
Best regards,

Carlos J. Quintero

MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
Projects, Close Windows, Review TabIndex and Collections, Add Procedure, Add
Error Handler, Add Procedure or Module Header, Clear Immediate Window,
Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
Properties and more:
www.mztools.com



Quote:> The syntax

> SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X) ON
> (A.Y = C.Y)  }

> works perfectly with MS ODBC Driver for Oracle and the Oracle ODBC driver.
> However the above syntax does not work SQL Server.

> SELECT  * FROM {oj A LEFT OUTER JOIN B  ON (A.X = B.X) LEFT OUTER JOIN C
ON
> (A.Y = C.Y)  }

> seems to work with SQL Server and I saw a posting that suggested that the
> user was able to get the above syntax to work with Oracle, which I have
not
> succeeded in doing yet.

> Any suggestions?

 
 
 

ODBC Escape Sequence and Outer Joins

Post by Eb » Sat, 14 Jul 2001 04:11:16


Well I was able to get the statement to work with a syntax of putting the
two outer joins in different select statements and nesting the two
statements in an outer select.  I would have typed it out, but as you can
guess, its quite *.

I think we'll just live with it till we migrate to Oracle 9i.

Thanks for your help.
Eb



> It seems that with SQL Server you must use

>  SELECT  * FROM {oj A LEFT OUTER JOIN B ON (A.X = B.X) LEFT OUTER JOIN C
ON
> (A.Y = C.Y)  }

> and not

>  SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X)
ON
> (A.Y = C.Y)  }

> Despite native syntax in SQL Server is close to the first statement, the
> right one in ODBC syntax is the second one. So I would say this is a bug
in
> the SQL Server ODBC driver.

> Also it seems a bug that SQLNativeSql ODBC API function does not work with
> the SQL Server ODBC driver. I am not sure if syntax translation is made at
> server level rather than at ODBC driver level (I think I read something
> about that).

> --
> Best regards,

> Carlos J. Quintero

> MZ-Tools 2.0 freeware 'all-in-one' Add-In for VB6: Task List, Line
> Numbering, TabIndex Assistant, Procedure Callers, Find and Replace in All
> Projects, Close Windows, Review TabIndex and Collections, Add Procedure,
Add
> Error Handler, Add Procedure or Module Header, Clear Immediate Window,
> Statistics, Copy/Paste/Rename/Remove controls with code, Default Controls
> Properties and more:
> www.mztools.com



> > The syntax

> > SELECT  * FROM {oj A LEFT OUTER JOIN B LEFT OUTER JOIN C ON (A.X = B.X)
ON
> > (A.Y = C.Y)  }

> > works perfectly with MS ODBC Driver for Oracle and the Oracle ODBC
driver.
> > However the above syntax does not work SQL Server.

> > SELECT  * FROM {oj A LEFT OUTER JOIN B  ON (A.X = B.X) LEFT OUTER JOIN C
> ON
> > (A.Y = C.Y)  }

> > seems to work with SQL Server and I saw a posting that suggested that
the
> > user was able to get the above syntax to work with Oracle, which I have
> not
> > succeeded in doing yet.

> > Any suggestions?

 
 
 

1. ODBC Escape Sequence and Outer Joins

All,

I am trying to port a couple of statements from Oracle to SQL Server.  So
far, it has gone pretty smoothly but I am having some problems converting
some Oracle-specific Outer Joins to ODBC Escape sequences that will still
work in Oracle.

The statment looks like this:

SELECT * FROM A, B, C WHERE
   A.X = B.X(+) AND
   A.Y = C.Y(+)

I have tried re-writting the statement as

SELECT  * FROM {oj A LEFT OUTER JOIN B ON A.X = B.X},{oj A LEFT OUTER JOIN C
ON A.Y = C.Y}
and this seems to work with SQL Server, however it doesn't work with Oracle.

Does anyone have any suggestions?
Thanks in advance
Eb

2. posted newbie log backup question below (new question)

3. Outer Join + Outer Join

4. Installing the personnal edition of sql server 2000

5. SQL-92 outer join vs T-SQL outer join (6.5 or 7.0) - test script included

6. URGENT help needed. PRB - refreshing grid

7. Outer Joins, difference between *= and OUTER JOIN

8. Server Machine configuration

9. Left-Outer join and Right-Outer join

10. Outer Join + Outer Join

11. Left outer join works, Right outer join just goes on for ever, processor at 100%

12. LEFT OUTER LOOP JOIN and LEFT OUTER JOIN

13. Outer joins in Oracle vs outer joins in Sybase