sqloracleunioncalculated-columnsunion-all

Unite tables in Oracle SQL developer and create new customized column with name of tables


I have around ten tables with the same dimension of columns and with the same columnnames. These table are united with UNION ALL. Now I want to create a customized column and add the name of the tables to all of these different tables.

SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
       ROUND(SUM(quantity), 2) AS "Quantity",
       ROUND(SUM(value) , 2) AS "ContractValue",
       product AS "Product",
       intention AS "Intention"
FROM table1
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, kelag_intention

UNION ALL 

SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
       ROUND(SUM(quantity), 2) AS "Quantity",
       ROUND(SUM(value) , 2) AS "ContractValue",
       product AS "Product",
       intention AS "Intention"
FROM table2
GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, kelag_intention

I want a new column named as "Branch", where I can create for each table a specific entry, like for table1 the row entries of Branch should be should be Optimization Sales" and for table2 it should be "Validation Sales". How can I do this?


Solution

  • You can add a static value for the "Branch" column in each of your SELECT statements within the UNION ALL.

    SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
           ROUND(SUM(quantity), 2) AS "Quantity",
           ROUND(SUM(value) , 2) AS "ContractValue",
           product AS "Product",
           intention AS "Intention",
           'Optimization Sales' AS "Branch"  -- Branch specific to table1
    FROM table1
    GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention
    
    UNION ALL 
    
    SELECT TO_CHAR(begtime, 'YYYY-MM') AS "DelivMonth",
           ROUND(SUM(quantity), 2) AS "Quantity",
           ROUND(SUM(value) , 2) AS "ContractValue",
           product AS "Product",
           intention AS "Intention",
           'Validation Sales' AS "Branch"  -- Branch specific to table2
    FROM table2
    GROUP BY TO_CHAR(begtime, 'YYYY-MM'), product, intention;