Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016

    Unanswered: Conversion to Joins

    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
    ,asb.Qty,asb.Length,asb.Width,asb.SubServObjectCod e,pe.ledind
    from T_Asbuiltstructure as asb,t_part as p,st_partext as pe,t_servobject as so
    where asb.ServObjectCode = '07233001'
    and p.partcode=asb.partcode
    and so.servobjectcode=*asb.subservobjectcode
    and p.partcode*=pe.partcode and so.enddate is null


  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    select cols
        from table a
    inner join some_other_table b
       on a.col = b.col
    left outer join on this_last_table c
       on a.col = c.col
    where a.col =?

  3. #3
    Join Date
    Jan 2013
    Provided Answers: 1

    even I would not try to do a direct translation.

    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)

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts