Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading