Rewriting Queries to use ANSI Joins instead of old Comparison Operators

• jordonpilling

  SSCommitted

  Points: 1690

  More actions

  May 14, 2009 at 5:18 am

  #204302

  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....

• Grant Fritchey

  SSC Guru

  Points: 397721

  More actions

  May 14, 2009 at 6:41 am

  #994436

  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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software