I have to convert the following SQL statemant into SQL server syntax, meaning I need to convert the =* and *= to LEFT JOINS.
I have no experience yet with these statements so please help!
Select asb.lotnr,asb.abslinenr,asb.startdate,asb.partCode ,p.Description
from T_Asbuiltstructure as asb,t_part as p,st_partext as pe,t_servobject as so
where asb.ServObjectCode = '07233001'
and p.partcode*=pe.partcode and so.enddate is null
This is harder than you think. The extended equality sign notation actually came in 2 forms; Sybase and Oracle (Centura – nee Gupta - let you configure their product to work either way.
Consider a very simple example (I hope you have heard of the Chris Date SPJ database)
from suppliers, SupParts
where supplier.sup_nbr *= SupParts.sup_nbr
and onhand_qty < 200;
If you perform the extended equality join first you get one result and then apply the 2nd single table predicate on quantity to that result. It is not the same as doing them in the reverse order!
Oracle did the outer joins and other predicates in one order; Sybase did than the other way. I cannot remember which did it. Which way. If you had more than one outer join in a where clause, the results were ambiguous! The optimizer could pick various orders depending on available indexes, etc.
Another problem is that you can share the same table as both preserved and unpreserved with extended equality. This is easy to do with aliases in the in fixed join notation.
I strongly recommend that instead of trying to just translate your code to the new notation, you look at what results you actually want from the specs and rewrite the statement from scratch. Hey, I was on ANSI X3H2 when we were playing with this and