1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help with outer join

Discussion in 'SQL PL/SQL' started by Bufoss, Oct 24, 2018.

  1. Bufoss

    Bufoss Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Greece
    Hi all,
    I am trying the following query but the result is not the appropriate :

    Code (SQL):
    WITH table1 AS (
    SELECT '201801' AS MONTH, 1 AS col1, 'A1' AS col2, 1 AS col3, 'X1' AS col4, 100 VALUE FROM dual UNION ALL
    SELECT '201801' AS MONTH, 1 AS col1, 'A1' AS col2, 2 AS col3, 'X2' AS col4, 60 VALUE FROM dual UNION ALL
    SELECT '201801' AS MONTH, 2 AS col1, 'A2' AS col2, 1 AS col3, 'X1' AS col4, 20 VALUE FROM dual UNION ALL
    SELECT '201802' AS MONTH, 2 AS col1, 'A2' AS col2, 1 AS col3, 'X1' AS col4, 11 VALUE FROM dual
    ),
    table2 AS (
    SELECT 1 col1, 'A1' col2 FROM dual UNION ALL
    SELECT 2 col1, 'A2' col2 FROM dual UNION ALL
    SELECT 3 col1, 'A3' col2 FROM dual
    ),
    table3 AS (
    SELECT 1 col3, 'X1' col4 FROM dual UNION ALL
    SELECT 2 col3, 'X2' col4 FROM dual
    )
    SELECT t1.MONTH, t1.col1, t1.col2, t1.col3, t1.col4, t1.VALUE
    FROM table1 t1
    FULL OUTER JOIN  ( SELECT  t2.col1, t2.col2, t3.col3, t3.col4, 0 AS VALUE
                         FROM table2 t2
                        CROSS JOIN table3 t3 ) VW ON t1.col1 = vw.col1
                                                 AND t1.col2 = vw.col2
                                                 AND t1.col3 = vw.col3
                                                 AND t1.col4 = vw.col4;


    I want to make all the combinations cross joining
    tables table2 and table3 and then outer joining with table2
    in order to have all the possible combinations for every month.

    If I don''t have value 0 should be assigned as follow :

    [output]
    -- =======================
    201801 1 A1 1 X1 100
    201801 1 A1 2 X2 60
    201801 2 A2 1 X1 20
    201801 2 A2 2 X2 0
    201801 3 A3 1 X1 0
    201801 3 A3 2 X2 0
    -- =======================
    201802 1 A1 1 X1 0
    201802 1 A1 2 X2 0
    201802 2 A2 1 X1 11
    201802 2 A2 2 X2 0
    201802 3 A3 1 X1 0
    201802 3 A3 2 X2 0
    -- =======================
    [/output]

    Any ideas ?