Very Computer

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

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

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

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

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

Thanks.
Eb

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

Thanks for your help.
Eb