Rewriting Queries to use ANSI Joins instead of old Comparison Operators

  • Hello there, we are going to be upgrading several databases to SQL server 2008 soon, and after running Upgrade advisor, we have identified 50+ views and stored procedures that use old style operators for joins *= =* etc, this database is running in 8 compatibility mode explaining how it has been working up to now on a 2005 server. Either way, i think its time to get rid of these. However, some of the queries have 6+ tables, manually rewriting these joins is going to be a serious pain (and error prone) unless i can find a better way of converting these queries. For example, i have the following query:

    SELECT PERIOD = CONVERT(CHAR, ( DATEPART(year, o.PUR_DATE) * 100 )

    + DATEPART(month, o.PUR_DATE)),

    PRO_CODE = l.PRO_CODE,

    OUTSTANDING = ( SUM(PUR_QTY_ORD) - SUM(PUR_TOT_REC) )

    FROM PURCHASE p,

    PUR_ORD o,

    PUR_LINE l,

    ANALYSIS_DATA s,

    BALANCES_STOCK b,

    COH_PUR_SPLITS u

    WHERE p.PUR_CODE = o.PUR_CODE

    AND l.PRO_CODE *= b.PRO_CODE

    AND l.DEPOT_CODE *= b.DEPOT_CODE

    AND PUR_QTY_ORD > 0

    AND PUR_QTY_ORD > PUR_TOT_REC

    AND o.DEPOT_CODE = l.DEPOT_CODE

    AND s.PRO_CODE = l.PRO_CODE

    AND o.DOC_ID = l.DOC_ID

    AND o.DOC_TYPE = l.DOC_TYPE

    AND p.PUR_CODE = '0099'

    AND l.PUR_COMPLETE = 'N'

    AND o.DOC_NUMBER = l.DOC_NUMBER

    AND l.DEPOT_CODE *= u.DEPOT_CODE

    AND l.DOC_TYPE *= u.DOC_TYPE

    AND l.DOC_ID *= u.DOC_ID

    AND l.DOC_NUMBER *= u.DOC_NUMBER

    AND l.DOC_LINE *= u.DOC_LINE

    GROUP BY CONVERT(CHAR, ( DATEPART(year, o.PUR_DATE) * 100 ) + DATEPART(month, o.PUR_DATE)),

    l.PRO_CODE

    I tried using Query Designer to fix them, however it only seems to be able to fix queries with 1 operator, Views with more than one join gives the following error:

    Illegal use of outer join operator.

    The text that you have selected cannot be represented graphically using the Query Designer. Please select just the text necessary to design a SELECT, DELETE, INSERT or UPDATE statement.

    Any ideas/tools/scripts to convert these joins en-mass?

    Any info would be great.

    Regards

    Jordon

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • I think this one will require application of the Mk1 Eyeball and the 19xx release of Brain.

    There are too many variables possible for a script to automate the process. For example, which of the criteria should be moved to the JOIN and which should be left in the WHERE clause? You can't just assume that PK/FK relationship is the only criteria that defines the JOIN. You're going to have to rewrite the queries. But that's OK. You've got opportunities in there to clean up the code (I saw columns without a table reference and your tables don't define a schema) as well as fix the JOIN syntax.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply