将旧版SQL外连接* =，= *转换为ANSI [英] Convert Legacy SQL Outer JOIN *=, =* to ANSI

问题描述

我需要将旧式SQL外连接转换为ANSI.

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

原因是，我们正在从旧版数据库实例(2000/5?)升级到SQL 2016.

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

旧版SQL查询:-

选择

-我要选择的数据-

 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')

在这里，Party1和Party2是聚会类型代码，而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 :

我在做什么错?

选择

-我要选择的数据-

 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')

很显然，在所有三个旧式联接中，cp(对手方)表位于* =的左侧.因此，这应该转换为所有这三个表的左外部联接.但是，我的解决方案似乎不起作用 我怎样才能解决这个问题 ?我在这里做什么错了?

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 :)

编辑

我还有另一个类似的查询:

I also have another query like this :

选择

-我要选择的数据-

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  

在这种情况下，dvco和d表似乎在同一表sm上进行了LEFT OUTER JOIN.我该如何处理? 也许加入同一张表并使用别名sm1和sm2? 还是应该将sm用作中央表，并在dvco和d表上将联接更改为RIGHT OUTER JOIN?

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 ?

推荐答案

感谢您的帮助，对于后期的帖子深表歉意，但是我使用SSMS内置的查询设计器工具来快速破解了它.它只是重构了我所有的查询，然后在Join本身上输入了正确的Join，Left或Right以及Where条件作为AND条件，所以我得到了关于pre和post的正确数据结果集，只是有时是数据排序/排序有点差. 我迷失了截止日期，无法更早地更新解决方案.再次感谢您的帮助.希望这也会对其他人有帮助！

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

仍然有些不确定，如果连接条件相同并且过滤条件也相同，为什么排序/排序会稍微偏离一点，因为数据是100％匹配的.

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.

要使查询Designer正常工作，只需选择您的旧版SQL，然后 通过按Ctrl + Shift + Q或转到主菜单打开查询设计器 工具栏=>查询=>编辑器中的设计查询.

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.

就这样.这会将您的旧代码重构为新的ANSI标准.您将获得转换后的查询以及可以复制和测试的新联接.对我来说，这100％的时间都是有效的，除了在某些情况下排序不匹配之外，您可以通过在pre和post两者中添加一个简单的order by子句来进行比较，以进行比较.

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 :

查看全文