ODBC escape sequence

ODBC escape sequence

Post by Eb » Fri, 27 Jul 2001 23:34:01



All,

I am trying to convert an existing Oracle outer join to a SQL Server
equivalent (ANSI standard), and I am looking for some suggestions.

The SQL stmt looks like this
SELECT * FROM A, B WHERE A.X(+) = B.X AND   A.Y(+) = B.Y

Thanks
Eb

 
 
 

ODBC escape sequence

Post by DaveSat » Sat, 28 Jul 2001 00:32:09


know nothing about Oracle, but I think it w/b:
SELECT   *
FROM     A
LEFT OUTER JOIN B ON A.X = B.X AND A.Y = B.Y

--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 2000 SP1/7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

Quote:> All,

> I am trying to convert an existing Oracle outer join to a SQL Server
> equivalent (ANSI standard), and I am looking for some suggestions.

> The SQL stmt looks like this
> SELECT * FROM A, B WHERE A.X(+) = B.X AND   A.Y(+) = B.Y

> Thanks
> Eb


 
 
 

ODBC escape sequence

Post by Carlos J. Quinter » Sat, 28 Jul 2001 01:35:31


In ANSI:

SELECT * FROM A RIGHT OUTER JOIN B ON A.X = B.X  AND  A.Y = B.Y

In ODBC:

SELECT * FROM {oj A RIGHT OUTER JOIN B ON A.X = B.X AND  A.Y = B.Y }

In Oracle:

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

--
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 convert an existing Oracle outer join to a SQL Server
> equivalent (ANSI standard), and I am looking for some suggestions.

> The SQL stmt looks like this
> SELECT * FROM A, B WHERE A.X(+) = B.X AND   A.Y(+) = B.Y

> Thanks
> Eb

 
 
 

ODBC escape sequence

Post by Eb » Sat, 28 Jul 2001 02:09:43


Thanks for your input, however it still doesn't work.

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

SELECT * FROM  A , B  WHERE A.X  =* B.X  AND  A.Y  =* B.Y (Legacy SQL
Server)

and
SELECT * FROM A RIGHT OUTER JOIN B ON A.X = B.X  AND  A.Y = B.Y (ANSI SQL
Server)

return different resultsets.  The first two, return the same numbers, while
the last one is different.

Any other thoughts?
Thanks.



> In ANSI:

> SELECT * FROM A RIGHT OUTER JOIN B ON A.X = B.X  AND  A.Y = B.Y

> In ODBC:

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

> In Oracle:

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

> --
> 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 convert an existing Oracle outer join to a SQL Server
> > equivalent (ANSI standard), and I am looking for some suggestions.

> > The SQL stmt looks like this
> > SELECT * FROM A, B WHERE A.X(+) = B.X AND   A.Y(+) = B.Y

> > Thanks
> > Eb

 
 
 

ODBC escape sequence

Post by Carlos J. Quinter » Sat, 28 Jul 2001 02:31:18


Really? Let's test this first:

In Oracle, execute:

CREATE TABLE A (C1 INTEGER)
CREATE TABLE B (C1 INTEGER)
INSERT INTO A VALUES(1)
INSERT INTO B VALUES(1)
INSERT INTO B VALUES(2)

SELECT A.C1, B.C1 FROM A, B WHERE A.C1(+) = B.C1

Returns:

"C1", "C1"
1, 1
<Null>, 2

SELECT A.C1, B.C1 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1 }

Returns:

"C1", "C1"
1, 1
<Null>, 2

In SQL Server (MSDE), execute:

CREATE TABLE A (C1 INTEGER)
CREATE TABLE B (C1 INTEGER)
INSERT INTO A VALUES(1)
INSERT INTO B VALUES(1)
INSERT INTO B VALUES(2)

SELECT A.C1, B.C1 FROM  A, B WHERE  A.C1 =* B.C1

Returns:

"C1", "C1"
1, 1
<Null>, 2

SELECT A.C1, B.C1 FROM  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1

Returns:

"C1", "C1"
1, 1
<Null>, 2

SELECT A.C1, B.C1 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1 }

Returns:

"C1", "C1"
1, 1
<Null>, 2

The 5 results are identical in my test.

I'm going home. Tell me tomorrow. It the above works, we will try with
double columns outer joins.

--
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:> Thanks for your input, however it still doesn't work.

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

> SELECT * FROM  A , B  WHERE A.X  =* B.X  AND  A.Y  =* B.Y (Legacy SQL
> Server)

> and
> SELECT * FROM A RIGHT OUTER JOIN B ON A.X = B.X  AND  A.Y = B.Y (ANSI SQL
> Server)

> return different resultsets.  The first two, return the same numbers,
while
> the last one is different.

> Any other thoughts?
> Thanks.

 
 
 

ODBC escape sequence

Post by Eb » Sat, 28 Jul 2001 03:04:36


Works like a charm


> Really? Let's test this first:

> In Oracle, execute:

> CREATE TABLE A (C1 INTEGER)
> CREATE TABLE B (C1 INTEGER)
> INSERT INTO A VALUES(1)
> INSERT INTO B VALUES(1)
> INSERT INTO B VALUES(2)

> SELECT A.C1, B.C1 FROM A, B WHERE A.C1(+) = B.C1

> Returns:

> "C1", "C1"
> 1, 1
> <Null>, 2

> SELECT A.C1, B.C1 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1 }

> Returns:

> "C1", "C1"
> 1, 1
> <Null>, 2

> In SQL Server (MSDE), execute:

> CREATE TABLE A (C1 INTEGER)
> CREATE TABLE B (C1 INTEGER)
> INSERT INTO A VALUES(1)
> INSERT INTO B VALUES(1)
> INSERT INTO B VALUES(2)

> SELECT A.C1, B.C1 FROM  A, B WHERE  A.C1 =* B.C1

> Returns:

> "C1", "C1"
> 1, 1
> <Null>, 2

> SELECT A.C1, B.C1 FROM  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1

> Returns:

> "C1", "C1"
> 1, 1
> <Null>, 2

> SELECT A.C1, B.C1 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 = B.C1 }

> Returns:

> "C1", "C1"
> 1, 1
> <Null>, 2

> The 5 results are identical in my test.

> I'm going home. Tell me tomorrow. It the above works, we will try with
> double columns outer joins.

> --
> 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



> > Thanks for your input, however it still doesn't work.

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

> > SELECT * FROM  A , B  WHERE A.X  =* B.X  AND  A.Y  =* B.Y (Legacy SQL
> > Server)

> > and
> > SELECT * FROM A RIGHT OUTER JOIN B ON A.X = B.X  AND  A.Y = B.Y (ANSI
SQL
> > Server)

> > return different resultsets.  The first two, return the same numbers,
> while
> > the last one is different.

> > Any other thoughts?
> > Thanks.

 
 
 

ODBC escape sequence

Post by Carlos J. Quinter » Sat, 28 Jul 2001 16:22:19


So? Have you solved the problem?

--
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:> Works like a charm

 
 
 

ODBC escape sequence

Post by Eb » Sat, 28 Jul 2001 20:39:26


I haven't solved the problem, I was just saying that what you tried also
works for me too (using singular columns).

Thanks.
Eb


> So? Have you solved the problem?

> --
> 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



> > Works like a charm

 
 
 

ODBC escape sequence

Post by Carlos J. Quinter » Tue, 31 Jul 2001 16:13:02


Well, using two columns for outer joins is as follows:

In Oracle execute:

DROP TABLE A
DROP TABLE B
CREATE TABLE A (C1 INTEGER, C2 INTEGER)
CREATE TABLE B (C1 INTEGER, C2 INTEGER)
INSERT INTO A VALUES(1,1)
INSERT INTO B VALUES(1,1)
INSERT INTO B VALUES(2,2)

All these 2 statements:

SELECT A.C1, A.C2, B.C1, B.C2 FROM A, B WHERE A.C1(+) = B.C1 AND A.C2(+) =
B.C2
SELECT A.C1, A.C2, B.C1, B.C2 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 =
B.C1 AND A.C2 = B.C2}

Return:

"C1", "C2", "C1", "C2"
1, 1, 1, 1
<Null>, <Null>, 2, 2

In SQL Server (MSDE) execute:

