Removing non-ANSI Left Outer Join *=
-
I need to rewrite the following SELECT statement to omit the non-ANSI LEFT OUTER JOIN symbol *= (see the LAST 2 predicates below). Problem resides with DB1..TAB_a being the LEFT table in the new statement but I also need DB3..TAB_d to be in LEFT OUTER position as well.. HOW DO I accomodate this?
SELECT stuff
FROM DB1..TAB_a,
DB2..TAB_b,
DB2..TAB_c,
DB3..TAB_d,
DB4..TAB_e,
DB4..TAB_f,
DB5..TAB_g
WHERE DB1..TAB_a.col_1 LIKE 'S%'
AND DB1..TAB_b = 21
AND DB1..TAB_a.col_1 = DB2..TAB_b.col_1
AND DB2..TAB_b.col_2 = DB2..TAB_c.col_1
AND DB2..TAB_c.col_2 = DB3..TAB_d.col_1
AND DB3..TAB_d.col_5 *= DB4..TAB_e.col_12
AND DB1..TAB_a.col_1 *= DB4..TAB_f.col_15
BT -
Based on what you have given this may or may not work:
SELECT stuff
FROM
DB1.dbo.TAB_a a
inner join DB2.dbo.TAB_b b
on (a.col_1 = b.col_1)
inner join DB2.dbo.TAB_c c
on (b.col_2 = c.col_1)
inner join DB3.dbo.TAB_d d
on (c.col_2 = d.col_1)
left outer join DB4.dbo.TAB_e e
on (d.col_5 = e.col_12)
left outer join DB4.dbo.TAB_f f
on (a.col_1 = f.col_15)
cross join DB5.dbo.TAB_g g -- no criteria given in original where clause
WHERE
a.col_1 LIKE 'S%'
AND b.? = 21 - what column is supposed to = 21?
Why I say it might not work, is you may have to move the last elements of the where clause into the joins in the from clause. Also, I added a cross join as there was no criteria for TAB_g.
-
I apologize for NOT includnig DB5.. in the above predicate.. ANYWAY, I found a solution for this situation --
Using SQL Server 2000 - in Enterprise Mgr expand a DB and it's tables, right mouse click any table, OPEN Table, Return All Rows, Paste in your NON-ANSI SQL, click the Show/Hide Diagram Pane, and let SQL Server regenerate the NON-ANSI SQL into ANSI standard !!
Worked rather nicely.
BT
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply