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

Question

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?

Answer 1

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;

Answer 2

Add it as a new column:

SELECT 'Optimization Sales' AS "Branch",                  --> this
       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 'Validation Sales' AS "Branch"                     --> this
       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