SQL Server Performance

Converting Ansi Outer join to Newer Outer join

Discussion in 'SQL Server 2005 General Developer Questions' started by Fais, Oct 23, 2008.

  1. Fais New Member

    Hi all,
    I am trying to convert some Ansi sql code ( OLD Version ) to new version of sql for compliance with SQL Server 2005 code.
    (Ansi SQL ( OLD CODE ) uses (1) ' =* ' for Right outer join (2) ' *= ' for left outer join and ' = ' sign for inner join)
    I have tried converting some code to newer version . Please see below and do let me know, is this right way of doing (or) not.
    Code :
    Ansi syntax ( valid on 80 compatible databases ( SQL Server 2000) using '=*' for Right outer join and '=' sign for inner join)
    Select < Column 1...........Column n >
    FROM v_unit v,
    device d,
    romver r1,
    romver r2,
    romver r3,
    romver r4,
    otap_profile op

    WHERE v.cid = @intcid
    and v.status < 4
    and r1.dsn=*v.dsn and r1.module_id=0
    and r2.dsn=*v.dsn and r2.module_id=1001
    and r3.dsn=*v.dsn and r3.module_id=1003
    and r4.dsn=*v.dsn and r4.module_id=2000
    and d.dsn=v.dsn
    and op.opid=*d.opid

    Modified script for SQL Server 2005:
    Select < Column 1...........Column n >
    FROM romver r1
    RIGHT OUTER JOIN v_unit v ON r1.dsn = v.dsn
    LEFT OUTER JOIN romver r2 ON v.dsn = r2.dsn
    LEFT OUTER JOIN romver r3 ON v.dsn = r3.dsn
    LEFT OUTER JOIN romver r4 ON v.dsn = r4.dsn
    INNER JOIN device d ON v.dsn = d.dsn
    LEFT OUTER JOIN otap_profile op ON d.opid = op.opid
    WHERE v.cid = @intcid
    AND v.status < 4
    AND r1.module_id = 0
    AND r2.module_id = 1001
    AND r3.module_id = 1003
    AND r4.module_id = 2000
  2. TommCatt New Member

    What you have looks good. But, of course, I can't actually execute the queries to test them.

    What I would do is just execute both forms of the query joining only the first two tables and compare the results. Then add one join at a time. If there is any difference, you will be able to tell exactly at which table and join the deviation occurred.
  3. moh_hassan20 New Member

    There is a very simple and clean way to convert old Ansi sql code using query designer in SSMS, that is the steps:

    • run ssms
    • select your sql code and Copy your query to clipboard
    • click new query
    • from menu query -> design query in editor , or
    or press control - shift Q keys
    • The dialoge add table appear , don't select table and press close.
    • In the query designer window paste the ansi query
    • click the mouse any where
    • you will find your query in the new format [:D]
    • copy your new sql code , and replace old code

    i tested that example
    old ansi code
    select ProductID
    from Production.Product a,
    Production.ProductDocument b
    where a.ProductID *=b.ProductID

    the new query
    SELECT a.ProductID
    FROM Production.Product AS a
    LEFT OUTER JOIN Production.ProductDocument AS b
    ON a.ProductID = b.ProductID
  4. moh_hassan20 New Member

    <p>&nbsp;other short method</p><ul><li>run ssms&nbsp;</li></ul><ul><li>click new query <br></li><li>write your ansi code query <br></li><li>Mark and select you code by mouse&nbsp;</li><li>right click mouse</li><li>click design query in editor from pop up menu<br></li><li>the query designer window is opened, with the query but in the new format (join ... on ....)<br></li><li>Enjoy the new format <img src="http://sql-server-performance.com/Community/emoticons/emotion-2.gif" alt="Big Smile"></li></ul><br><p>&nbsp;</p>

Share This Page