DROP TABLE A
DROP TABLE B
CREATE TABLE A (C1 INTEGER, C2 INTEGER)
CREATE TABLE B (C1 INTEGER, C2 INTEGER)
INSERT INTO A VALUES(1,1)
INSERT INTO B VALUES(1,1)
INSERT INTO B VALUES(2,2)

All these 3 statements:

SELECT A.C1, A.C2, B.C1, B.C2 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 =
B.C1 AND A.C2 = B.C2}
SELECT A.C1, A.C2, B.C1, B.C2 FROM  A RIGHT JOIN  B ON A.C1 = B.C1 AND A.C2
= B.C2
SELECT A.C1, A.C2, B.C1, B.C2 FROM  A , B WHERE A.C1 =* B.C1 AND A.C2 =*
B.C2

Return:

"C1", "C2", "C1", "C2"
1, 1, 1, 1
<Null>, <Null>, 2, 2

It is all as expected, isnt it?

--
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 haven't solved the problem, I was just saying that what you tried also
> works for me too (using singular columns).

 
 
 

ODBC escape sequence

Post by Eb » Wed, 01 Aug 2001 02:05:55


They do, my problem was in the translation of the join.  Was a little
confused with how Oracle outer joins works.

Thanks for your help.
Eb


> Well, using two columns for outer joins is as follows:

> In Oracle execute:

> DROP TABLE A
> DROP TABLE B
> CREATE TABLE A (C1 INTEGER, C2 INTEGER)
> CREATE TABLE B (C1 INTEGER, C2 INTEGER)
> INSERT INTO A VALUES(1,1)
> INSERT INTO B VALUES(1,1)
> INSERT INTO B VALUES(2,2)

> All these 2 statements:

> SELECT A.C1, A.C2, B.C1, B.C2 FROM A, B WHERE A.C1(+) = B.C1 AND A.C2(+) =
> B.C2
> SELECT A.C1, A.C2, B.C1, B.C2 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 =
> B.C1 AND A.C2 = B.C2}

> Return:

> "C1", "C2", "C1", "C2"
> 1, 1, 1, 1
> <Null>, <Null>, 2, 2

> In SQL Server (MSDE) execute:

> DROP TABLE A
> DROP TABLE B
> CREATE TABLE A (C1 INTEGER, C2 INTEGER)
> CREATE TABLE B (C1 INTEGER, C2 INTEGER)
> INSERT INTO A VALUES(1,1)
> INSERT INTO B VALUES(1,1)
> INSERT INTO B VALUES(2,2)

> All these 3 statements:

> SELECT A.C1, A.C2, B.C1, B.C2 FROM { oj  A RIGHT OUTER  JOIN  B ON A.C1 =
> B.C1 AND A.C2 = B.C2}
> SELECT A.C1, A.C2, B.C1, B.C2 FROM  A RIGHT JOIN  B ON A.C1 = B.C1 AND
A.C2
> = B.C2
> SELECT A.C1, A.C2, B.C1, B.C2 FROM  A , B WHERE A.C1 =* B.C1 AND A.C2 =*
> B.C2

> Return:

> "C1", "C2", "C1", "C2"
> 1, 1, 1, 1
> <Null>, <Null>, 2, 2

> It is all as expected, isnt it?

> --
> 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 haven't solved the problem, I was just saying that what you tried also
> > works for me too (using singular columns).

 
 
 

1. ODBC Escape Sequence

Hi,

I would like to a find a field in an access 97 database
via odbc/mfc/mvc++ with the value (including "'"s)

                'f"unf'

Which escape sequences in an CRecordset-query consisting of

CString s("SELECT * FROM table AND table.field LIKE _________");

must I use?
--
 Bernd Geistert
 Bavarian Research Center for Knowledge Based Systems
 Am Weichselgarten 7, D-91058 Erlangen

2. Dynamic select statement

3. odbc escape sequence Oracle

4. VFP3 - Client/Server Sample App

5. ODBC Escape Sequence and Outer Joins

6. An internal error occured

7. ODBC Escape Sequences

8. Help!!! Anyone used ORA_FFI ????????

9. ODBC Escape Sequence and Outer Joins

10. ODBC escape sequence

11. ODBC CALL escape sequence versus SQL_NOSCAN_ON

12. Escape sequences in string fields - possible?

13. How to use JDBC Escape Sequences