BLOCK-LEVEL ON ERROR UNDO, THROW.

USING OpenEdge.Core.Assert.

CLASS tests.CompoundOuterJoinTest:

    DEFINE TEMP-TABLE tt NO-UNDO
        FIELD a1 AS INTEGER.

    DEFINE TEMP-TABLE tt1 NO-UNDO
        FIELD a1 AS INTEGER.

    DEFINE TEMP-TABLE tt2 NO-UNDO
        FIELD b1 AS INTEGER.

    @Setup.
    METHOD PUBLIC VOID setUp():
        EMPTY TEMP-TABLE tt.
        EMPTY TEMP-TABLE tt1.
        EMPTY TEMP-TABLE tt2.

        CREATE tt.  ASSIGN tt.a1  = 1.
        CREATE tt.  ASSIGN tt.a1  = 2.

        CREATE tt1. ASSIGN tt1.a1 = 1.
        CREATE tt1. ASSIGN tt1.a1 = 2.
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEF1():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.
        
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,?|1,2,1|2,1,?|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEF2():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 1.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|1,2,1|2,1,?|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEF3():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 2.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|1,2,1|2,1,2|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEE1():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,?|1,1,1|1,2,1|2,1,?|2,1,2|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEE2():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 1.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|1,1,1|1,2,1|1,2,1|2,1,?|2,1,2|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEEE3():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 2.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|1,2,1|2,1,2|2,2,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFE1():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,?|1,1,1|2,1,?|2,1,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFE2():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 1.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|1,1,1|2,1,?|2,1,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFE3():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 2.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.

        cExpectedOutput = "1,1,1|2,1,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.

    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFF1():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.

        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
        
        cExpectedOutput = "1,1,?|2,1,?|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFF2():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 1.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.
        
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
        
        cExpectedOutput = "1,1,1|2,1,?|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.

    @Test.
    METHOD PUBLIC VOID testOuterJoinQueryEFF3():
        DEFINE VARIABLE hQuery          AS HANDLE    NO-UNDO.
        DEFINE VARIABLE cActualOutput   AS CHARACTER NO-UNDO INITIAL "".
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
        
        CREATE tt2. ASSIGN tt2.b1 = 1.
        CREATE tt2. ASSIGN tt2.b1 = 2.
        RELEASE tt2.

        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt1, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().

        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
            IF NOT AVAILABLE tt2 THEN DO:
                CREATE tt2.
                ASSIGN tt2.b1 = tt.a1.
                RELEASE tt2.
            END.
        END.
        
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
        
        cExpectedOutput = "1,1,1|2,1,2|".
        Assert:Equals(cExpectedOutput, cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinWithNullValue():
        DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
        DEFINE VARIABLE cActualOutput AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
    
        CREATE tt2. ASSIGN tt2.b1 = ?.
        CREATE tt2. ASSIGN tt2.b1 = 1.
    
        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, first tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().
    
        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2|", tt.a1, tt2.b1).
        END.
    
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
    
        cExpectedOutput = "1,1|2,?|".
        Assert:Equals(cExpectedOutput,cActualOutput).
    END METHOD.
    
    @Test.
    METHOD PUBLIC VOID testOuterJoinWithEmptySource():
        DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
        DEFINE VARIABLE cActualOutput AS CHARACTER NO-UNDO.
        DEFINE VARIABLE cExpectedOutput AS CHARACTER NO-UNDO.
    
        EMPTY TEMP-TABLE tt2.
    
        CREATE QUERY hQuery.
        hQuery:SET-BUFFERS(BUFFER tt:HANDLE, BUFFER tt1:HANDLE, BUFFER tt2:HANDLE).
        hQuery:QUERY-PREPARE("for each tt, each tt1, each tt2 where tt2.b1 = tt.a1 outer-join").
        hQuery:QUERY-OPEN().
        
        DO WHILE hQuery:GET-NEXT():
            cActualOutput = cActualOutput + SUBSTITUTE("&1,&2,&3|", tt.a1, tt1.a1, tt2.b1).
        END.
    
        hQuery:QUERY-CLOSE().
        DELETE OBJECT hQuery.
    
        cExpectedOutput = "1,1,?|1,2,?|2,1,?|2,2,?|".
        Assert:Equals(cExpectedOutput,cActualOutput).
    END METHOD.

    @TearDown.
    METHOD PUBLIC VOID tearDown():
        EMPTY TEMP-TABLE tt.
        EMPTY TEMP-TABLE tt1.
        EMPTY TEMP-TABLE tt2.
    END METHOD.

END CLASS.