Convert Legacy SQL Outer JOIN *=, =* to ANSI

Question

I need to convert a legacy SQL outer Join to ANSI.

The reason for that being, we're upgrading from a legacy DB instance (2000/5 ?) to SQL 2016.

Legacy SQL query :-

SELECT

--My Data to Select--

 FROM counterparty_alias ca1,  
  counterparty_alias ca2,  
  counterparty cp,  
  party p  
 WHERE cp.code *= ca1.counterparty_code AND
  ca1.alias = 'Party1' AND  
  cp.code *= ca2.counterparty_code AND  
  ca2.alias = 'Party2' AND
  cp.code *= p.child_code AND  
  cp.category in ('CAT1','CAT2')

Here, Party1 and Party2 Are the party type codes and CAT1 and CAT2 are the category codes. They're just data; I have abstracted it, because the values don't really matter.

Now, when I try to replace the *= with a LEFT OUTER JOIN, I get a huge mismatch on the Data, both in terms of the number of rows, as well as the Data itself.

The query I'm using is this :

What am I doing wrong ?

SELECT

--My Data to Select--

 FROM 
  counterparty cp  
  LEFT OUTER JOIN counterparty_alias ca1 ON cp.code = ca1.counterparty_code  
  LEFT OUTER JOIN counterparty_alias ca2 ON cp.code = ca2.counterparty_code      
  LEFT OUTER JOIN party p  ON  cp.code = p.child_code
 WHERE  
  ca1.alias = 'Party1' AND  
  ca2.alias = 'Party2' AND
  cp.category in ('CAT1','CAT2')

Clearly , in all the three legacy joins , the cp (counterparty) table is on the Left hand Side of the *=. So that should translate to a LEFT OUTER JOIN WITH all the three tables. However, my solution doesn't seem to to be working How can I fix this ? What am I doing wrong here ?

Any help would be much appreciated. Thanks in advance :)

EDIT

I also have another query like this :

SELECT

--My Data to Select--

FROM dbo.deal d,  
 dbo.deal_ccy_option dvco,  
 dbo.deal_valuation dv,  
 dbo.strike_modifier sm  
WHERE d.deal_id = dvco.deal_id  
AND d.deal_id = dv.deal_id      
AND dvco.base + dvco.quoted *= sm.ccy_pair  
AND d.maturity_date *= sm.expiry_date  

In this case, both the dvco and d tables seem to be doing a LEFT OUTER JOIN on the same table sm. How do I proceed about this ? Maybe join in on the same table and use an alias sm1 and sm2 ? Or should I use sm as the central table and change the join to RIGHT OUTER JOIN on dvco and d tables ?

Answer 1

Thanks for the help and sorry for the late post, but I got it to work with a quick hack, using the Query Designer Tool inbuilt in SSMS. It simply refactored all my queries and put in the correct Join, Either Left or Right , and the Where condition as an AND condition on the Join itself, so I was getting the correct data result set for both pre and post, only sometimes the data sorting/ordering was a little off. I got lost with deadlines and couldnt update with the solution earlier. Thanks again for the help. Hope this helps someone else too !!

Still a little bit unsure though why the ordering/sorting was a little off if the Join condition was the same and the filters as well, because data was a 100 % match.

To get the query Designer to Work , just select your legacy SQL, and open the Query Designer by pressing Ctrl + Shift + Q or Goto Main Menu ToolBar => Query => Design Query in Editor.

Thats it. This will refactor your legacy code to new ANSI standards. You wll get the converted query with the new Joins that you can copy and test. Worked 100% of the time for me, except in some cases where the sorting was not matching, which you can check by adding a simple order by clause to both pre and post to compare the data.

For reference, I cross checked with this post :

http://sqlblog.com/blogs/john_paul_cook/archive/2013/03/02/using-the-query-designer-to-convert-non-ansi-joins-to-ansi.aspx

Answer 2

If you're doing a conversion, it has been my experience that *= is a RIGHT OUTER JOIN and =* is a LEFT OUTER JOIN in terms of a straight conversion.

I am converting hundreds of stored procs and views now and through testing this is what matches. I run the query as the original first, then make the changes and re-run it with the ANSI compliant code.

The data returned needs to be the same for consistency in our application.

So for your second query I think it would look something like this:

 FROM dbo.deal d  
  INNER JOIN dbo.deal_ccy_option dvco ON d.deal_id = dvco.deal_id
  INNER JOIN  dbo.deal_valuation dv ON d.deal_id = dv.deal_id 
  RIGHT OUTER JOIN  dbo.strike_modifier sm ON d.maturity_date = sm.expiry_date
  AND (dvco.base + dvco.quoted) = sm.ccy_pair 

Answer 3

I think the problem with your translation is that you are using conditions on the right tables in the where clause instead of in the on clause.
When I tried to translate it, this is the translation I've got:

FROM counterparty cp
LEFT JOIN counterparty_alias ca1 ON cp.code = ca1.counterparty_code 
                                AND ca1.alias = 'Party1'
LEFT JOIN counterparty_alias ca2 ON cp.code *= ca2.counterparty_code 
                                AND ca2.alias = 'Party2'
LEFT JOIN party p ON cp.code = p.child_code
WHERE cp.category in ('CAT1','CAT2')

However, it's hard to know if I'm correct since you didn't provide sample data, desired results, or even a complete query.