调试经验——Oracle复杂多表查询的维护、功能增强方法（Enhancement/Maitenance of complicated Oracle query）

工作中需要维护一个比较重要的报表，包含50多列数据，来自15张数据表。

嵌套查询数量多（with as定义的子查询有6个），数据关系复杂（where子句中的限定条件多）。

昨天，收到用户的新要求：添加一个来自另外一个数据库的新字段。于是，从早上开始，就吭哧吭哧开始忙起来了。

基本步骤如下：

1. 这个新字段的业务含义是什么？（个人理解：优秀临床试验研究员的标记）

2. 这个新字段在现有的数据库中是否存在？（经在DB Master File中快速查询，发现存在同名字段，但来自不同的表）

3. 如何将该字段加入现有的query？（经分析，现有query中有个子查询，是专门存放研究员信息的，将该字段加入该子查询是符合业务逻辑的，而且这样query的改动更为容易）

4. 将查询出来的结果与另外两种替代方法进行交叉验证（其一是通过OBIEE获取数据集，其二是运行.iqy文件通过Excel查询数据集，这两种方式都属于手动操作），经过一番努力，欣喜地发现三种方法得到结果完全一致（说明客户公司的数据质量还是很高的，必须点个赞！）

5. 测试通过后，发邮件告诉客户，可从下次release开始执行此次变更。

这个问题总共一个在一个小时内就解决掉了，自我感觉挺良好，所以决定顺便挑战一下一年多来一直没有解决掉的一个遗留问题：SCA的查询。

到目前为止，这个SCA字段都是每次拉出一个很大的数据集，然后用vlookup手动查询的。虽然好几次努力想将其并入query中，都是徒劳无功。

今天，从中午11点到晚上10点，基本上一直在搞这个问题。结果是好的，终于搞定了（通过query的方式，实现了该字段查询的自动化，而不是吭哧吭哧的vlookup）。

小结一下基本步骤：

1. 能否在现有的with子句中新增一个SCA的子查询，将SCA字段并入query？（以前试过的方式，今天再次尝试，未能突破。主要报错：ORA-01417: a table may be outer joined to at most one other table）

刚才在网上搜了一下这个错误，竟然发现第一条查询结果就是正解，可惜我已为此苦耗了一天（哭！！！）

https://community.oracle.com/thread/3954721

2. 由于没有围绕这个报错深挖下去，最后被逼的没办法了，竟然想到了再外面嵌套一层子查询的方法。这其实是上面帖子中提到一种方法。

3. 将现有的整条query作为一个和SCA子查询并列的子查询，在两者的外层写主查询，框架如下：

SELECT QUERY.COUNTRY, 
                ...,
                SCA.SCA,
                ...
FROM
              (SELECT  TABLE1.COUNTRY AS COUNTRY
                                 ...
                FROM     TABLE1,
                                ...
                WHERE  ...
              ) QUERY,
               (SELECT  TABLEn.ORGNAME AS SCA
                                 ...
                FROM     TABLEn,
                                ...
                WHERE  ...
              ) SCA
WHERE ...

4. 顺带修复另外一个问题，一个UKey可以对应多个SCA，但目前为止都是最多只查询一条SCA。修复方法：使用Listagg函数进行行转列（数据压缩）操作

5. 数据验证，发现两个问题。

问题1：LISTAGG的结果中包含重复记录。好在前不久在OBIEE开发工作中碰到过这个问题，而且专门为此写了篇技术博客。于是，打开此文章，发现我写的还挺简洁明了的，很快便通过子查询解决了此问题。

参考笔者的另一篇博文： 调试经验——Oracle列转行函数 Listagg() 查询结果的去重（去除重复值）方法（https://blog.csdn.net/hpdlzu80100/article/details/79503702）。

问题2：对于一个已close的study，在整个query中查出的SCA竟然有30多条，但在SCA子查询中只有一条SCA，这个问题到目前还没解决（对我来说还是个谜）。

好在目前的query要求仅查询未close的study数据，所以暂时无碍。

6. 抽查未close的数据，测试通过！！！

至此，从问题解决到发表小结文章，已经过12小时有余，过程虽苦，还是颇有成就感的！