How do I add a EBDP column to the other two tables in the UNION ALL?

Question

How do I add a columns similar to the EDBP column in SP_Main to the EM_Main and OP_Main tables when they don't contain any of the columns that the SP_Main table has. I need a column of the same data type so that the UNION ALL doesn't break.

Here is the code:

SELECT
   ISNULL(CONVERT(money,[COLUMN1]), 0.00) 
         * COALESCE(CONVERT(MONEY,[COLUMN2]),CONVERT(MONEY,[COLUMN3]), 1.00) AS [EBDP],
   ISNULL(CONVERT(money,[COLUMN1]), 0.00) 
         * COALESCE(CONVERT(MONEY,[COLUMN2]),CONVERT(MONEY,[COLUMN3]), 1.00) 
      + [ActivityPrice] AS TotalPrice,
   convert(money,[COLUMN4]) AS ActivityPrice
FROM [SP_Main] e

UNION ALL

SELECT 
    [COLUMN WILL NEED TO BE CREATED FOR HERE]
    ISNULL(CONVERT(money,[COLUMN5]), 0.00) AS [TotalPrice],
    ISNULL(CONVERT(money,[COLUMN6]), 0.00) AS [ActivityPrice]
FROM EM_Main

UNION ALL 

SELECT 
    [COLUMN WILL NEED TO BE CREATED FOR HERE]
    ISNULL(CONVERT(money,[COLUMN5]), 0.00) AS [TotalPrice],
    ISNULL(CONVERT(money,[COLUMN6]), 0.00) AS [ActivityPrice]
FROM OP_Main

Answer 1

I believe you can force a specific type by casting/converting a specific value as a specific data type - if the 2nd and 3rd selects have SELECT CAST(NULL AS money) AS [EBDP] that will do what you need (assuming the final column is of type money) e.g., for the second select in the union

select 
    CAST(NULL AS money) AS [EBDP],
    ISNULL(CONVERT(money,[COLUMN5]), 0.00) AS [TotalPrice],
    ISNULL(CONVERT(money,[COLUMN6]), 0.00) AS [ActivityPrice]

from EM_Main

Alternatively, you can use SELECT CAST(0 AS money) AS [EBDP] if you want a specific value.

If you prefer CONVERT, you can swap that in too e.g., SELECT CONVERT(money, 0) AS [EBDP]

Note that in the second and third SELECTs, the column names (EBDP, TotalPrice, ActivityPrice) are not needed, but it can help with maintaining the query (e.g., understanding what the query is doing when coming back to it after 6 months).

Answer 2

If you need a dummy column just to make the UNION ALL work, you can use the following:

CONVERT(money,0.00) AS [EBDP],

Here I wrote an example (but I had to make some changes in your query to get it working):

https://sqlfiddle.com/sql-server/online-compiler?id=9a89ae75-55b6-4d7b-a571-3d006b84e5c7