SQL 聚合函数失败

Question

我遇到了一种我无法诊断的奇怪行为。

我有一个 Oracle 数据库 (11g) 并且我已经在这个数据库中构建了一个 View 。此 View 返回一个信息列表，这些信息在问题发生之前很好，在问题之后目前也很好。

假设此 View 返回 10 行。当我在此 View 上运行聚合函数时，例如让我们说一个 COUNT(*) 我得到一个 10 行的列表，每行显示“Null”而不是带有数字 10 的一行。

这是一个突然开始的问题，我没有在知情的情况下对数据库或 View 进行更改(但是这是一个大型公司数据库，并且可以由少数其他人进行更改 - 所有这些人目前都否认进行了任何更改) 也没有影响 View 的已知触发器

此问题不会出现在其他表或其他 View 中。我尝试在构成最终 View 的每个表或 View 上运行聚合函数，并且每个表或 View 都按预期返回一个数字。

我不知道还有什么可以尝试的。我的谷歌搜索也没有提供任何线索。我会上传代码，但它又大又笨重，我也无法解释它是如何查看数据的(可以说数据库是法语构建的就足够了)

真正让我感到惊讶的是， View 按预期返回数据，但是当我对其运行聚合函数时(例如 SELECT COUNT(*) FROM VIEW_THAT_IS_FAILING)，它以这种奇怪的方式失败。有任何想法吗？

查看失败的定义:

SELECT x.OBJEXCDE,
       x.OBJDSC,
       x.CLASSIFICATION,
       x.COUNTRY,
       NVL(y.MONTH_USAGE, 0) AS MONTH_USAGE,
       x.STKQTY,
       x.STANDARD_COST

    FROM (SELECT a.OBJINCDE, a.OBJEXCDE, a.OBJDSC, NVL(b.STKQTY, 0) AS STKQTY, NVL(c.OBJPRC1VAL, 0) AS STANDARD_COST, NVL(d.OBJCHARVAL, 'Exception') AS CLASSIFICATION,
                 CASE WHEN TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') =  ' 0615 130' THEN 'UK'
                      ELSE 'IRE' END AS COUNTRY             
             FROM P_OBJ a, P_OBJSTORE b, P_OBJPRC c,
                  (SELECT a.OBJINCDE, b.OBJCHARVAL
                      FROM P_OBJ a, (SELECT OBJINCDE, OBJCHARVAL FROM P_OBJCHAR WHERE CHARINCDE = 524) b
                      WHERE a.OBJINCDE = b.OBJINCDE (+)) d             
             WHERE a.OBJINCDE = b.OBJINCDE (+)
             AND a.OBJINCDE = c.OBJINCDE (+)
             AND a.OBJINCDE = d.OBJINCDE (+)
             AND TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') IN (' 0615 130', ' 1158 284')
             AND a.CLASSINCDE IN (83, 84, 126)  --New Parts, Repairable Parts, Installation Parts
             AND (d.OBJCHARVAL IS NULL OR NVL(c.OBJPRC1VAL, 0) = 0)) x,
         W_USAGE_1_MONTH y

    WHERE x.OBJINCDE = y.OBJINCDE (+)
    AND x.COUNTRY = y.COUNTRY (+)

    AND (NVL(y.MONTH_USAGE, 0) + x.STKQTY) > 0

W_USAGE_1_MONTH 的定义:

SELECT a.OBJINCDE, 
       a.OBJEXCDE, 
       a.OBJDSC, 
       NVL(b.STKQTY, 0) AS STKQTY, 
       NVL(c.OBJPRC1VAL, 0) AS STANDARD_COST, 
       NVL(d.OBJCHARVAL, 'Exception') AS CLASSIFICATION,
       CASE WHEN TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') =  ' 0615 130' THEN 'UK' ELSE 'IRE' END AS COUNTRY             

    FROM P_OBJ a, 
         P_OBJSTORE b, 
         P_OBJPRC c,
         (SELECT a.OBJINCDE, b.OBJCHARVAL FROM P_OBJ a, (SELECT OBJINCDE, OBJCHARVAL FROM P_OBJCHAR WHERE CHARINCDE = 524) b WHERE a.OBJINCDE = b.OBJINCDE (+)) d             

    WHERE a.OBJINCDE = b.OBJINCDE (+)
    AND a.OBJINCDE = c.OBJINCDE (+)
    AND a.OBJINCDE = d.OBJINCDE (+)
    AND TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') IN (' 0615 130', ' 1158 284')
    AND a.CLASSINCDE IN (83, 84, 126)  --New Parts, Repairable Parts, Installation Parts
    AND (d.OBJCHARVAL IS NULL OR NVL(c.OBJPRC1VAL, 0) = 0)

Answer 1

只是为了好玩，请尝试以下操作:

SELECT x.OBJEXCDE,
       x.OBJDSC,
       x.CLASSIFICATION,
       x.COUNTRY,
       NVL(y.MONTH_USAGE, 0) AS MONTH_USAGE,
       x.STKQTY,
       x.STANDARD_COST
  FROM (SELECT a.OBJINCDE,
               a.OBJEXCDE,
               a.OBJDSC,
               NVL(b.STKQTY, 0) AS STKQTY,
               NVL(c.OBJPRC1VAL, 0) AS STANDARD_COST,
               NVL(d.OBJCHARVAL, 'Exception') AS CLASSIFICATION,
               CASE
                 WHEN TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') =  ' 0615 130' THEN 'UK'
                 ELSE 'IRE'
               END AS COUNTRY             
           FROM P_OBJ a
           LEFT OUTER JOIN P_OBJSTORE b
             ON (a.OBJINCDE = b.OBJINCDE)
           LEFT OUTER JOIN P_OBJPRC c
             ON (a.OBJINCDE = c.OBJINCDE)
           LEFT OUTER JOIN (SELECT a.OBJINCDE, b.OBJCHARVAL
                              FROM P_OBJ a
                              LEFT OUTER JOIN (SELECT OBJINCDE, OBJCHARVAL
                                                 FROM P_OBJCHAR
                                                 WHERE CHARINCDE = 524) b
                                ON (a.OBJINCDE = b.OBJINCDE)) d
             ON (a.OBJINCDE = d.OBJINCDE)
          WHERE TO_CHAR(b.STOREINCDE, '0000') || TO_CHAR(c.OBJRTEINCDE, '000') IN (' 0615 130', ' 1158 284') AND
                a.CLASSINCDE IN (83, 84, 126) AND --New Parts, Repairable Parts, Installation Parts
                (d.OBJCHARVAL IS NULL OR
                NVL(c.OBJPRC1VAL, 0) = 0)) x
  LEFT OUTER JOIN W_USAGE_1_MONTH y
    ON (x.OBJINCDE = y.OBJINCDE AND
        x.COUNTRY = y.COUNTRY)
  WHERE (NVL(y.MONTH_USAGE, 0) + x.STKQTY) > 0

这只是重新设计以使用 ANSI 连接语法的原始 View 。试一试，看看它是否有任何改变。

也可能是 DBA 应用了补丁或进行了一些其他影响这一点的更改。

分享和享受。