Analyzing a View to Understand Unexpected/Erroneous Behavior

It is fairly common for me to analyze a single record where unexpected behavior is occurring with a particular view.

Here is a trivial view (this is not a Noetix View):

CREATE OR REPLACE VIEW ONTX0_ORDER_LINE
AS
SELECT
    HEAD.ORDER_NUMBER,
    ITEM.SEGMENT1 ITEM,
    ITEM.DESCRIPTION ITEM_DESCRIPTION,
    LINE.ORDERED_QUANTITY
FROM
    ONT.OE_ORDER_HEADERS_ALL HEAD,
    ONT.OE_ORDER_LINES_ALL LINE,
    INV.MTL_SYSTEM_ITEMS_B ITEM,
    NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
 WHERE 1=1
    AND LINE.ORG_ID=XMAP.ORG_ID
    AND LINE.HEADER_ID = HEAD.HEADER_ID
    AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;


Now suppose through testing, we know that we receive multiple records in our results set with ORDER_NUMBER = '30066929', though we know there should only be one record.  We know this to be the case with most records, yet this record is the first one which comes back in our result set.


What is the most efficient approach to troubleshooting this problem?  Again, with our trivial view, we can find the solution quickly; yet there is value in thinking about this problem as a process and trying to refine/speed-up this process.

As with most problem solving, one should identify the problem precisely:

"For ORDER_NUMBER, '30066929', I am getting three records when I anticipate one record.  In fact, I see this triplication problem systemically".

Now that this problem has been identified, it is good to look at the most recent changes and rollback them (if the issue is a new issue).

If this approach does not work, I wanted to present my quick, brute force solution.


    1. Make a copy of the view definition (but just keep the SQL statement).


    SELECT
        HEAD.ORDER_NUMBER,
        ITEM.SEGMENT1 ITEM,
        ITEM.DESCRIPTION ITEM_DESCRIPTION,
        LINE.ORDERED_QUANTITY
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;



    2. Next, comment all line except the SELECT, FROM and WHERE lines as follows:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
    FROM
--        ONT.OE_ORDER_HEADERS_ALL HEAD,
--        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
--        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID ;

      If you are using a text editor and it happens to be VIM, you can just invoke this EX command:

        %s/^/--/c

        Where % identifies the range of lines where I intend to substitute characters (all lines).
        The '^' character is an anchor conveying to VIM that I want to find the beginning of the line.
        The '--' characters are going to placed on all lines in this range of lines.
        The 'c' option allows me to confirm which lines I want to perform this replace.

    3.  Add a condition to the where clause to more closely examine the error/problem identified.

        In this case, it should be as follows, "AND HEAD.ORDER_NUMBER = '30066929'". Since our condition is on the header table, let us uncomment all lines associated with these two tables in both the from and where clause.


        Also, we should have visibility of the primary key of the driving table (here it is "LINE.LINE_ID").  Thus we add this line in the select clause.

      
    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
--        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
--        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';

    Optimally, our condition should be on the driving table (LINE), and it should restrict the result set for the driving table to one record.  In this trivial case, we are not doing this (one could have multiple lines for a given header).



    4. Next, we uncomment tables in the From clause and their associated Where clause lines.  Here, we do this with the XMAP base view:


    SELECT
--        HEAD.ORDER_NUMBER,
--        ITEM.SEGMENT1 ITEM,
--        ITEM.DESCRIPTION ITEM_DESCRIPTION,
--        LINE.ORDERED_QUANTITY
        LINE.LINE_ID
    FROM
        ONT.OE_ORDER_HEADERS_ALL HEAD,
        ONT.OE_ORDER_LINES_ALL LINE,
--        INV.MTL_SYSTEM_ITEMS_B ITEM,
        NOETIX_SYS.ONTX0_OU_ACL_MAP_BASE XMAP
     WHERE 1=1
        AND LINE.ORG_ID=XMAP.ORG_ID
        AND LINE.HEADER_ID = HEAD.HEADER_ID
--        AND LINE.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
--      testing
        AND HEAD.ORDER_NUMBER = '30066929';


    5. After the XMAP base view being uncommented, we procedd to uncomment the ITEM table.  Here we will find that we are missing the organization_id column in the join between LINE and ITEM.


I have found this approach can work fairly quickly (20 minutes to quickly assess/analyze a view).

While this is fairly common task, it is important to approach it with speed and precision and clarity.