All geek questions in one place

SQL aggregation error

I came across strange behavior that I cannot diagnose.

I have an Oracle database (11g), and I created a view in this database. This view returns a list of information that was good before the problem occurred and is currently fine after the problem.

Say this view returns 10 rows. When I run an aggregate function over this view, for example. let's say COUNT (*) I get a list of 10 lines, each of which displays "Null" instead of a single line with number 10.

This was a problem that started abruptly without me, deliberately making changes to the database or presentation (however this is a large corporate database, and the changes could have been made by a few selected people - all of whom currently deny any changes were made). There are also no triggers that affect the view.

This issue does not appear in other tables or other views. I tried to run an aggregate function for each table or view that makes up the final view, and each of them returns as many as possible.

I'm not sure what else to try. My Google searches also do not offer any links. I would download the code, but it is large and bulky, I also could not explain how it looks at the data (suffice it to say that the database is built in French)

What really causes me is that the view returns the data as expected, but when I run an aggregate function on it (e.g. SELECT COUNT (*) FROM VIEW_THAT_IS_FAILING), it fails in this strange way. Any ideas?

A view that does not match the definition:

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

Definition for 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)
+4
source share
2 answers

While the support request has not yet been answered, I had a problem solving this problem.

, , SQL . , W_USAGE_1_MONTH , NULL , .

, , -, , W_USAGE_1_MONTH FROM FROM Clause:

(SELECT OBJINCDE, MONTH_USAGE, COUNTRY FROM W_USAGE_1_MONTH) b

, ( ) , SQL-. W_USAGE_1_MONTH , , .

, , , , . , , - .

0

:

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

, , .

.

0

Source: https://habr.com/ru/post/1539905/

More articles:


All Articles